comparing the totals for two years

[vasu1211]

ost ocity dst dcity shipdate paid amt transport amt
A B C D 1/1/07 7888 777
E F G H 2/3/07 733 887
I J K L 4/5/08 998 756
A B M N 5/1/08 778 697
E F S T 9/1/08 877 345
A B O Q 1/2/07 20 10

OUTPUT required is


ost ocity sumoftransportamt(2007) sumoftransportamt(2008)
sumoftransportamt(2007) sumofpaidamt(2007) sumofpaidamt(08)
A B 787 697

697 7908 778
E F
887
345 877 733


i want to calculate the sum of paid amt and transport amt for every
origin(ost and ocity) and then compare the totals across two years 07
and 08.


can this be done using a single query- SQL ? what would be a single
SQL statement?


thanks

 

[ajetrumpet]

OUTPUT required is


ost ocity sumoftransportamt(2007) sumoftransportamt(2008)
sumoftransportamt(2007) sumofpaidamt(2007) sumofpaidamt(08)
A B 787 697

697

http://www.access-programmers.co.uk/forums/showthread.php?t=135763

Try and figure out how the following query is described in the above FAQ. It should give you the output you want:

SELECT ost, ocity, sum(transportamount2007) AS [Sum of Transport 2007], 
   sum(transportamount2008) AS [Sum of Transport 2008]

FROM....

GROUP BY ost, ocity

Syntax is not of Access flavor, but you can change that yourself....

 

[vasu1211]

hi

hi

thanks e r the reply- but do i hav e to create seperate queries for calculating the sum for both the years. Can i doit by a single query? thanks

 

[ajetrumpet]

No you don't. I just showed you that. The table in your first post shows that you have records in the (same table) that relate to more than one year. You also stated that you wanted these totals listed in groups (by City/State combination), didn't you?

Just try what I gave you and see what the outcome is. You might have questions after that...

 

[Rich]

Use A Totals query select the grouping and summing options you want