Count the amount of times a name appears in a query

[awade]

Good Evening,

I have a log in table that counts every time a user logs onto the database. Each time a user logs in it adds another entry to a table.

I have built a query to display the user name and date of log in so I could display this information in a report. The report is now getting rather long, and I am looking for a way to display each users name and have the total number of times they have logged in, not display each time they have logged in separately.

The Query has two fields "Agent Name" and "Logger Date"

The report displays the data as follows

Agent Name Logger Date
User 1 26/5/13
User 1 26/5/13
User 1 27/5/13
User 2 28/5/13

What I would like to do is have a report listing each users name, with a column showing how many times they have logged on. e.g.

User Name Login Count
User 1 3
User 2 1

 

[billmeye]

In the query for your report record source, group by Agent Name and in the second column, use Count([Agent Name]) and it will count the number of rows for each Agent Name.

 

[awade]

Thanks Bill. My apologies but I am really new to access. Could you please explain exactly where these codes go?
How do I group by agent name, is that in the Field, Table, Sort, Show, Criteria Fields and where do I add that code in the second column.
I'm using Access 2003

 

[pr2-eugin]

Hello Andrew, if your Query is something like..

SELECT agentName, loggerDate FROM tbl_Logger;

Then change it to..

SELECT agentName, Count(agentName) As CountOfTotalLog 
FROM tbl_Logger
GROUP BY agentName;

 

[billmeye]

When you are in design mode for your report, enable the Property sheet if it is not already visible. On the tab for Data you will see the Record Source. This most likely is the name of your table. Instead of using the table, we can make a query that will evaluate the information in the table. I suggest for this, you attach your DB and I will go ahead and update it so you can see better what I am talking about.

 

[awade]

Please find attached the query, tables and report from the database to make it easier to explain / establish what I was after.

Thanks for your help so far.

Cheers

 

[pr2-eugin]

So did you manage to try the change I have advised?

Anyway, I have made the changes..

 

[billmeye]

You already were using a query as your recordsource for your report so I just modified that query to count occurrences by Agent in your tbl_Logger table.

 

[awade]

Paul and Bill,

Thank you very much for putting the time into getting this sorted out for me. I appreciate your assistance.

Cheers

Andrew

 

[billmeye]

Glad to be of help.

 

[ootkhopdi]

Hello Andrew, if your Query is something like..

SELECT agentName, loggerDate FROM tbl_Logger;

Then change it to..

SELECT agentName, Count(agentName) As CountOfTotalLog 
FROM tbl_Logger
GROUP BY agentName;

this code given result for total count of login
can we count login time in column pregressively
this result can taken by use of "DCOUNT" function

ootkhopdi