Results 1 to 10 of 10
  1. #1
    Vinagray@gmail.com is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    5

    Sum function within query


    I have an inventory database. The key is the 'page' number the goods came in on. It's a 3 digit number. At the end of the last fiscal year, the CFO decided that instead of expanding the field to 4 digits, as the last 'page' was 977, he wanted to restart the count at 100. All goes well except when I now run the end of the month inventory, and within the report query is sum function, it does the math perfectly well for any goods still in inventory for pages up to 977, but does the math wrong on the pages starting with 100. Is there any explanation you can think of?

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are there old records with the same page numbers? The query wouldn't know to exclude them.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Vinagray@gmail.com is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    5
    No. The 'oldest page' is 675, and the new ones only go to 180 thus far.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I personally wouldn't have gone back, given that sooner or later you're going to run into existing numbers. What's the calculation? Can you attach the db here? I assume you're summing an amount field while grouping on the page field. If so, I can't think of why the newer ones wouldn't work, despite them having lower numbers.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Vinagray@gmail.com is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    5
    Hi. I cannot find the manage attachments button the do that.

    Here is the code though. IN these phrases within the query " Sum(Round([units]*[Duty Per Unit],2)) AS Duty, Sum(Round([units]*[Freight per pcs],2)) AS Freight, Sum(Round([units]*[Handling per pcs],2)) AS Handling" for all the 'pages' between 100 and 175 the math is incorrect. Anything greater, and the math is correct. If you show me how to attach the actual database, I will do that.

    SELECT Inventory.Div, Inventory.Style, Inventory.Color, Inventory.Page, Inventory.Sub, Sum(Inventory.Units) AS PCS, Sum(Round([units]*[Unit Price],2)) AS Purchases, Sum(Round([units]*[Duty Per Unit],2)) AS Duty, Sum(Round([units]*[Freight per pcs],2)) AS Freight, Sum(Round([units]*[Handling per pcs],2)) AS Handling, Round(Sum(Round([units]*[Unit Price],2))+Sum(Round([units]*[Duty Per Unit],2))+Sum(Round([units]*[Freight per pcs],2))+Sum(Round([units]*[Handling per pcs],2)),2) AS Inventory, Avg(Inventory.Cost) AS Average
    FROM Inventory
    WHERE (((Inventory.Period) Between [forms]![month end inventory]![start date] And [forms]![month end inventory]![end date]))
    GROUP BY Inventory.Div, Inventory.Style, Inventory.Color, Inventory.Page, Inventory.Sub
    HAVING (((Inventory.Div)="d" Or (Inventory.Div)="s") AND ((Sum(Inventory.Units))<>0))
    ORDER BY Inventory.Div, Inventory.Style, Inventory.Color, Inventory.Page, Inventory.Sub;

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I don't see anything wrong with that offhand. If you click on Go Advanced under the quick reply, you should see a Manage Attachments button. There you can attach the db. You may need to compact/repair and zip first.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Vinagray@gmail.com is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    5
    I cannot attach the Db as even after compacting, it is 16,000 kb large.

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Even after zipping? You can email it to me if it's still too big, or delete some unrelated tables from a copy.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Vinagray@gmail.com is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    5
    I emailed it as it was still 1600 Post zip!

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What's an example of an item that's wrong, and what should it be for what parameters?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. datediff function in query
    By LeesKeys in forum Queries
    Replies: 3
    Last Post: 06-23-2016, 08:54 AM
  2. Function won't run in a Query
    By Paul H in forum Programming
    Replies: 12
    Last Post: 07-08-2015, 12:16 PM
  3. JOIN query with SUM function
    By BigMikeM in forum Queries
    Replies: 1
    Last Post: 06-25-2015, 09:33 AM
  4. VBA function query
    By Pacific1 in forum Programming
    Replies: 1
    Last Post: 12-04-2014, 01:19 PM
  5. Replies: 4
    Last Post: 11-19-2010, 07:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums