Need help creating a nested query? or macro? or other option that will make this work

[DBTechServ]

Hi all.
I have an access 2010 database which makes use of multiple tables and all use the same structure. Each table is named based on an unique project alpha-numeric identifier and contains that specific project's bill of materials.
What I need is to be able to query multiple tables for a specific part id number and only list tables that contain that item.

Below is a working query that list a range of table names based on the hard coded values that I am testing with. I will eventually want to change that to variables that are entered at runtime -

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name)>"200100" And (MSysObjects.Name)<"200201") AND ((MSysObjects.Type)=1));

Below is a working query that list the all of the parts used in a specific project -

SELECT [200100 VPK-260].[ID NO], [200100 VPK-260].DESCRIPTION, [200100 VPK-260].[MFG NO]
FROM [200100 VPK-260]
WHERE ((([200100 VPK-260].[ID NO])="77101664"));

I am not a SQL programmer and any help would be appreciated.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DBTechServ]

Thanks PHV.
The database was created long ago by others and I can't change it now.... just having to work within it. I am trying to keep from having to open each table and manually extract the pertinent data. I have approximately 15 part numbers to search for in nearly 600 tables at this time and want to develop a way to retrieve the information that can be reused in the future.

 

[PHV]

So, you can't do it in SQL.
I'm afraid you have to use VBA.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

A BRIEF overview of what the VBA should do...

Use a recordset to loop the table names...

For each pass through the loop concatenate a SQL string to execute... and then... it gets interesting... You might put the results in a new table... Either another recordset or make your SQL insert statements and run them.

 

[DBTechServ]

Thanks PHV and lameid.
I guess I will have to break down and create a looping procedure.