George10988
Registered User.
- Local time
- Today, 07:50
- Joined
- Apr 23, 2011
- Messages
- 41
Hello everyone,
I am trying to create a report in which it is populated by only the current month's population of juvenile detainee's. I need to have a field in this report that basically numbers each juvenile. EG:
Line| Last Name| Middle Initial| FirstName
1 | Doe | John | John
2 | Doe | Jane | Jane
This is what I've got going so far.
On open of the report an append query appends only the records for the current month to a temporary table named "Monthly Pop" then the report itself is based off this table. (the reports record source IS this table)
On the table itself I have an autonumber field which I thought would provide me with a good counter; however, it only works correctly on the first run. Let me go on with how my system works so I can fully explain why it does not work.
After the user closes the report I have a delete query which deletes all the records from the temporary table that are in the current month.
The problem I am getting is once the report is open again and hence the append query is also run, the auto number does not start at 1. It starts based on the last number of the previous appeneded records. For example if the first run of the append query returned 3 records and then the delete query did its work on exit of the report, the next append query would start at 4 instead of 1. I am not sure what to do.
I tried adding =Count([Monthly Population].[random field to count]) to my reports ID column but it fills in the total number of records instead of actually counting the records.
I am trying to create a report in which it is populated by only the current month's population of juvenile detainee's. I need to have a field in this report that basically numbers each juvenile. EG:
Line| Last Name| Middle Initial| FirstName
1 | Doe | John | John
2 | Doe | Jane | Jane
This is what I've got going so far.
On open of the report an append query appends only the records for the current month to a temporary table named "Monthly Pop" then the report itself is based off this table. (the reports record source IS this table)
On the table itself I have an autonumber field which I thought would provide me with a good counter; however, it only works correctly on the first run. Let me go on with how my system works so I can fully explain why it does not work.
After the user closes the report I have a delete query which deletes all the records from the temporary table that are in the current month.
The problem I am getting is once the report is open again and hence the append query is also run, the auto number does not start at 1. It starts based on the last number of the previous appeneded records. For example if the first run of the append query returned 3 records and then the delete query did its work on exit of the report, the next append query would start at 4 instead of 1. I am not sure what to do.
I tried adding =Count([Monthly Population].[random field to count]) to my reports ID column but it fills in the total number of records instead of actually counting the records.