Duplicates in union query

  • Thread starter Thread starter Peter Kinsman
  • Start date Start date
P

Peter Kinsman

I have a form with a combo box to select Vendors.
The rowsource was a union query selecting Suppliers from the AS/400 and a
few specials from a separate table - OK so far.
Now the client is in the process of transferring to Navision so I need to
add in the Navision Vendor table. The Purchase Ledger from the AS/400 was
imported but certain characters - like single quotes - were dropped. The
result is duplicated records where the Code is the same but the Name is
different.
Does anyone know if it is possible to remove the duplicates without using a
second GROUP BY query?

Many thanks

Peter Kinsman
 
Peter said:
I have a form with a combo box to select Vendors.
The rowsource was a union query selecting Suppliers from the AS/400
and a few specials from a separate table - OK so far.
Now the client is in the process of transferring to Navision so I
need to add in the Navision Vendor table. The Purchase Ledger from
the AS/400 was imported but certain characters - like single quotes -
were dropped. The result is duplicated records where the Code is the
same but the Name is different.
Does anyone know if it is possible to remove the duplicates without
using a second GROUP BY query?
Click to expand...
No. GROUP BY is necessary when the content of a single column (or subset of
columns) determines whether or not a duplicate exists.
 
It is a bit verbose, but I have added to the subsequent SELECT queries a
WHERE Code NOT IN clause with the text of the earlier queries within the
brackets.
This seems to work.

Peter
 
I'm happy it worked for you, but I personally would have gone for the group
by option - not only was it more work to add those subqueries, those
subqueries likely killed the performance.
 
Back
Top