query to select table based on another table feild value

[hyperionfall]

i have a query that makes a new table and renames (to that systems id, system#xxx_qty&loc) it when a new system is added. the new table tracks the quantities and location of parts for that new system, each system is unique. i also have a table (system id) that has a single feild that identifies the system (multiple front ends). i need to make a query that will select the correct table (system#xxx_qty&loc) based on the field value of the system id table. any ideas?



thanks for the help guys

[pbaldy]

It is almost always a mistake to have multiple tables like that. In any case, you would have to build the SQL in code to have a dynamic table.

[hyperionfall]

ye i know i should not set tables up that way but my manager wants everything on sharepoint and to keep the table size small enough for sharepoint to handle i need to have seperate tables for each system..i may be missing something in the design i dont know. what i have is an inventory list that each system uses. each system has its own locations for parts so i have to track each system with its location and quantities. each system has about 3 thousand parts with about 25 systems so that means i end up with one table with about 75000 records. share point willl not handle this