Record Count On each record In Report

[TJBernard]

I have searched and not found a way to do this.

What the users want is basically:

1. Mr. Adams
2. Mr. Bradley
3. Mrs. Johnson
4. Mrs. Smith

I am not sure how to the the number on the left to appear on an MS Access report. I will keep searching and trying different things, if I come up with anything I will let you know.

If anyone has any ideas, I appreciate the help.

 

[gromit]

Hi -

You could create a query that includes all the existing field for the report, PLUS an arbitary rank for whatever field you are ordering the records by (E.g. last name).

To create a query with a ranking you need to create an alias for the table (this sounds worse than it is). As a simple example, the following SQL will rank the values in tblData, according to Field1

SELECT AliasTable.*, 
(Select Count(*) from tblData Where tblData.Field1 <=  AliasTable.Field1;) AS Rank
FROM tblItems AS AliasTable
ORDER BY Field1;

NOTE: This approach is probably only suited for times where you don't have a huge table. The alias basically compares the entire table against itself, so the amount of time required grows quite fast.

If your VBA is up to it, an alternate approach would be to create a temporary table and you can just write an index number as you go.

hth,

- g

 

[Rich]

add an unbound textbox to the report, set its control source to =1, set its running sum property to overall

 

[TJBernard]

add an unbound textbox to the report, set its control source to =1, set its running sum property to overall

Thank you very much, this worked perfectly.

How very convenient.