union query?

[Guest]

I have four tables that I need to query to use as the control source for a
combo box. The combo box is cbLocations and the four tables and fields are:

[tblCustomerDCs].[txtCustomerDCID].[txtCustomerName]
[tblCustomerIDs].[txtCustomerID].[txtCustomerName]
[tblFacilities].[txtFacilityID].[FacilityName]
[tblSupplierIDs].[txtSupplierID].[txtSupplierName]

I need to store the txtID values.

All of the tables have a different number of records and may also have
similar IDs.

How in the world can I manage this? A union query seems the answer but I
don't want to waste a bunch of time with that if there's a better way.

Your help is greatly appreciated!

 

[Marshall Barton]

I have four tables that I need to query to use as the control source for a
combo box. The combo box is cbLocations and the four tables and fields are:

[tblCustomerDCs].[txtCustomerDCID].[txtCustomerName]
[tblCustomerIDs].[txtCustomerID].[txtCustomerName]
[tblFacilities].[txtFacilityID].[FacilityName]
[tblSupplierIDs].[txtSupplierID].[txtSupplierName]

I need to store the txtID values.

All of the tables have a different number of records and may also have
similar IDs.

How in the world can I manage this? A union query seems the answer but I
don't want to waste a bunch of time with that if there's a better way.

A UNION query sounds like a good idea, but it depends on
what you mean by "may also have similar IDs".

If the result requires unique IDs, you might(?) have a
problem. You can check this by using the query wizard to
create a Find Duplicates query based on the UNION query.

Note that if a duplicate ID also means a duplicate name,
then use a UNION query. If none of the records in the
results of the UNION query can be duplicate records, then it
would be more efficient to use a UNION ALL query.

 

[Guest]

Thanks, Marshall.

A UNION query sounds like a good idea, but it depends on
what you mean by "may also have similar IDs".

If the result requires unique IDs, you might(?) have a
problem. You can check this by using the query wizard to
create a Find Duplicates query based on the UNION query.

I just checked and there are 11 duplicate numbers.

Note that if a duplicate ID also means a duplicate name,
then use a UNION query. If none of the records in the
results of the UNION query can be duplicate records, then it
would be more efficient to use a UNION ALL query.

So I was tinkering:
SELECT [txtCustomerDCID] AS Location FROM [tblCustomerDCs]
UNION SELECT [txtCustomerID] FROM [tblCustomerIDs]
UNION SELECT [txtFacilityID] FROM [tblFacilities]
UNION SELECT [txtSupplierID] FROM [tblSupplierIDs];

Of course, this returns everything except the 11 duplicates as mentioned
above however no IDs will have duplicate Names. Could adding Names to the
query "force" the return of the other 11 records? How do I do that? Can it be
done?

This is all very confusing for me as I've only used union queries a couple
times and for nothing this tricky.

Thanks!

[tblCustomerDCs].[txtCustomerDCID].[txtCustomerName]
[tblCustomerIDs].[txtCustomerID].[txtCustomerName]
[tblFacilities].[txtFacilityID].[FacilityName]
[tblSupplierIDs].[txtSupplierID].[txtSupplierName]

 

[Marshall Barton]

A UNION query sounds like a good idea, but it depends on
what you mean by "may also have similar IDs".

If the result requires unique IDs, you might(?) have a
problem. You can check this by using the query wizard to
create a Find Duplicates query based on the UNION query.

I just checked and there are 11 duplicate numbers.

Note that if a duplicate ID also means a duplicate name,
then use a UNION query. If none of the records in the
results of the UNION query can be duplicate records, then it
would be more efficient to use a UNION ALL query.

So I was tinkering:
SELECT [txtCustomerDCID] AS Location FROM [tblCustomerDCs]
UNION SELECT [txtCustomerID] FROM [tblCustomerIDs]
UNION SELECT [txtFacilityID] FROM [tblFacilities]
UNION SELECT [txtSupplierID] FROM [tblSupplierIDs];

Of course, this returns everything except the 11 duplicates as mentioned
above however no IDs will have duplicate Names. Could adding Names to the
query "force" the return of the other 11 records? How do I do that? Can it be
done?

[tblCustomerDCs].[txtCustomerDCID].[txtCustomerName]
[tblCustomerIDs].[txtCustomerID].[txtCustomerName]
[tblFacilities].[txtFacilityID].[FacilityName]
[tblSupplierIDs].[txtSupplierID].[txtSupplierName]

You could get all of the IDs just by using UNION ALL, but it
seems like it would be meaningless to see the 11 duplicate
ID with no other information.

If you want the names too, then I think you still want to
use UNION ALL simply because you do not need the extra
processing to eliminate duplicates.

If I understand what you are doing, I think this will do
what you asked:

SELECT txtCustomerDCID AS Location,
txtCustomerName As LocName
FROM tblCustomerDCs
UNION ALL
SELECT txtCustomerID, txtCustomerName
FROM tblCustomerIDs
UNION ALL
SELECT txtFacilityID, FacilityName
FROM tblFacilities
UNION ALL
SELECT txtSupplierID, txtSupplierName
FROM tblSupplierIDs