Microsoft Access Help
Author
Discussion

Neptune188

Original Poster:

348 posts

208 months

Tuesday 28th August 2018
quotequote all
Calling on the wisdom of PH...please move if not appropriate here.

I have an access database with 8 tables all of which are identical. Each record has a key which is unique to that table but common across each table (IE the whole database each table will only have one instance of XXX but there will be 8 instances of XXX in the whole database). The tables are all related to each other using this key.

I'm trying to write a query that will look for a certain value (A geographic location identifier) across all tables and return the records associated with this identifier - but I can't work out how to do it in the query wizard and much googling is drawing a blank.

Can anyone provide some pointers?

Greenmantle

2,257 posts

139 months

Wednesday 29th August 2018
quotequote all
Use SQL Parse through. Data in Access can be retrieved by writing TSQL code which is easier to do rather than using the ancient access query GUI.

select field1 from table a where field1 = 'VALUE'
union
select field1 from table b where field1 = 'SAME VALUE'
union
select field1 from table c where field1 = 'SAME VALUE'

etc

obviously if you want to join the tables together via this key then its a different query
you will need to provide more info before I can make that call.

John

Neptune188

Original Poster:

348 posts

208 months

Thursday 30th August 2018
quotequote all
I had originally intended for all the tables to be joined together by this key, but will try this and see if it works.

Basically each table is a location, date, supplier, quantity and price. I'm looking for a user to select a location, date and supplier to return the quantity required and price. I get a new table weekly - I can kind of work around it by appending the new table to a combined table but it's a bit messy.