-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Multiple queries, single input
- Thread starter jaye
- Start date
No problem.
I have a query that takes data from a table between a certain date and time. Say June 2nd and June 3rd. Now, I have separate queries that will separate this data into little sections of time (eg. between 2AM and 5AM). I am running 4 of these smaller queries.
Then, I have subforms that will add up the number of rows in these smaller queries (for efficiency analysis). Therefore, I have a large form that calls on all four subforms for it's data. However, when this large form is opened, it reqires that the date and time be input four separate times (due to the first, original query).
I am wondering if it is possible to have the user input this Date and time only once.
Thanks.
I have a query that takes data from a table between a certain date and time. Say June 2nd and June 3rd. Now, I have separate queries that will separate this data into little sections of time (eg. between 2AM and 5AM). I am running 4 of these smaller queries.
Then, I have subforms that will add up the number of rows in these smaller queries (for efficiency analysis). Therefore, I have a large form that calls on all four subforms for it's data. However, when this large form is opened, it reqires that the date and time be input four separate times (due to the first, original query).
I am wondering if it is possible to have the user input this Date and time only once.
Thanks.
Upvote
0
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 73,474
- Office Version
- 365
- Platform
- Windows
So, let's see if I got this straight:
You have a Table, let's call it Table1.
From that table you have a Query (let's call it Query1) that selects records from Table1 based on Date Criteria (my guess is that it is a Parameter Query, am I right?). Then from that Query1 there are 4 "sub queries" which break the data from Query1 into four different time blocks. Your subforms are then run off of these 4 "sub" queries.
Now when you open the report, it prompts you to enter the date 4 times instead of just once.
Can we confirm:
The date parameter should just be on Query1, not on the 4 subqueries. Since the 4 sub queries call on Query1, whenever one of these queries is run, it asks for the date.
That is a very interesting scenario. I have done stuff like this in the past, but it requires VBA programming to create the SQL code necessary to run the queries. Basically, you create a "Criteria Form" that the user fills out (instead of a parameter query). VBA code then builds the queries behind the scenes. If you are not familiar with VBA code, you may not be comfortable with that solution, though.
Maybe someone else can suggest a non-VBA solution?
You have a Table, let's call it Table1.
From that table you have a Query (let's call it Query1) that selects records from Table1 based on Date Criteria (my guess is that it is a Parameter Query, am I right?). Then from that Query1 there are 4 "sub queries" which break the data from Query1 into four different time blocks. Your subforms are then run off of these 4 "sub" queries.
Now when you open the report, it prompts you to enter the date 4 times instead of just once.
Can we confirm:
The date parameter should just be on Query1, not on the 4 subqueries. Since the 4 sub queries call on Query1, whenever one of these queries is run, it asks for the date.
That is a very interesting scenario. I have done stuff like this in the past, but it requires VBA programming to create the SQL code necessary to run the queries. Basically, you create a "Criteria Form" that the user fills out (instead of a parameter query). VBA code then builds the queries behind the scenes. If you are not familiar with VBA code, you may not be comfortable with that solution, though.
Maybe someone else can suggest a non-VBA solution?
Upvote
0
Similar threads
- Question
- Question
