MS Access 2002 - Using SQL to filter through data

[jay.barnes]

Hello! I was looking for some help with something I'm trying to accomplish with MS Access. I have data set up in the following manner:

CUSTNBR|TRANSYTD|  RPTDATE
---------------------------
   1   |    7   | 5/28/2010
   1   |    10  |12/31/2010
   1   |    2   | 4/29/2011 
   2   |    8   |12/31/2010
   2   |    6   | 4/29/2011
   3   |    3   | 4/29/2011
   4   |    4   | 5/28/2010
   4   |    6   |12/31/2010
   4   |    2   | 4/29/2011
   5   |    5   | 5/28/2010
   5   |    8   |12/31/2010
   5   |    5   | 4/29/2011
   6   |    6   | 5/28/2010
   6   |    10  |12/31/2010
   6   |    6   | 4/29/2011

---------------------------

What I'm looking to do in MS Access is construct a SQL query that will get the number of transactions for each account between 5/28/2010 and 4/29/2011.

This would involve subtracting the TRANSYTD figure for 12/31/2010 from the TRANSYTD figure for 5/28/2010 for each customer number, and then adding that figure to the the TRANSYTD figure for 4/29/2011 to that for each account. The output, based on the data above, should look something like this:

CUSTNBR|TRANACTIONS|
--------------------
   1   |      5    |
   2   |     14    |
   3   |      3    |
   4   |      4    |
   5   |      8    |
   6   |     10    |
--------------------

I've tried several variations of UNION, and SUM- and, also trying them after separating the data into tables, based on RPTDATE, none of which give me the desired results.

Can anyone make any suggestions, please? Thanks!

-Jay

[BitBlt]

Okay, so this was a fun one. I had to do this using a fake table, plus I used Access2003 (hope you don't mind -- syntax should be the same).

First, create these queries:
qryDistinctCustNbr:

SELECT DISTINCT dbo_TransStuff.CUSTNBR
FROM dbo_TransStuff;

qryFor1231:

SELECT dbo_TransStuff.CUSTNBR, dbo_TransStuff.TRANSYTD, dbo_TransStuff.RPTDATE
FROM dbo_TransStuff
WHERE dbo_TransStuff.RPTDATE=#12/31/2010#;

qryFor0528:

SELECT dbo_TransStuff.CUSTNBR, dbo_TransStuff.TRANSYTD, dbo_TransStuff.RPTDATE
FROM dbo_TransStuff
WHERE dbo_TransStuff.RPTDATE=#5/28/2010#;

qryFor0430:

SELECT dbo_TransStuff.CUSTNBR, dbo_TransStuff.TRANSYTD, dbo_TransStuff.RPTDATE
FROM dbo_TransStuff
WHERE dbo_TransStuff.RPTDATE=#4/30/2011#;

Then create this query
MainQuery:

SELECT 
qryDistinctCustNbr.CUSTNBR, 
qryFor528.TRANSYTD, 
qryFor1231.TRANSYTD, 
qryFor429.TRANSYTD, 
IIf(IsNull([qryFor1231]![TRANSYTD]),0,[qryFor1231]![TRANSYTD]) 
- IIf(IsNull([qryFor528]![TRANSYTD]),0,[qryFor528]![TRANSYTD]) 
+ IIf(IsNull([qryFor429]![TRANSYTD]),0,[qryFor429]![TRANSYTD]) 
AS Field1
FROM 
((qryDistinctCustNbr LEFT JOIN qryFor528 ON qryDistinctCustNbr.CUSTNBR = qryFor528.CUSTNBR) LEFT JOIN qryFor1231 ON qryDistinctCustNbr.CUSTNBR = qryFor1231.CUSTNBR) LEFT JOIN qryFor429 ON qryDistinctCustNbr.CUSTNBR = qryFor429.CUSTNBR;

Of course, you'll have to change all the field/table names to fit what you're really doing, but this seemed to work and give the result needed.

Good luck!

[ChrisPadgham]

SELECT TransInfo.CustNo, Sum(IIf([RPTDate]=" 5/28/2010",[TransYTD]*-1,[TransYTD])) AS CombinedTrans FROM TransInfo GROUP BY TransInfo.CustNo;

[jay.barnes]

Thank you for the responses, guys! I tried them both, but ended up going with Chris' simpler solution. Regardless, thanks for both of your time!