How can I add the table name (dynamically) as a field in a query

[rarchamb]

Here is my dilemma,
We are a manufacturing company - we have a HUGE database of parts as well as assemblies where these parts are (or can be) used. Each assembly is saved as its own table listing internal and external part numbers, descriptions and quantities used. We have HUNDREDS of assemblies and TENS OF THOUSANDS of parts. A finished product is comprised of several assemblies, each of which, as explained above, is comprised of several parts. The SAME part (i.e. 1.5 inch bolt) can be used in one or more assemblies.
I have a query that will show me all the parts used in a build (using several assemblies) as well as the quantities of these parts used (for the total build), but it does NOT tell me for which assembly each of these parts are required. I am including a PORTION of the query's SQL code (please note this needs to be compatible with Access 2016):

select [ACCELERATOR LINK].PNC, [ACCELERATOR LINK].[Part #], [ACCELERATOR LINK].qty
from [ACCELERATOR LINK] where qty is not null
union all
select [ARMREST & VISOR].PNC, [ARMREST & VISOR].[Part #], [ARMREST & VISOR].qty
from [ARMREST & VISOR] where qty is not null
union all
select [ASH RECEPTACLE].PNC, [ASH RECEPTACLE].[Part #], [ASH RECEPTACLE].qty
from [ASH RECEPTACLE] where qty is not null
union all
select [BATTERY CARRIER].PNC, [BATTERY CARRIER].[Part #], [BATTERY CARRIER].qty
from [BATTERY CARRIER] where qty is not null
union all


select [CAB MOUNTING & BODY].PNC, [CAB MOUNTING & BODY].[Part #], [CAB MOUNTING & BODY].qty
from [CAB MOUNTING & BODY] where qty is not null
union all
select [CAUTION PLATE].PNC, [CAUTION PLATE].[Part #], [CAUTION PLATE].qty
from [CAUTION PLATE] where qty is not null
union all
select [CONSOLE BOX & BRACKET].PNC, [CONSOLE BOX & BRACKET].[Part #], [CONSOLE BOX & BRACKET].qty
from [CONSOLE BOX & BRACKET] where qty is not null
union all
select [COWL PANEL & WINDSHIELD GLASS].PNC, [COWL PANEL & WINDSHIELD GLASS].[Part #], [COWL PANEL & WINDSHIELD GLASS].qty
from [COWL PANEL & WINDSHIELD GLASS] where qty is not null
union all
select [EMBLEM & NAME PLATE].PNC, [EMBLEM & NAME PLATE].[Part #], [EMBLEM & NAME PLATE].qty
from [EMBLEM & NAME PLATE] where qty is not null
union all
select [FLOOR INSULATOR].PNC, [FLOOR INSULATOR].[Part #], [FLOOR INSULATOR].qty
from [FLOOR INSULATOR] where qty is not null
union all
select [FLOOR MAT & SILENCER PAD].PNC, [FLOOR MAT & SILENCER PAD].[Part #], [FLOOR MAT & SILENCER PAD].qty
from [FLOOR MAT & SILENCER PAD] where qty is not null
union all
select [FLOOR MEMBER].PNC, [FLOOR MEMBER].[Part #], [FLOOR MEMBER].qty
from [FLOOR MEMBER] where qty is not null
union all
select [Frame].PNC, [Frame].[Part #], [Frame].qty
from [Frame] where qty is not null;

...and so on (it is actually 125 lines long for this particular PARTIAL build). I can also "pull" all my table names from the following query:

SELECT MSysObjects.Name AS table_name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~")
AND ((Left([Name],4))<>"MSys")
AND ((MSysObjects.Type) In (1,4,6))
AND ((MSysObjects.Flags)=0))
And (MsysObjects.Name) Not Like "AllPartsList*"
order by MSysObjects.Name

This gives me a list of my tables as found in this build. So what I need is to have the query insert another field where the name of the table (assembly in my case) is shown for each part listed.

So something like this:

select [CAUTION PLATE].PNC, [CAUTION PLATE].[Part #], {this table's name - CAUTION PLATE in this instance - as ASSEMBLY}, [CAUTION PLATE].qty
from [CAUTION PLATE] where qty is not null

for each line of the code above - Please also note that the "original" data for the assembly tables comes from EXCEL and NOT ACCESS and each individual line or section of code is originally assembled in Excel from a "concat" function so that we can verify the order or the commands and therefore minimize keying errors (as well as quickly repair these).

[andy49]

If they're all the same, you could pull the field names into a record set then build the query as a string.

[JoeM]

Each assembly is saved as its own table listing internal and external part numbers, descriptions and quantities used. We have HUNDREDS of assemblies and TENS OF THOUSANDS of parts.

Sounds like a design issue, which is making things much harder than they have to be.
Any time that you have many tables structured the same, that is usually a sign that they should all be in the same table. You just need to add another field or two, like: ASSEMBLY_NAME.
Remember, it is easy to filter on that field to only get the records for a particular assembly, but very cumbersome to sew multiple similar tables together, as you can see.
Another drawback is that UNION queries are not updateable.

[rarchamb]

As stated in the code snipet above, they are not all the same, although they do repeat on each line, so I guess I don't understand your answer or I need more clarifications..

[rarchamb]

It may very well be a design issue as the original tables or data sets (as I have stated) are in EXCEL and were "populated" by a non-IT person in a way that made sense to them (separating by assembly), and as I stated there are HUNDREDS of these. It would be too time consuming to re-enter all the data, so I need to work with the tools I've been given.

[JoeM]

It would be too time consuming to re-enter all the data, so I need to work with the tools I've been given.

You don't really need to re-enter all the data (at least not manually). You could use Append Queries to write all the data from all your smaller tables into one big table.
You could even come up with some VBA to do all that automatically. You would just need the list of tables to run though.

[HiTechCoach]

I do agree that you have a design issue. The fact that you have to UNION all the data from the multiple tables is a HUGE RED FLAG that they should have been combined into the same tables with a field for assembly.

So something like this:

select [CAUTION PLATE].PNC, [CAUTION PLATE].[Part #], {this table's name - CAUTION PLATE in this instance - as ASSEMBLY}, [CAUTION PLATE].qty
from [CAUTION PLATE] where qty is not null

The above is close.

You can create a calculated field to hold the assembly name like this:

..., "CAUTION PLATE" as ASSEMBLY, ...

Try this:

select [CAUTION PLATE].PNC, [CAUTION PLATE].[Part #], "CAUTION PLATE" as ASSEMBLY, [CAUTION PLATE].qty
from [CAUTION PLATE] where qty is not null

You will need to ass the calucated fielsin the same position in each query.

[rarchamb]

I do agree that you have a design issue. The fact that you have to UNION all the data from the multiple tables is a HUGE RED FLAG that they should have been combined into the same tables with a field for assembly.




The above is close.

You can create a calculated field to hold the assembly name like this:

..., "CAUTION PLATE" as ASSEMBLY, ...

Try this:

select [CAUTION PLATE].PNC, [CAUTION PLATE].[Part #], "CAUTION PLATE" as ASSEMBLY, [CAUTION PLATE].qty
from [CAUTION PLATE] where qty is not null

You will need to ass the calucated fielsin the same position in each query.

I ended up doing it "manually" as you've indicated above - it's just VERY tedious as there are something like 40 "tables" in this partial build.
Although I do agree that the design of the database is flawed, I needed to work with what I had (or have really) to start. Now that I have a query "template" to work with, I will be able to "correct" these and have them work properly. It just would have been nice to have some sort of "easy" code to fetch the table name from where data is pulled - I'm sure I am not the only one who's had this need.