+ Reply to Thread
Results 1 to 12 of 12

Query help

  1. #1
    Registered User
    Join Date
    10-27-2010
    Location
    NC, United States
    MS-Off Ver
    2007
    Posts
    32

    Query help

    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

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Query help

    Try this:
    Please Login or Register  to view this content.
    Change the order of your fields in the query.
    Last edited by alansidman; 11-17-2010 at 12:06 PM. Reason: Forgot Period inSQL
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    10-27-2010
    Location
    NC, United States
    MS-Off Ver
    2007
    Posts
    32

    Re: Query help

    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?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Query help

    James;
    To make sure we are on the same page, it might be beneficial if you posted your db with soome sample data.

  5. #5
    Registered User
    Join Date
    10-27-2010
    Location
    NC, United States
    MS-Off Ver
    2007
    Posts
    32

    Re: Query help

    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.
    Attached Files Attached Files

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Query help

    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

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Query help

    forgot to attach.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-27-2010
    Location
    NC, United States
    MS-Off Ver
    2007
    Posts
    32

    Re: Query help

    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.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Query help

    Ok. I'm clear now. I have an idea. Need an hour.

  10. #10
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Query help

    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
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    10-27-2010
    Location
    NC, United States
    MS-Off Ver
    2007
    Posts
    32

    Re: Query help

    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.

  12. #12
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Query 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1