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