Query in Access 3

[nissan240zx]

Hello All,
I am working on a query in Access.
Did some work on and now want to add some more conditions.

Here is the query so far:

SELECT [PaysysTag].COLLID AS Expr1, Count(*) AS TotalAccounts, Collectors.Collector_FirstName, Collectors.Collector_LastName, Groups.[Group Name], Groups.[Group Desc]

FROM PaysysTag INNER JOIN (Groups INNER JOIN Collectors ON Groups.GroupID = Collectors.Collector_Group) ON PaysysTag.CTA_PERM_COLL_ID = Collectors.Collector_ID
GROUP BY [PaysysTag].COLLID, Collectors.Collector_FirstName, Collectors.Collector_LastName, Groups.[Group Name], Groups.[Group Desc];

In my table Paysystag I have 2 fields called HDR_USER_ALPHA_1 & HDR_USER_ALPHA_2

The business logic is that all Collectors in teams ('5A','5B','5D','5F') use HDR_USER_ALPHA_1 and Collectors in ('5E','5C') use HDR_USER_ALPHA_2

In my above query I want to display the appropriate HDR_USER_ALPHA_ value depending upon the Collector team.
How do I do it.
This is the way my Collector table is setup:

Collector_JobTitle	Collector_Group
Collector - 5A	            3
Collector - 5A	            3
Collector - 5B	            4
Collector - 5B	            4
Collector - 5C	            2
Collector - 5C	            2
Collector - 5D	            5
Collector - 5D	            5
Collector - 5D	            5
Collector - 5D	            5
Collector - 5E	            1
Collector - 5F	            6
Collector - 5F	            6

I also have a field that stores collector team name in Paysystag table called HDR_USER_5_CD

A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder

 

[PHV]

My suggestion wasn't a criteria but an expression !
A starting point to see if HDR_USER_ALPHA is correct:
SELECT Collectors.Collector_FirstName, Collectors.Collector_LastName, Groups.[Group Name], Groups.[Group Desc]
, IIf(HDR_USER_5_CD Like '5[ABDF]', HDR_USER_ALPHA_1, IIf(HDR_USER_5_CD Like '5[CE]',HDR_USER_ALPHA_2,'?')) AS HDR_USER_ALPHA
FROM PaysysTag INNER JOIN (Groups INNER JOIN Collectors ON Groups.GroupID = Collectors.Collector_Group) ON PaysysTag.CTA_PERM_COLL_ID = Collectors.Collector_ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nissan240zx]

SUPER DOOPER.
THANKS PHV
it worked.
Now phase 1 is done..
wow...excited..
After I work bit more tweaks on this I need to figure out how to get the lowest and highest alpha based values..

thanks once again..


A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder