I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
Counting of Cases and counting no records
- Thread starter danassef
- Start date
- Local time
- Today, 00:08
- Joined
- Feb 28, 2001
- Messages
- 30,578
I found your description a little confusing. Be that as it may, I think I have at least a glimmer of what you want.
When you get back an empty report, it is always because you have somehow overconstrained your query. I.e. your filters are TOO efficient - they got rid of everything. So the FIRST think to look for is how you are filtering out queries.
To get a bunch of records for specific date ranges - whether we are talking last week, last month, last year, or last century (and in 2006, we can say that....), you need to know two things that will be your friends.
DateAdd (or DateSub) lets you compute a date on-the-fly that is some number of date units earlier or later than the current date. You need to use Now() for the current date (or Date() might be better).
To imply a date range, you need to use the BETWEEN ... AND ... operator as a means to select appropriate records in your WHERE clauses.
Therefore, I strongly urge you to read up on these topics: DateAdd (or DateSub) and the BETWEEN ... AND ... operator as a way to create the selected recordsets.
Now if you want these reported separately, you are looking at reports containing sub-reports, perhaps. Which implies different date ranges and therefore different recordsets and therefore distinct queries, I think.
If you want to keep the last week, last year, etc. segments distinct from each other, you have no choice but to run separate queries and later merge them in any of several ways. In terms of the Divide-and-Conquer approach, I would do this as several sub-reports in a master report. But there are other ways to skin this particular cat.
When you get back an empty report, it is always because you have somehow overconstrained your query. I.e. your filters are TOO efficient - they got rid of everything. So the FIRST think to look for is how you are filtering out queries.
To get a bunch of records for specific date ranges - whether we are talking last week, last month, last year, or last century (and in 2006, we can say that....), you need to know two things that will be your friends.
DateAdd (or DateSub) lets you compute a date on-the-fly that is some number of date units earlier or later than the current date. You need to use Now() for the current date (or Date() might be better).
To imply a date range, you need to use the BETWEEN ... AND ... operator as a means to select appropriate records in your WHERE clauses.
Therefore, I strongly urge you to read up on these topics: DateAdd (or DateSub) and the BETWEEN ... AND ... operator as a way to create the selected recordsets.
Now if you want these reported separately, you are looking at reports containing sub-reports, perhaps. Which implies different date ranges and therefore different recordsets and therefore distinct queries, I think.
If you want to keep the last week, last year, etc. segments distinct from each other, you have no choice but to run separate queries and later merge them in any of several ways. In terms of the Divide-and-Conquer approach, I would do this as several sub-reports in a master report. But there are other ways to skin this particular cat.
Similar threads
