Results 1 to 9 of 9
  1. #1
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    55

    Query sum much higher than it should be

    I am trying to sum the totalCost of each productName from this query (this shows all entries for Hot Like Me):
    Click image for larger version. 

Name:	hot1.PNG 
Views:	20 
Size:	23.0 KB 
ID:	45027
    in the qryBatchCost query:

    Click image for larger version. 

Name:	hot2.PNG 
Views:	18 
Size:	7.1 KB 
ID:	45026

    But as you can see, the total it gives me is much higher than it should be. I cannot see why.

    Built with the query designed, but for reference, the SQL:


    Code:
    SELECT qryCostingPerOz.productName, Sum(qryCostingPerOz.totalCost) AS SumOftotalCostFROM tblMaterials, qryCostingPerOz
    GROUP BY qryCostingPerOz.productName;

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,715
    You haven't joined your two tables on anything

    FROM tblMaterials, qryCostingPerOz

    So you Are getting a cartesian join, which will join every record in one table to the other
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,310
    I think it should be (why have tblMaterials in there):

    SELECT qryCostingPerOz.productName, Sum(qryCostingPerOz.totalCost) AS SumOftotalCost FROM qryCostingPerOz
    GROUP BY qryCostingPerOz.productName;

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    55
    Good points! I was intending to solve a new problem to me, which I wasn't going to get into in this thread, but it explains why I have tblMaterials

    qryCostingPerOz helps find the cost per oz of the hot sauce produced. But then when we bottle them, there is a fixed cost per bottle for a definite amount of ozes. So I planned to track these fixed unit costs in the second table. This query is intended to help me get a final cost per bottle. I'm still working out the best way to do this. I forgot to account for the existence of that second table, since I figured the query would ignore whatever I didn't ask it explicitly to sum.

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,310
    No it won't, adding the second table will create duplicates which will skew your totals as you experienced. You can get your fixed cost by using a dLookup on the second table or create a third query. Hard to give you a definite answer without seeing what you have, but hopefully it will put you on the right track.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    55
    Quote Originally Posted by Gicu View Post
    No it won't, adding the second table will create duplicates which will skew your totals as you experienced. You can get your fixed cost by using a dLookup on the second table or create a third query. Hard to give you a definite answer without seeing what you have, but hopefully it will put you on the right track.

    Cheers,
    If you're interested in taking a look, I've attached a zip version. It's a very small DB so far and I didn't bother scrubbing the data because it's not that critical, although I guess you'll have our recipes if you want to try making some hot sauce.

    If you have a suggestion on the best way to use the cost per oz and the fixed cost per bottle to generate a resultant total cost per 5 oz and 2 oz bottle I'm very happy to hear it! I'm not sure what to do with the data in the tblMaterials table since it doesn't really need to be related to anything, it just stores data that we will pull it at one point to calculate. I like keeping it as a table rather than obfuscating it as a fixed value in a calculation so that we can update the cost/supplier over time through the form.

    BBCo.zip

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,310
    Here is one way to do it using domain aggregate functions.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    55
    Quote Originally Posted by Gicu View Post
    Here is one way to do it using domain aggregate functions.
    Cheers,
    Vlad
    Thanks Vlad, I'll try this with some modifications, for example I'll need to divide the total batch cost by average yield before adding on fixed cost per bottle. One follow-up question: are these in-query functions slower than a standard query? There's two reasons I'm hesitant to lean too heavily on these; one, complexity of updating/changing them vs simplicity of a query, and two, potentially the processing speed.

  9. #9
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,310
    Yes they could be potentially slower but with much bigger recordsets (thousands of records). You can do the same with totals queries and add them to the final totaling query but you have to make sure you use appropriate criteria so they only return the desired record.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 5
    Last Post: 09-23-2020, 02:31 PM
  2. Want to add buttons to go one record higher
    By hendrikbez in forum Access
    Replies: 6
    Last Post: 12-15-2015, 12:24 PM
  3. How to Get Data to Override Higher Amounts
    By ns8069 in forum Database Design
    Replies: 1
    Last Post: 01-28-2015, 02:46 PM
  4. Replies: 5
    Last Post: 06-04-2012, 11:55 AM
  5. Replies: 0
    Last Post: 08-19-2008, 11:12 AM

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 - Senior Forums