Counting Number of Records in Temp Table (1 Viewer)

[George10988]

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.

 

[Beetle]

For that to work you would have to do a Compact & Repair in between each run of the report, but you're making this way more complicated than it needs to be. Simply create a Select (or Totals) query that returns the records you need, use the query as the record source of the report, and do the numbering in the report using a method like the one here;

http://allenbrowne.com/casu-10.html

 

[QTNM]

Actually, I believe you could just add a new text box to your Report Footer, and in the Control Source of the text box, type: =count([Monthly Population]) ... or any other field for that matter.

Is this where you were looking to put a total records value, at the end of the report?

QT

 

[George10988]

Beetle you were right on the money with that link. Thanks

QTMN- I was just looking for a count that would display the number of rows in my report. thanks though!