By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

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

natalie99
P: 41
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:
Expand|Select|Wrap|Line Numbers
  1.  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! :)
Jul 23 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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
Jul 24 '08 #2

natalie99
P: 41
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!)

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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]
  3. FROM tbl1
  4. GROUP BY tbl1.Country, tbl1.Analysis;
  5.  
  6.  
etc etc for every month, which took a while!)
Jul 24 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.