G
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?
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?