473,322 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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:
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
2 4307
Stewart Ross
2,545 Expert Mod 2GB
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
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

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

Similar topics

3
by: paul | last post by:
I have a query that takes monthly totals and rolls them up to get a balance at a specific time code eg: dt bucket mon_ttl --- ------ ------- 199903 ...
4
by: The Bit Bandit | last post by:
Hopefully someone can help me create a query that I'm having some trouble with. I have three tables: invoices, invoicedetails, invoicepayments The fields are: invoices -------- InvoiceNo
8
by: Tony Williams | last post by:
I have a database that hold s figures for each quarter. I want to create a query whereby the user can ask for the running total of the data upto the quarter they enter into a prompt. The database...
4
by: New Guy | last post by:
I'm trying to work with a system that somebody else built and I am confounded by the following problem: There is a table of payments and a table of charges. Each client has charges and payments...
3
by: brm6546545 | last post by:
I have a tblTax. It has fields InvoiceNum, TaxAuthority, TaxableTotal, NonTaxableTotal, TaxCollected. Sample data 1,county,10.00,0.00,0.40 1,city,10.00,0.00,0.10 2,state,0.00,15.00,0.15 ...
2
by: BerkshireGuy | last post by:
I have a form that acts like a dashboard to show summarized data. Currently, this form gets its summarized values from a total's query. When the user selects to run the dashboard, they should be...
9
by: JJM0926 | last post by:
I'm trying to create a running totals query in access 97. I have followed the directions on how to do it from Microsofts website article id 138911. I took their code they had and replaced it with...
4
by: Micheal | last post by:
Greetings Access Group, Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days...
0
MMcCarthy
by: MMcCarthy | last post by:
Rather than using the Access design view change the view to SQL. I am going to attempt to outline the general syntax used for SQL queries in Access. Angle brackets <> are used in place of some...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.