Unanswered: Can A Query Be Created That Allows the User to Select the Table?
I have a datbase with a master equipment list and a table for expenditures for each year from 1999 to present. I need a query that will allow the user to search by equipment serial number and allow the user to select which table (year) will be searched.
This forum has been a fantastic help to me from day 1.
Thanks. I'll see how this will work. A couple of questions for clarification.
1. When you say delete, do you mean as part of the SQL statement or separately? Why delete it if it will be used multiple times?
2. When this is done, what type of "response" will I get? Will I get the window to fill in the data for the Table?
3. Can I use one query to run this with the table as the variable (since the tables have the same structure) or do I need a separate query for each selection the customer may make?
Thanks a lot for the help. I'll learn this stuff if it kills me!! Thanks for the great support and quick response.
If you dont want to delete the query, then why are you trying to change the source (table) at run-time? Why not juse create all the queries ahead of time and select which one you want to run based on the users action?
This code is used for creating a dynamic query. You would want to delete the query later unless you dont care that everytime it is run there is a new query in the dB.
That is what I thought about doing. Creating a query for each year and then allow the user to select but I didn't know how to make it selectable for the user. I'm not familar with Macro writing and don't know how to do it. I can create the separate queries easy enough, I just need to know how to have the user select which year to run.
Also, is there a way to run a query that will span multiple tables. Say if the user wanted to look at the charges from 1999 to date for a particular serial number.
Thanks again for all of the help. I appreciate you patience.
Sorry for not looking sooner. Had some personal stuff going on.
Still looking at finding a way for a query to span multiple tables. I have billing information in a separate table by year (1999 to present year) and want to run a query that can look up equipment serial numbers in one, two or all of the years and total the results.
I'm not sure if I'm explaining this right and I hope someone can help.
Example of what I want to do:
Serial Number = 12345 call totals from 2001, 2002, 2003, 2004 (each year a different table) add totals for each year and total of all years.