Define Table with a Variable

[Bedsingar]

Hello,

I am trying to understand how I'd go about designing one query (Even if I have to use SQL to do it) that chooses its table based on a variable.

So:

I have 12 tables named F01,F02, F03 etc



and want to preferably have a configuration page (Form Possibly?!) that has a combo box with all the table names in, from which you can set which table the query should use.

How do I also set it so that the variable selected is stored for future use until updated again via the form?

So I expect the query code will look something like (Keeping it simple)

SELECT DISTINCT VARIABLEHERE.ProjectNumber, Max(VARIABLEHERE.[Accounting Date]) AS [MaxOfAccounting Date] 
FROM VARIABLEHERE
GROUP BY VARIABLEHERE.ProjectNumber;

Thanks

[rpeare]

if the structure of all of your tables is the same why is the data in that many different tables? Can you not combine all the data and simply add an additional field saying with an indication of the source of the data? This would make what you're trying to do you would simply change the criteria of a query rather than having to build an entirely new query every time.

[Bedsingar]

Could do, but as the source data comes from 12 sepperate tables I wanted to keep the data sepperate.

Instead I have created a union query & linked a form to that, so workarround achieved.

Thanks

Josh

[rpeare]

I thought your original question was to build a SQL statement from scratch based on some selected criteria. This would be far easier, assuming all your tables have the same structure, if they were in one table. So you created a union query to include all data then built your SQL statement on that?

This solution certainly works and is in the spirit of what I suggested but as your database grows I think you'll find that union queries can be extremely inefficient and you should consider moving all the data to one table, again assuming the table structure of all of them is the same or very nearly the same.