Duplicates in union query

[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

 

[Bob Barrows]

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?

No. GROUP BY is necessary when the content of a single column (or subset of
columns) determines whether or not a duplicate exists.

 

[Peter Kinsman]

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

 

[Bob Barrows]

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.