Top 10 records of specific field in table

[Chuck]

I have a table that captures when a person logs onto the database. It
stores their windows logon name, the computer terminal, their department,
and the date/time when they logged on and the date/time when they logged
off. Its for audit purposes.

Currently, there are over 190 people that can sign on and off and any
terminial and any time.

The audit report lists by their department each user's access information,
grouped by their windows logon name. Depending on the person, the report is
rather long and spans many pages. The audit review group would like to see
only the last 20 logons for the person.

I tried the TOP function, but that doesn't produce the desired results. The
table size will increase dramatically as more people sign on/off, so maybe
something that would just keep the top 20 logons for each individual.

Any help would be great.

 

[Guest]

This is basically a report question. I would add a text box to the detail
section of the report:
Name: txtCountLogin
Control Source: =1
Running Sum: Over Group
Visible: No
Then add code to the On Format event of the Detail Section of the report:
Cancel = Me.txtCountLogin > 20

 

[chuckr1958]

Thanks Duane, however, the report is grouped by their department, so
it only shows the top 20 of the entire department. I would assume
that I would have to make a group header for the logon name but keep
it under department.

 

[Guest]

That would be the idea (group by the field you want to count/limit).

 

[chuckr1958]

That would be the idea (groupby the field you want to count/limit).
--
Duane Hookom
MicrosoftAccessMVP






- Show quoted text -

Thanks Duane, I did get the report to be correct, however, the table
is now rapidly expanding as users sign on and off all day. Do you
have any ideas on how to delete older records greater than the last 20
logons for each user? I figure that I would have to clean up the
table first through some sort of delete query and then change the VBA
to add a users logon information and delete the oldest if greater than
20. This has to be grouped by user. Any ideas?

 

[Guest]

I'm not sure that I would delete records unless your performance degrades.
You could then use a delete query.