Weekly report and multiple column counts

[Linda]

I need to generate a weekly of every Friday and give counts for
multiple fields. So it will look like:


Week # of Open # of Closed # Pending
7/7/07 9 2 5
7/13/07 17 0 1
7/20/07 12 3 6
7/27/07 20 0 1


How would I set this up? Originally I was going to have a temporary
table with all the values and have the report's recordsource point to
the temp table but what if multiple users run the report? Wouldn't
the data get messed up?

 

[KARL DEWEY]

Post your table and field names.

 

[Linda]

same as shown above:

week
nbropen
nbrclosed
nbrpending

You see the values for Week needs to be generated dynamically
depending on the date the report is run. If run Dec 10, it should
work backwards and capture all fridays till Start Date (this value is
another table). I was going to create field counts per query but then
I would need counts for each 'Week' period in the report. And didnt
see any other way to do it other than update temp tables as vba runs
the queries and then open report with temp table values.

 

[KARL DEWEY]

We are not understanding the other. I asked for your table and field names
that contains the data that the query is to pull records from. What you
post was the same as what you say your output must look like.

Also post sample data from that table.

 

[Baz]

This looks like a job for the crosstab query wizard.

 

[Linda]

Yes, I originally thought a crosstab query.

I basically have different tables with various data and they all have
date fields. For example, there are 3 existing queries already which
gets the counts for which I am trying to do a date range limitation
for counts. The existing 3 queries run pretty fast for data in all
date range.

Problem: # Open, # Closed, and # Pending already exists in a query
for all data in a table. If I start adding in criterias in the WHERE
clause ... and say added dateOpen <= 12/31/2007 for a cumulative
count, and dateClosed <= 12/31/2007 in the same query, the counts are
the same for # Open and # Closed.


My workaround: I created a temp table to hold counts and used vba to
run SQL select statments to get the various counts and run update sql
statements to the temp table. This works but takes 5 minutes to run.

I am trying to describe what my problem without being too complex and
looking for an efficient solution.