Where's Used based on 100 + tables

[Dave14867]

Hello all,
I have a database that I have developed over the last 3 years or so that works great but I want to add functionality to it. I want to do a where's used report and have the report tell me in what different products any given part number is used. I have 101 different tables (BOM's) and I want to to able to input a part number and have the query go thru each table and indicate the Parent Item and the Part Number in question with the Qty.

How would I go about this?

I did have this functionality previously but just used 1 table but this was abandoned because of the time it took to look things up but that was also running across a network which I now run locally then once a week copy the BE file over to the network.

Thanks in Advance

Dave

[ranman256]

WHY are there 100 tables? this seems wrong.

youd need to program a routine the go thru each table to produce a query to load the results to the tResult table.
a form would have the search item: txtFind
then the code would query all the tables
either use a list of the tables,
or
cycle thru the table names to get only Bom tables (my example)
put all results into the tResult table

dim tdf as tabledef
dim db as database
dim sSql as string

docmd.Setwarnings false
set db = currentdb
for each tdf in db.tabledefs
    'get the Bom tbl
   if instr(tdf.name ,"Bom")>0 then
     sSql = "Insert into tResults Select * from " & tdf.name & " where [name] = '" & me.txtFind & "'"
     docmd.runSql sSql
next 
docmd.Setwarnings true
docmd.Opentable "tResults"

[Minty]

I agree with Ranman, you shouldn't have a table per BOM?

That's a fundamental design problem.
Every time you add a new BOM you create a new table, then have to add or change your forms queries etc. to accommodate it?
That isn't correct.

[ranman256]

Can you not merge all the tables into 1 Bom?

[orange]

Dave,

Further to ranman's advice, please describe exactly what you need. Is it a field/control name where used or the value of specific data?

[Dave14867]

So you both recommend going back to a single BOM table. I could take the time to create a new tblBOM and then append all of the individual ones to that one. That did work but became extremely slow over the network.

[Minty]

If network speed was a concern then maybe the forms displaying the data need to be designed to pull less data through when they load?

Could you show us the current table designs?

[Dave14867]

Unfortunately I cannot post much, I may create a dummy table to illustrate what I am working with. Most tables (Bom's) have 2 to 12 items. The speed really became an issue, running it locally solved all of that.

[Minty]

A dummy version would help.

I'm just doing some basic maths 100 ish BOM's with max 12 lines is only 1200 records?
I'm struggling to see how can that be slow?

I suspect there was something else causing this performance issue?

[CJ_London]

usual design culprits for poor performance are lack of normalisation, lack of indexing, forms/reports based on whole tables and filtered rather than applying criteria before fetching the data. The openform command misleadingly has a WHERE parameter which implies criteria but actually, its a filter.