Run query from different tables based on user input

[Vacco]

I have an inventory database with tables for each type of item in inventory. Each table has the same feilds for the equiptment type. Is there anyway to run a query from different tables based on user input before query is run?

Thanks Vacco

 

[Golom]

Not really. A saved query is a compiled (or partly so) object and, as such, it's table references are fixed (i.e. you can't change the FROM-clause information because the "compiled" part of the query would no longer be valid.)

To do this you would need to build the query in code using the table name(s) that you require. You can then run the query and retrieve it into a recordset or you can create a new version of the query from the code-created SQL.

 

[dhookom]

Since "Each table has the same feilds for the equiptment type", another method would be to create a union query from the separate tables and then query based on the name of the original table. You SQL might look like:

SELECT "Monitor" as EquipType, *
FROM tblMonitors
UNION ALL
SELECT "Printer", *
FORM tblPrinters
UNION ALL...

You could then create a query based on the EquipType field.

BTW If all the fields are the same, then I would have only used a single table which would look much like the results of the union query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]