How do I stop my union query displaying an ID number?

[Guest]

I am querying 3 tables in a union query, tblInvoices, tblDebtors and
tblContracts. I have another table called suppliers, with 2 fields, ID &
Name. The bound column in the 3 main tables is to the supplier ID, but I have
adjusted the column widths to 0,10 so the supplier name is shown. The
supplier name is displayed when I run a normal query on 1 table, but when I
run a union query, it displays the supplier ID. Is there any way I can show
the supplier name - or any other way of querying/reporting on 3 tables at
once.
Any help would be great...

 

[Douglas J. Steele]

Sounds as though you're using Lookup fields in your tables. See
http://www.mvps.org/access/lookupfields.htm at "The Access Web" for an
explanation of why most of us feel lookup fields are a bad idea.

If you're stuck with your design, you'll need to join each of the tables
with the Supplier table:

SELECT tblInvoices.Field1, tblInvoices.Field2, tblSuppliers.SupplierName
FROM tblInvoices INNER JOIN tblSuppliers
ON tblInvoices.Supplier = tblSuppliers.SupplierId
UNION
SELECT tblDebtors.Field1, tblDebtors.Field2, tblSuppliers.SupplierName
FROM tblDebtors INNER JOIN tblSuppliers
ON tblDebtors.Supplier = tblSuppliers.SupplierId
UNION
SELECT tblContracts.Field1, tblContracts.Field2, tblSuppliers.SupplierName
FROM tblContracts INNER JOIN tblSuppliers
ON tblContracts.Supplier = tblSuppliers.SupplierId

 

[Guest]

Thanks Douglas, I think that might work.... although now I am really worried
having read your link. I though I was following the rules when I created the
suppliers table with the supplier ID. If you had company names repeated in 3
tables, what would you have set up? (I am very new to and self taught in
Access..) I thought about having the supplier name as primary key and binding
it to that column.
Anything you could point me towards would be a massive help.
Thanks
Nat

 

[Douglas J. Steele]

What a lookup field does (as you've discovered) is simply store the Id of
the related value, not its actual text. In actual fact, that's what you
should do.

However, there's little reason to do it using a lookup field. Simply put
SupplierId (presumably a Long Integer) in each of the 3 tables, and join
each to the Supplier table, as I illustrated. Lookup fields may have their
place when you're updating tables directly, but you should never be doing
that. You should always be using forms, and you can create your own
combobox, with a query against the Supplier table as its row source, bound
to the Supplier Id in the table, so that you get the equivalent effect of
using a lookup field.