Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2011
    Posts
    15

    Unanswered: Query not calcualting correctly

    Hello. I have a Project database that has a query to calculate the quantities that need to be ordered. Items are entered in a subform which populates a table. I created a query that takes the QTY Needed subtracts QTY on hand and Qty on order. The first query pulls the QTY needed from the table and what I want is for it to sum what is needed for each part number. What is happening is for certain items it is generating numbers that are way out of whack.

    example:
    inventory system shows 175 routers on hand
    we need 717 for 3 different projects
    we have 598 on order
    so it should show that I have 56 left, which would not show up on the TO BE ORDERED REPORT.

    What I am getting from the query is 2151. Which is 717*3... why is this happening and how can I fix it?

    Thanks in advance!

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Usually, I get such errors when there are multiple matches of items in various queries. For example, if one of your sub queries pulls up multiple rows of the same item, you could get unexpected results. For more specific help, I would suggest posting the sql text.
    John M Reynolds

  3. #3
    Join Date
    Jun 2011
    Posts
    15
    that is exactly what is happening. I am not very familar with SQL so when asked to post the SQL txt I am not real sure what to post.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so show us your SQL
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    When you are viewing your query, depending on your version of access, there may be a tool in the top right that allows you to select query by example, show data, or SQL. Choose SQL, copy what you find there, and paste it here.
    John M Reynolds

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its has to be a malformed join, so in the abnsence of the SQL there's stuff all we can do
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2011
    Posts
    15
    Awesome!! Thank you for telling me how to get to the sql. here it is.....

    SELECT PRODUCTS.[ITEM NUMBER], [ALL INVENTORY].[Item Description], Sum(PRODUCTS.QUANTITY) AS SumOfQUANTITY, Projects.Completed
    FROM Projects INNER JOIN ([ALL INVENTORY] INNER JOIN ((PRODUCTS LEFT JOIN [GP Import Query Query] ON PRODUCTS.[ITEM NUMBER] = [GP Import Query Query].[Item Number]) LEFT JOIN [PO Query Query] ON PRODUCTS.[ITEM NUMBER] = [PO Query Query].[Part Number]) ON [ALL INVENTORY].[Item Number] = PRODUCTS.[ITEM NUMBER]) ON Projects.[Project Name] = PRODUCTS.[PROJECT NAME]
    GROUP BY PRODUCTS.[ITEM NUMBER], [ALL INVENTORY].[Item Description], Projects.Completed
    HAVING (((Projects.Completed)=0));
    Last edited by glb466; 08-12-11 at 11:10.

  8. #8
    Join Date
    Jun 2011
    Posts
    15
    That bad???

  9. #9
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I have no idea what data is coming from where. When it comes to eliminating duplicate records, save your query as a new query, remove a table from the query, then see if that gets rid of the duplicates. If not, then continue to remove tables and check if that was enough. The problem is most likely that you are not joining primary keys, but I cannot tell without knowing your table structure.
    John M Reynolds

Posting Permissions

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