Is there an easy way to change the table that a query is using? I am going to have several tables (Bill of Materials) for different Jobs, and wanted to keep the jobs in ine database, will each job being its own table. Then I could run my queries and generate an organized bill of materials, and will be able to look back later and see what parts are common in each job (table).

Basically what I am looking to do is have the user prompted to select which table to use to run the queries.

Thanks for any help!

Hi Joshm

Welcome to the forum

It is possible but not trivial. Before you settle on this design take a deep breath, read the link, count to 3 and be certain you want to go down this route. Under most circumstances - this would be considered bad design.

http://r937.com/relational.html

pootle flump ur codings are working excelent.

... and that has got to be the record date-of-joining-to-first-post difference ever

pootle flump ur codings are working excelent.

Snarky: This has (pardon my French) cluster-f**k written all over it ... Bad design? Hell yes. Nobody designs a DB to have each job be in its own table ... You can analyze material commonalities within a single table ... It's called reports.

Hey Poots, doesn't Access still have a limit on the # of physical tables allowed? I know it was 255 as of A2K ...

So this design MAY blow up in you face is short order ...

Yes to Pootle Flump! Usually you want to keep fields that collect the same information in 1 table and "group" them by another field similar to a "category" type field. It makes it easier in the long run on design and reporting.

Yeah, it been awhile. I originally joined to fix another problem with another table and ended up corresponding away from the forum.

Any suggestions on another way to approach this situation?

Ah - the world is back in order

Dunno - never got close to 255 in Access quite frankly.

joshm - what was the design of the original Jobs tables?

pootle flump ur codings are working excelent.

I am uning Inventor to create 3d Drawings... I was hoping to export my bill of materials to Access, keep some kind of order between jobs (drawings), and be able to generate an organized bill of materials and be able to search the different jobs for common parts (for example all jobs that use a certain model pump)

That last statement is another good reason why a nice, relational design is best.

By design of jobs table I meant like
Table:
Jobs
Fields:
Job_ID Integer
Job_Name Text(30)

etc etc

pootle flump ur codings are working excelent.

Table: (Job Name) for which ever job

Fields:
Item
Part Number
Description
Qty
Cost
FloodedWeight
Weight
Labor
PL (pipe length)