Report That Shows Number of Occurrences In A Table

[Albreezy]

Hello all! This is my first post, and I hope I'm not asking something that has been covered before. I believe this will be a unique situation. After searching, it looks like people only have issues with deleting and/or finding duplicates.

I have a table from excel with multiple columns, and it actually has a new column added each day. For each day (column) user names appear for each user that has logged on. I would like to find a way to create a report that only shows which users names have appeared a certain number of times (such as 4). Ex: Using the table below, 2 different users (Chris and Jennifer) logged on at least 4 times. I need to make a report that lists which users logged on 4 or more times, and the count of those occurrences as well.

The reason we need to do this, is because we are having problems with users continuing to log on in a manner we don't want them logging in. After they have continued to log in after so many times, we are going to disable their access completely.

Thanks for your help! Let me know if you need any further explanation.

Day 1	Day 2	Day 3	Day 4	Day 5
Chris	Chris	Chris	Chris	Chris
John	Robert	Robert	Robert	Scott
Amanda	Jennifer	Jennifer	Larry	Kate
Jennifer	Larry	Mary	John	Jennifer
Kevin		Rich	Mary

<tbody>
</tbody>

 

[Joe4]

Welcome to the Board!

If you can convert this data to a normalized table in Access, it would be very easy (working with denormalized data like you have makes things much harder than they need to be).

In a normalized table, you would only have one table with two fields, in this example.

Day      Name
1        Chris
1        John
1        Amanda
1        Jennifer
1        Kevin
2        Chris
...

Then it would just be a simple Aggregate (Totals) Query grouping on Name and counting the Day field.

 

[Albreezy]

I should have mentioned, each day could be around 800 user names. Also, I'll be looking for occurrences of at least 20x. Would the normalized table still be appropriate?

 

[Joe4]

YES! The larger your data records and fields gets, the more important (critical) it becomes to have normalized data!

 

[Joe4]

Note, if all the data appears on one page in Excel, and you have a list of all the different possible names, you could do this pretty easily in Excel using a COUNTIF formula over your whole data range.

 

[Albreezy]

I'll give this a shot, thanks!

 

[Albreezy]

Something to add...the user names are not predetermined. There will be new user names each day that I will not have prior knowledge of.

 

[Joe4]

Sounds like you then really want to work with a normalized table then.