Counting Records Delima

[Guest]

I am trying to wrap my head around a record couting delima.

I need to count the number of records in my table for "Total", "Open", and
"Closed" using a "Reporting Period" which ends each Quarter.

The user submits their report at the end of each Quarter and I use that
action to set the value for the "Reporting Period" in each record within
their "Office". I have written a Query to count the "Open", "Closed", and
Total; however, my client wishes the numbers to reflect a running count to
date.

Example:
Reporting Period Total Closed Open
1st QTR FY07 23 6 17
2nd QTR FY07 17 11 12
3rd QTR FY07 12 8 4
4th QTR FY07 4 4 0

I tried creating a new table to store the counts at the end of each
"Reporting Period", but an Update query won't work, since it isn't updating
values in the master data table.

I tried a Make table Query, but that replaces the entire table with new data.

An append query seems to be the logical choice, but I haven't been able to
write 1 query which will, a) perform all three counts, b) accomodate a user
making multiple submissions for the same "Office" for the same "Reporting
Period" (users do tend to make mistakes).

Any suggestions?

 

[Guest]

What is the structure of your table(s)?

I presume you have a date field on each record?

You shouldn't really store these values. Your tables should be setup with
fields that allow you to use a query to return the totals you want.

Steve

 

[Pieter Wijnen]

A little boolean trick might help you (I Assume Date field To mark as Open
or Closed)

SELECT DatePart("q", A.Period) , Count(*) As Total, -Sum(IsNull(A.Closed))
As Open, -Sum(Not IsNull(A.Closed)) As Closed
FROM MyTable A
Group By DatePart("q", A.Period)

HTH

Pieter

 

[Guest]

I have a date field for the date the record was entered, but I do not see how
that would apply.

I can COUNT the records with a query. I need to count the records at the
time the Summary Report is ran will tell me how many records were remaining
open from last time, how many where closed this time, and how many are still
open.

Since the status "Open" or "Closed" on record will be reported multiple
times. Each Quarter the record will be counted as either "Open" or "Closed".
I will decriment the number by one from the "Open" in the quarter in which
it it closed. I will also reduce the number of "TOTAL" for the next
"Reporting Period" by one.

 

[Guest]

The "Open" date would be the date the record was entered. I do not store a
date for when the record is closed. I could, but wouldn't it be in a
separate date field?

 

[Pieter Wijnen]

If At all possible I'd change Opened & Closed to be DateFields
That Way you can use simple arethmetics to achieve your goal w/o having to
rely on
stored snapshots of your data.
You would typically set the Opened Date to the creation date (Before Insert)
& could use an unbound tickbox for the users to tick (as they do today)
that sets the closed date to =Date()
The Query will offcourse be somewhat more complicated than my previous
sample though...

HTH

Pieter

PS If you decide to stick to your approach...
It will be far simpler to achieve using code
& you'll need more than one Query
& running it twice in a Quarter will play havoc with your stored Snapshot