Totals - Using Group by and Sum with a Where clause (Access)

[natalie99]

Hi All

I thought this would be easy but I am having great difficulty! Could someone kindly please explain where I am going wrong?

I have a single table of data, tblA

each line shows a start/stop, country, date and price

eg. lines:
Action-------Country----------Date-------------------Value
STOP------Argentina---------01/05/2005-------------($50)
STOP------Fiji------------------01/08/2007--------------($100)
START-----Fiji-------------------01/06/2006-------------$150
START-----Cuba----------------01/04/2008-------------$50

Pretty simple.

I need to create a query with the following:

Country names (vertical), split by start or stop

eg.

Argentina - Start
Argentina - Stop
Cuba - Start
Cuba - Stop
Fiji - Start
Fiji - Stop

I then need to populate the Prices of the starts/stops that took place in each month

i.e.
Country/Price per Month---------01/05/2005--------01/06/20005---------01/07/2005
Argentina---------Start--------------$150----------------- $50
Argentina---------Stop--------------($50)-----------------($200)

essentially this mimics an excel pivit table function but i don't know how to create it in access?

I have tried using Group By Country, Sum Price, with a criteria of Date works for one col only
I have tried using an expr:

 Month1: iif(Date=#01/05/2005#,(sum([Value]),0)

then repeating for subsequent months this works for one col too, but once I add a second col summing the Value field it stops working...
I have tried multiple other ideas which I won't list as I'm sure I'm missing something obvious (fingers crossed)

Please help! :)

[Stewart Ross]

Hi Natalie. The nearest equivalent to the pivot table Access has is the Crosstab query. It has some limitations which will become apparent if you try it, particularly with the dates involved (as when pivoted on date it will produce a column for every date listed in the dataset, even if used just once for one of the countries).

Open your current grouping query and in design view select Query, Crosstab. This adds an additional row to the grid in which you select whether a field is a row heading (as many as you like), the column heading (one only), or the value pivoted (one only). The column heading in your case will be your date field, the value will be a sum of the amounts.

Try it out and let us know how you get on.

-Stewart

[natalie99]

Hi Stewart!!

Thanks the cross tab query worked perfectly for the growth accrual.

_______________ _______________ _______________ _______________ _

(Before seeing your post I had eventually decided on using the following (which did work but was a lot more work than your solution!)

SELECT tbl1.Country, tbl1.Type, Sum(IIf([Month]=#5/1/2007#,[Price],0)) AS [May 2007], Sum(IIf([Month]=#6/1/2007#,[Price],0)) AS [June 2007], Sum(IIf([Month]=#7/1/2007#,[Price],0)) AS [July 2007]
FROM tbl1
GROUP BY tbl1.Country, tbl1.Analysis;

etc etc for every month, which took a while!)