Query Help

[CCPKGUY]

QUERY 1
ACCOUNT_NO = ‘30123000’
OCCUPANT_CODE
UTILITY_TYPE = ‘W’
JOUR_CODE = ‘CR’
TRANS_DATE Between 12/1/2012 – 4/30/2013
TRANS_AMT

Results are as below which is what I want to see. Those are the payments made for the utility (W)Water
ACCOUNT_NO OCCUPANT_CODE UTILITY_TYPE JOUR_CODE TRANS_DATE TRANS_AMT
30123000 5 W CR 12/13/2012 -$16.02
30123000 5 W CR 1/17/2013 -$16.02
30123000 5 W CR 2/12/2013 -$26.54
30123000 5 W CR 3/13/2013 -$16.02
30123000 5 W CR 4/8/2013 -$5.50


QUERY 2
ACCOUNT_NO = 30123000
OCCUPANT_CODE
JOUR_CODE = BJ
TRANS_DATE Between 12/1/2012 - 4/30/2013
TRANS_AMT
DATE_PAID

QUERY 2
ACCOUNT_NO OCCUPANT_CODE JOUR_CODE TRANS_DATE TRANS_AMT DATE_PAID
30123000 5 BJ 12/27/2012 $121.65 1/17/2013
30123000 5 BJ 1/29/2013 $133.61 2/19/2013
30123000 5 BJ 2/25/2013 $124.77 3/18/2013
30123000 5 BJ 3/26/2013 $96.63 4/16/2013
30123000 5 BJ 4/24/2013 $115.71 5/15/2013


What I am trying to do is join the 2 queries that will show the the following below

ACCOUNT_NO OCCUPANT_CODE UTILITY JOUR_CODE TRANS_DATE TRANS_AMT JOUR_CODE DATE_PAID TRANS_AMT
30123000 5 W BJ 12/27/2012 $121.65 CR 1/17/2013 -$16.02

 

[randy700]

Perhaps something like this?

SELECT Q1.ACCOUNT_NO, 
       Q1.OCCUPANT_CODE, 
       Q1.UTILITY_TYPE, 
       Q2.JOUR_CODE AS 2ND_JOUR, 
       Q2.TRANS_DATE, 
       Q2.TRANS_AMT AS 2ND_AMT, 
       Q1.JOUR_CODE AS 1ST_JOUR, 
       Q2.DATE_PAID, 
       Q1.TRANS_AMT AS 1ST_AMT 
FROM Q1 INNER JOIN Q2 ON Q1.ACCOUNT_NO = Q2.ACCOUNT_NO

Randy

 

[CCPKGUY]

Randy....I set the query up as outilined in your suggestion but I am getting duplicates and I should only have 5 rows of the data. What am I during wrong?

 

[randy700]

Sorry. Joining on the account no field is going to be a problem.
I'll have to look a little closer.

Perhaps someone else, that still works with Access, will jump in... DHOOKUM maybe??


Randy

 

[dhookom]

Thanks Randy, DHOOKOM doesn't have time at the moment to try to decipher the data when it's not presented using TGLM so it is in columns. I might have time to pick some low hanging fruit until later tonight.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I'd try this join:

FROM Q1 INNER JOIN Q2 ON Q1.ACCOUNT_NO = Q2.ACCOUNT_NO AND Q1.TRANS_DATE = Q2.DATE_PAID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886