It's been about 5 years since I wrote any queries and I'm kinda stumped on this simple one. Hopefully after this it will trigger some of what I learned the first time around.
I am trying to group this table by fiscal year and commodity. Then I want to sum the totals to get a year to date total for each. This would be current year to date for one and then what it was a year ago for the other fiscal year to understand what I'm doing. Every time I try to write it I get the aggregate function error. I was thinkin I could group by fiscal year and commodity and just do a sum to at least get me close but I can't even get reletively close.
My query is currently.
SELECT [Container Tons].[Fiscal Year], [Container Tons].Period, [Container Tons].[Fiscal Month], [Container Tons].Port, [Container Tons].Commodity, Sum([Container Tons].Tons) AS SumOfTons
FROM [Container Tons]
WHERE [Container Tons].Period <= 4
GROUP BY [Container Tons].[Fiscal Year], [Container Tons].Commodity
Try this:
Change the order of your fields in the query.SELECT ContainerTons.FiscalYear, Sum(ContainerTons.Tons) AS SumOfTons, ContainerTons.Commodity, ContainerTons.Period,ContainerTons.FiscalMonth, ContainerTons.Port FROM ContainerTons GROUP BY ContainerTons.FiscalYear, ContainerTons.Commodity, ContainerTons.Period,ContainerTons.FiscalMonth, ContainerTons.Port HAVING (((ContainerTons.Period)<=4));
Last edited by alansidman; 11-17-2010 at 11:06 AM. Reason: Forgot Period inSQL
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
What's causing Access to pop Expr1, Expr2, Expr3, etc in all my fields when I try to write this query just as you showed me? Even if I take it out it pops it right back in. I've never remembered having this problem. Because then when I run it like it is, I guess that causes it to ask for parameters for each field?
James;
To make sure we are on the same page, it might be beneficial if you posted your db with soome sample data.
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
I just briefly tried to mess with the Query with you recommendation but I might have played with it since. Anyway attached is the file.
Jason;
Look at the attached (Query1) and let me know if you are looking for more or if this will get you where you need to be.
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
forgot to attach.
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
Thanks for all your help so far.
What I was tryin to do is pull any month and get a year-to-date total. For example Chemicals for 2011 and add July-Aug-Sept-Oct. which are periods 1-4. Then go back and pull 2011 Chemicals 2010 for the first 4 periods to do a comparative basis. Once I get the queries perfected and all my data in I eventually wanted to create a form form or report for others to quickly gather data.
Ok. I'm clear now. I have an idea. Need an hour.
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
Jason;
Attached is an update to your db. I crreated a summary query by product. I then created a union query to join the two queries and sorted them to give you details and totals. I created a form that gives you the option to select what period summation you desire. This is fed to Query 1 and generates data based upon that. Look at all QBEs to try and understand. If you have any questions, post back, but this should get you started on the right track.
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
This is perfect. I can learn what I need from this when I create the rest of the DB. I'm sort of messing with this project in my spare time. Do you have any recommendation for Access books/relational database books. I would need it mostly for more complex queries like the one you did (Unions, Inner, Outta Join, etc.) and doing forms/reports. Looking back at the book I used 4/5 years ago it doesn't go too in depth with the more complex theories. Most of what I learned must have been in projects because I know we did more than the book covers. I just wish my computer hadn't crashed with my old projects so I had some kind of background other than memory. My old book I have is Data Management Databases and Organizations Fourth Edition by Richard T. Watson. Its defintely more about modeling and architecture.
Thanks again for all your help.
First off, I would suggest you go to this web site.
http://www.techonthenet.com/access/index.php
and this one
http://www.datapigtechnologies.com/AccessMain.htm
and this one
http://www.databasedev.co.uk/general.html
Save them in favorites and refer to them with issues. There are many others you can find with google. The only book that I have used is The Complete Reference Access 2002 by Virginia Anderson. Probably an updated version is available. I bot on Amazon (used) for little $$$.
You can always post back with questions. Good luck.
Alan
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks