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]

Post by 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?

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

Post by Bob Barrows

Post by 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?

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

[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.

Post by 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

Post by Bob Barrows

Post by 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?

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