How to Search through Multiple Tables that are different in one database.

[Mrdude1020]

Im working on a cross comparison database for the three food vendors for my boss. What is the best way to search across the three tables? They all have similar record titles but arent all uniform, and I cant change the records because I have to have the record names the same as the files that are being exported from the vendors websites. What would be the easiest way to search across three similar but not unifrom tables in one database? Ive also tried to make three buttons to update the lists after I download the items to have up-to-date prices on the products but am unsure if they are actually working correctly. Any advice would be greatly appreicated.

[ranman256]

You want a crosstab query OR
IF you can match the items then you want either an OUTER join query.

XTAB:
Make a UNION queries that has the same column count/subject (they dont need to have the same field names, just the same type data)

select vendor, item, cost from tbl1
union
select vendor, item, cost from tbl2
union
select vendor, item, cost from tbl3

save as qs3VendorPrices.

THEN use the wizard to create a CROSSTAB query from qs3VendorPrices and it will build a list for you on matching items.
ITEM, VENDOR1, vendor2, vendor2
spam $ $ $
Tuna $ $ $

[NTC]

if you seek to compare items - which are differently named, and there is no other field by which to join tables - then the real fix is to make a translation table:
Common: Variation
Apple : Granny
Apple : Macintosh

It would need to contain every item with a common and all variations. Making that could be a lot of grunt work. But once you have it - then you can join to it in each of the 3 tables separately. Once all tables are joined to it then you have the common field in all three tables and can then join on the common field to put the 3 items side by side.

[orange]

Dude,
It would be helpful if you could give an example, or more specific description, since readers seem unsure about your real requirement.