Qty Query applied to tables listed in another table?

[HughT]

I am working on a Bill of Material database. I have a " Table1" with fields
for 'qty' and 'table ref'. The quantity value is the quantity to be applied
to a separate tablename listed in the field "table Ref".

Example: Tablename listed in "table1" is Em102 and quantity is 2. What I
need to do is multiply the quantity values of items in the separate table
"Em102" by the quantity specified in Table1.

Table1 lists 10 different tables in the Table Ref field each with a
different quantity value to be applied to the corresponding separate table.

Any help would be appreciated.

 

[Allen Browne]

Very strange, Hugh.

The usual approach to BOM is to link the table to itself, rather than to 10
others. I don't quite follow, unless you have some kind of sub-classing
going on here.

In any case, the task could be done with outer JOINs, UNIONs, or subqueries.

a) Outer JOINs
Create the query with all 11 tables.
Make sure you use outer joins (double-click the line joining tables.)
This gives you independent quantities, so you will possibly end up with
calculated fields that try to patch the values together into one field.

b) UNION
If you want to be able to sum the quantities and see the output as if they
had all come from the one table, you could create a UNION query where each
of the 10 SELECTs gets the items from its related table.

c) Subquery
Withtout testing, I doubt you can put a field value into the FROM clause of
the subquery, so it will probably be a bunch of subqueries, each one
completely inside a Switch() or nested IIf().

 

[HughT]

Thankyou for your prompt response.

The databases are actualy derived from a series of 3d cad models comprising
a hierarchy of assemblies and sub assemblies. So each database is extracted
accordingly. The "table1" database has a field which has the table names of
all the other databases.

I have already built a query to import all the separate databases according
to the list in table1 into one database file. Now the table1 also lists the
quantities of each sub assembly required for the project. So we might have
just one sub assembly database or we could require 2 or 3 depending on
project.

What I need to do is associate the tablename with the corresponding table
from the other files and apply the quantity in table1 to them also. I hope I
am explaining this clearly.

 

[Allen Browne]

So which of the 3 suggestions suits you best?

 

[HughT]

To be honest I am not sure.

The databases are not linked, there are no corresponding fields in Table1
that associate directly with any fields in the other databases except for the
Tablename values.

I have tried to figure out if I can incoprorate the tablename as a field
entry in each of the sub databases and therefore creating a link property.

Perhaps if there was some way I could send you a copy of this database that
would help. I am not sure I am explaining this well.

I have already looked at ways of creating a recordset that I could associate
with the correspondng table name and apply the quantity criteria - but
without success.

Unofrtunately the structure of the databases are defined on extraction from
the cad system.

 

[Allen Browne]

Okay, if you can't identify which record matches which one in the other
table, I guess you won't be able to look it up.

If you can match them, a 4th possibility would be DLookup(), since you can
use the field to supply the table name as the 2nd argument.

 

[HughT]

Allen, this sounds like a way forward. Dlookup was not something I had
considered.

Thank you very much.