Ok, I hope I can explain this. Quarterly I have to update a complicated report. This report has numerous sub-reports hence about 10 different queries to aggregate everything.
The queries have to count the following items:
1. New cases, open cases, and closed cases by quarter (Q1, Q2, Q3,Q4), as well as count the same things for the year to date (ie if you run the report in Q3, how many total new open closed cases are there. I thought I would be the range of dates that I'm looking for in a separate field in the table.
2. How many cases there were by department and location.
3. How much money was spent.
You get the idea.
My questions is, instead of updating all of the queries, how can I enter the dates in a table and point the queries to the dates in that one table.
I tried a test query using: [Tables]![tblAnnualDateRange].[Q1]
For that field the data I entered in the table: Between #1/1/2012# and #3/31/2012 (the query needs a date field, however, I used a text field to see if i could get the quarter range). The query didn't work and asked for the Q1 parameter.
Also, some of the calculations in the report should only be based of of the previous quarter. So if someone runs the report in Q3, only Q1 and Q2 data should be displayed. To get around this, I thought about leaving Q3 blank for example, until Q4.
Hopefully I've explained this enough for someone to help get me started.
I might be better off doing some sort of date calculations in the queries, however, I thought I might start off using dates in a table.
John F.
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar