Find duplicate query varies

[jbrown0828]

I am new to tek-tips so if this has been discussed my apologies. The problem is when I use the wizard to create a query to examine duplicates based on an order number, I get 5278 records. Then I add some necessary fields to the Query and as I do the record count becomes less (5241). I looked at the SQL and it seems fine. Any ideas. Thanx. Jim Brown

 

[blom0344]

Hello Jim,

Without an example of your query this is hard to answer. If your query runs on one table alone, adding output fields should not influence the number of records you are getting.
However if you have joined a set of tables and start dragging in fields from one of the other tables, your SQL will extend with a where clause with the join definition between the 2 tables. If this is a inner join, dragging in output fields could then restrict the number of records returned. One way of dealing with this is setting the original table as a master (changing the join type in design between the two tables) and checking if this changes the number of records in output

 

[jbrown0828]

To answer the last post, the data is contained in one table and here are the queries in question.

The query that only evaluates duplicates and contains 5278 records is as follows:

SELECT DISTINCTROW First(POTbl.POComboID) AS [POComboID Field], Count(POTbl.POComboID) AS NumberOfDups
FROM POTbl
GROUP BY POTbl.POComboID
HAVING (((Count(POTbl.POComboID))>1));


The query that has additional (necessary) fields and contains fewer records (5241) is:

SELECT DISTINCTROW First(POTbl.POComboID) AS [POComboID Field], Count(POTbl.POComboID) AS NumberOfDups, POTbl.Ord_No, POTbl.Line_No, POTbl.Vend_No, POTbl.Item_No, POTbl.Item_Desc_1, POTbl.Item_Desc_2
FROM POTbl
GROUP BY POTbl.Ord_No, POTbl.Line_No, POTbl.Vend_No, POTbl.Item_No, POTbl.Item_Desc_1, POTbl.Item_Desc_2,POTbl.POComboID
HAVING (((Count(POTbl.POComboID))>1));

Thanx, Jim