none
Why would an Access query count something and double it? RRS feed

  • Question

  • Hello,

    I have set up some queries in Access to simply find duplicates in two tables. The duplicates meaning if the same P/N is present in each table then add to the queried list.

    However I've found that from Excel the count is much smaller. I have checked countless times and the Excel count is the accurate number.

    The Excel count is on the left and the Access count is on the right. There are more numbers that do match quantity to quantity so they have been omitted from this photo.

    A snapshot of what the query looks like:

    Below is the SQL code for the query:

    SELECT tbl_NEOCOP.PTNO, tbl_NEOCOP.Description, tbl_NEOCOP.Batch, tbl_NEOCOP.[Available Qty], tbl_NEOCOP.Condition
    FROM tbl_NEOCOP INNER JOIN tbl_Constrained ON tbl_NEOCOP.PTNO = tbl_Constrained.[P/Number]
    WHERE (((tbl_NEOCOP.[Available Qty])>="1") AND ((tbl_NEOCOP.Condition) Like 'AI'));
    

    Thursday, July 12, 2018 5:43 PM

All replies

  • Hello stillanoob,

    I note that you are using inner join, have you tried to use left join or right join? Could any of them return the correct count number for you?

    Besides, is the thread same same below thread? If not, please follow below thread to let us your current state of that thread. Thanks for understanding.

    How reliable is Conditional Formatting vs querying tables in Access?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, July 13, 2018 4:28 AM
  • No other JOIN statements returned the total I was looking for. The discrepancy is 40 total. I need a count of 860 and keep coming up with 900.
    Friday, July 13, 2018 10:48 AM
  • Nothing jumps out at me except for the criterion tbl_NEOCOP.[Available Qty] >= "1"

    Is Available Qty a text field? It seems to me that logically it would be a numeric such as an integer. Also, mixing single quotes and double quotes within a query might cause a problem.


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, July 13, 2018 8:26 PM
  • As you are joining the tables on non-key columns the query will return multiple instances of the same rows where there is more than one row in either table with the same part number value.  You cannot reliably use the DISTINCT predicate to suppress the duplicates because of the join on non-key columns, which means that there could be rows in either table whose values are legitimately duplicated over the subset of columns returned.

    A result table of those rows from tbl_NEOCOP, restricted on the Available Qty and Condition columns, where there is at least one match in tbl_Constrained on the part number, can be returned without any duplication, other than legitimate duplication in the base table rather than the result table, by means of the EXISTS predicate and a subquery:

    SELECT PTNO, Description, Batch, [Available Qty]
    FROM tbl_NEOCOP
    WHERE [Available Qty] >= 1
    AND Condition = "AI"
    AND EXISTS
        (SELECT *
         FROM tbl_Constrained
         WHERE tbl_Constrained.[P/Number] = tbl_NEOCOP.PTNO);

    I've assumed that the Available Qty column is a number data type, as would be expected for a quantity value.  Note the use of the equality operator rather than the LIKE operator for the restriction on the Condition column.  The LIKE operator should only be used for pattern matching, which is not the case here.

    Ken Sheridan, Stafford, England

    Sunday, July 15, 2018 12:09 PM
  • Interesting Ken,

    This query got me much closer. Now I'm at 858 parts compairing to the Excel file which has 860 only 2 off!!!

    I pasted this code in two other queries I was trying to do the same thing and those numbers are still way off. Really confused on this.

    Other query# 1 has 69 records in Excel yet the Access query is returning 272

    SELECT PTNO, Description, Batch, [Available Qty]
    FROM tbl_NEOCOP
    WHERE [Available Qty] >= 1
     AND Condition = "AI"
     AND EXISTS
         (SELECT *
          FROM tbl_BidPackage
          WHERE tbl_BidPackage.[Part Number] = tbl_NEOCOP.PTNO);


    Other query # 2 has 64 records in Excel yet the Access query is returning 167

    SELECT PTNO, Description, Batch, [Available Qty]
    FROM tbl_NEOCOP
    WHERE [Available Qty] >= 1
     AND Condition = "AI"
     AND EXISTS
         (SELECT *
          FROM tbl_ProgramManaged
          WHERE tbl_ProgramManaged.[P/N] = tbl_NEOCOP.PTNO);


    *** I realize that the Part Number column is different in all of the worksheets in that workbook that I've imported the data from ****

    *** I didn't author that file but they are all in fact Part Number columns regardless of how it was named by whomever made the file***

    • Edited by stillanoob Monday, July 16, 2018 11:51 AM
    Monday, July 16, 2018 11:49 AM
  • A query of this type, using the EXISTS predicate and a correlated subquery, will further restrict those rows from the recordset which would be returned by the outer query without the correlation, to those rows where there is a match in the recordset returned by the subquery on the column(s) on which the subquery is correlated with the outer query.  So, you need to ensure that the outer query and subquery would, without the correlation, return the recordsets you wish to compare.  In your case, without the correlation, the outer query is currently restricted on the Available Qty and Condition columns, but the subquery is unrestricted.  Should the subquery be restricted also?

    Ken Sheridan, Stafford, England

    Monday, July 16, 2018 1:15 PM
  • I'm not entirely sure how to answer that accurately Ken. I'm just trying to mirror conditional formatting from Excel files in Access.

    That is to say in our Excel file we have a worksheet with Part Numbers on it.

    There are numerous other worksheets in the workbook that have a list of part numbers that are flagged for particular reasons.

    On the main worksheet (NEOCOP) in the workbook we have a list of all of the part numbers. Our conditional formatting is setup to look to other worksheets in the workbook. "Constrained", "Program Managed", "Bid Awards", and "Ians List". If the Part Number column on the NEOCOP matches any parts in any of those other worksheets we have them color that part number on the NEOCOP worksheet a certain color.

    I've verified that the Excel conditional formatting is in fact accurate. As you can see from the numbers above Access numbers are way off from the numbers in Excel.

    This isn't a must that I complete this, however for common knowledge moving forward I would like to try and resolve this issue as I think it would be great for our team to be able to use this mini-database I've been trying to put together.

    I hope this clarifies what I'm trying to accomplish.

    Tuesday, July 17, 2018 11:52 AM
  • The query WILL return those rows in the subset of rows from tbl_NEOCOP where the value at the Available Qty column position is 1 or greater, where the value at the Condtion column position is AI, and where at least one row exists in tbl_ProgramManaged with the same value at the P/N column position as that at the PTNO position in tbl_NEOCOP.

    If the number of rows returned is not as expected, then either the outer query, unrestricted by the subquery,  and/or the subquery when not correlated with the outer query, are not returning the rows which you assume they are.

    Ken Sheridan, Stafford, England

    Tuesday, July 17, 2018 6:07 PM