Query in Access 3

nissan240zx · Nov 28, 2005

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:

Code:

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:

Code:

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

nissan240zx · Nov 29, 2005

Please advice

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

lespaul · Nov 29, 2005

why don't you provide some sample data and what your expected output is based on that sample.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

nissan240zx · Nov 29, 2005

Ok here is some sample data:
In the Paysystag table:
HDR_USER_ALPHA_1
FOS
FOS
POR
POR
SWE
POR
SWE
POR
POR
POR

HDR_USER_ALPHA_2
SSR
FOS
FOS
POR
POR
SWE
POR
SWE
POR
POR

HDR_USER_5_CD
5E
5B
5B
5F
5F
5C
5F
5C
5F
5F
5F

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

Now what I want to do is...I want to display the appropriate HDR_USER_ALPHA_ value depending upon the Collector team.
How do I do it.

Let me know if more information is required

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

dhookom · Nov 29, 2005

You mention "teams" but don't provide a clue where to find this field. Whatever table it is in, consider adding another field that identifies which HDR_USER_ALPHA_ value to use.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

PHV · Nov 29, 2005

In the query grid:
HDR_USER_ALPHA: IIf(HDR_USER_5_CD Like '5[ABDF]', HDR_USER_ALPHA_1, IIf(HDR_USER_5_CD Like '5[CE]',HDR_USER_ALPHA_2,'?'))

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

nissan240zx · Nov 30, 2005

Hello PHV,
In this expression

Code:

Like '5[ABDF]',

What does like '5 do.
Team id is available in Groups.GroupID, Collectors.Collector_Group & Paysystag.HDR_USER_5_CD

Please advice..

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

lespaul · Nov 30, 2005

Nissan240zx,

As requested above, it is much easier to assist you when we know what you want. The best way for you to do this is in the following manner:

Code:

I have these three tables:

PaySysTag
Groups
Collectors

PaySysTag
Field1        Feild2        Feild3       Field4
data          data          data         data
data          data          data         data
data          data          data         data
data          data          data         data

This table related to table Collectors through the ____ field in Collectors to the _____ field in Paysystag.  The data in Collectors is:

Collectors
Field1        Feild2        Feild3       Field4
data          data          data         data
data          data          data         data
data          data          data         data
data          data          data         data

Another table, Groups, is related to Collecters by the fields _______ & _________

Groups contains:

Groups
Field1        Feild2        Feild3       Field4
data          data          data         data
data          data          data         data
data          data          data         data
data          data          data         data


From this information I am trying to count the total number of accounts and have final information like:

QueryResults
Table.Field1        Table.Feild2        Table.Feild3       Table.Field4
data          data          data         data
data          data          data         data
data          data          data         data
data          data          data         data

by giving us a visual picture of what you have and what you want to end up with, it's much less frustating for us to help. If you browse through the postings you will see that people who present their question in this manner get the exact query they need within two posts.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

dhookom · Nov 30, 2005

Leslie deserves a star for the above and beyond reply.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

lespaul · Nov 30, 2005

thanks duane!! It's so hard when you want to help, but you're not given the appropriate information!

les

nissan240zx · Dec 5, 2005

Sorry my computer was having problems so couldnt logon earlier.
Will give the info as mentioned by leslie..

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

nissan240zx · Dec 7, 2005

Here is all the info that you would need for the query.
I have a main table called Paysystag which has records that contains fields like
CTA_PERM_COLL_ID - Collector ID
HDR_USER_ALPHA_1 - Range 1
HDR_USER_ALPHA_2 - Range 2
HDR_USER_5_CD - Collector's Team
EDCDIC_NAME - HDR_USER_ALPHA_1 & HDR_USER_ALPHA_2 equivalent converted value

Code:

Examples from Paysystag
CTA_PERM_COLL_ID
DMM
DMM
DMM
DMM
DMM
DMM
DMM
DMM

HDR_USER_ALPHA_1
AME
ALA
ALA
ALA
AME
ASB
ASB
AUR

HDR_USER_ALPHA_2
AGG
ALA
ALA
ALA
AME
ASB
ASB
AUR

HDR_USER_5_CD
5E
5E
5E
5E
5E
5E
5E
5E

EBCDIC_NAME
194200200
194212194
194212194
194212194
194213198
194227195
194227195
194229218

Then we have corresponding tables like Groups and Collectors.
Example Groups
GroupID	Group Name	Group Desc
5A	Major East	Major Accounts East
5B	Major West	Major Accounts West
5C	FM		Facilites Management
5D	Muni East	Municipal Accounts East
5E	National	National Accounts
5F	Muni West	Municipal Accounts West

Example Collectors
Collector_ID	Collector_FirstName	Collector_LastName	Collector_JobTitle	Collector_Group
ADB		Adrienne		Brooks				Collector - 5B	5B
AZW		Amberly		Waddle				Collector - 5D	5D
C09		Susie		Freeman				Collector - 5B	5B
C39		Debbie		Bryan				Collector - 5D	5D
C77		Jeamie 		Jacks				Collector - 5E	5E
CZR		Catherine		Rozier				Collector - 5D	5D
DMM		Dwight		Moore				Collector - 5C	5C
KDD		Kristy		Dodd				Collector - 5D	5D
PMO		Patti		Oliver				Collector - 5F	5F
S26		Cal		Cooper				Collector - 5C	5C
S68		Geneva		Wilson				Collector - 5E	5E
SSG		Sherie		Wynes				Collector - 5A	5A
SSI		Sonia		Singleton				Collector - 5E	5E
SWL		Shemika		Lawrence				Collector - 5E	5E
TAB		Tammy		Burkett				Collector - 5F	5F
TPL		Trelma		Pleas				Collector - 5A	5A

Condition - 
HDR_USER_ALPHA_1 for ('5A','5B','5D','5F')"
HDR_USER_ALPHA_2 for ('5E','5C')"

Query logic:
Select  [PaysysTag].[CTA_PERM_COLL_ID], Count(*) AS TotalAccounts, [Collectors].[Collector_FirstName], [Collectors].[Collector_LastName], [Groups].[Group Name], [Groups].[Group Desc] and show the FIRST HDR_USER_ALPHA_1/EBCDIC_NAME or HDR_USER_ALPHA_2/EBCDIC_NAME (according to the group the collector is in) and the LAST HDR_USER_ALPHA_1/EBCDIC_NAME or HDR_USER_ALPHA_2/EBCDIC_NAME
Based on the example above it should show the CTA_PERM_COLL_ID AS DMM, GROUP AS 5E, FIRST HDR_USER_ALPHA as AGG/194200200 & LAST HDR_USER_ALPHA as AUR/194229218

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

lespaul · Dec 7, 2005

and what results do you want from this information?

nissan240zx · Dec 8, 2005

Hello Leslie
I want to get the collector id, name, grrop name, first and last alpha & ebcdic name based on his/her group name condition.
Condition -
HDR_USER_ALPHA_1 for ('5A','5B','5D','5F')"
HDR_USER_ALPHA_2 for ('5E','5C')"

Thanks in advance

Jimmy Nissan

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

nissan240zx · Dec 9, 2005

Any advice on this query logic...

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

nissan240zx · Dec 9, 2005

HDR_USER_5_CD in PaySystag = GroupID in Groups = Collector_Group in Collectors

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

nissan240zx · Dec 9, 2005

CTA_PERM_COLL_ID in PaySysTag = Collector_ID in Collectors

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

nissan240zx · Dec 12, 2005

Any leads based on the information..
Please let me know..

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

PHV · Dec 13, 2005

HDR_USER_ALPHA_1 for ('5A','5B','5D','5F')
HDR_USER_ALPHA_2 for ('5E','5C')
I resubmit my suggestion (in the query grid):
HDR_USER_ALPHA: IIf(HDR_USER_5_CD Like '5[ABDF]', HDR_USER_ALPHA_1, IIf(HDR_USER_5_CD Like '5[CE]',HDR_USER_ALPHA_2,'?'))

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

nissan240zx · Dec 13, 2005

Hello PHV,
thanks for the query.
I am getting a syntax error when I try to run this clause

Code:

WHERE HDR_USER_ALPHA : IIf(HDR_USER_5_CD Like '5[ABDF]', HDR_USER_ALPHA_1, IIf(HDR_USER_5_CD Like '5[CE]',HDR_USER_ALPHA_2,'?'))

And this the complete query so far

Code:

SELECT Count(*) AS TotalAccounts, Collectors.Collector_FirstName, Collectors.Collector_LastName, Groups.[Group Name], Groups.[Group Desc], HDR_USER_ALPHA AS Expr1 INTO 1
FROM PaysysTag INNER JOIN (Groups INNER JOIN Collectors ON Groups.GroupID = Collectors.Collector_Group) ON PaysysTag.CTA_PERM_COLL_ID = Collectors.Collector_ID
WHERE HDR_USER_ALPHA : IIf(HDR_USER_5_CD Like '5[ABDF]', HDR_USER_ALPHA_1, IIf(HDR_USER_5_CD Like '5[CE]',HDR_USER_ALPHA_2,'?'))
GROUP BY Collectors.Collector_FirstName, Collectors.Collector_LastName, Groups.[Group Name], Groups.[Group Desc], [PaysysTag].CTA_PERM_COLL_ID;

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Query in Access 3

Programmer

Programmer

Programmer

Programmer

Programmer

MIS

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

MIS

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor