Report items counter

[jeridbohmann]

Below is a post I put on here previously. It was answered by using a
Select.Distinct and it worked great. The 2 people also mention that I could
add a counter on the report. So if Joe Smith went to google 20 times a day it
would say:
Joe Smith | www.google.com | 20
At the time it was needed, but now it is. Hoping for the same kind of help I
got before. Thanks!!

POST=
I have a strange request here. We are trying to lockdown internet usage at my
work and they asked me to take the logs (text files_ and import them into
Access.
Done without a problem.
However I have a series like this in the report:
Username www.google.com
Username www.msn.com
Username www.google.com

Now this user might have gone to google 2 times that day, but I want the
report to only show it once. It is in the tables as 2 times, but the report I
am trying to shrink with getting rid of duplicates. Not sure what to do here.
We have people who go to google (or whatever, such as their home page) 20
plus times a day. So the report is getting too long.

Thanks!

 

[Marshall Barton]

Below is a post I put on here previously. It was answered by using a
Select.Distinct and it worked great. The 2 people also mention that I could
add a counter on the report. So if Joe Smith went to google 20 times a day it
would say:
Joe Smith | www.google.com | 20
At the time it was needed, but now it is. Hoping for the same kind of help I
got before. Thanks!!

POST=
I have a strange request here. We are trying to lockdown internet usage at my
work and they asked me to take the logs (text files_ and import them into
Access.
Done without a problem.
However I have a series like this in the report:
Username www.google.com
Username www.msn.com
Username www.google.com

Now this user might have gone to google 2 times that day, but I want the
report to only show it once. It is in the tables as 2 times, but the report I
am trying to shrink with getting rid of duplicates. Not sure what to do here.
We have people who go to google (or whatever, such as their home page) 20
plus times a day. So the report is getting too long.

I think you want to get rid of the Distinct in the query and
use Group By instead:

SELECT username, site, Count(*) As CountOfSite
FROM thetable
GROUP BY username, site

 

[jeridbohmann]

I still used select distinct, but added the Count. Thank you very much. Never
knew it was there! I noticed there is a sum and other choices in the select
query. Still just a newbie. Thanks for your help!
Here's the statement being used:
SELECT DISTINCT [IPAdress Name].User, ProxyLog.Date, ProxyLog.Web,
ProxyLog.IPAdress, Count(ProxyLog.Web) AS CountOfWeb
FROM [IPAdress Name] INNER JOIN ProxyLog ON [IPAdress Name].IPAdress =
ProxyLog.IPAdress
GROUP BY [IPAdress Name].User, ProxyLog.Date, ProxyLog.Web, ProxyLog.IPAdress
ORDER BY [IPAdress Name].User, ProxyLog.Date;

 

[Marshall Barton]

The Group By clause guarantees that you'll have no duplicate
records, so using DISTINCT is redundant and just slows the
query while it checks for nonexistent duplicate records.
--
Marsh
MVP [MS Access]

I still used select distinct, but added the Count. Thank you very much. Never
knew it was there! I noticed there is a sum and other choices in the select
query. Still just a newbie. Thanks for your help!
Here's the statement being used:
SELECT DISTINCT [IPAdress Name].User, ProxyLog.Date, ProxyLog.Web,
ProxyLog.IPAdress, Count(ProxyLog.Web) AS CountOfWeb
FROM [IPAdress Name] INNER JOIN ProxyLog ON [IPAdress Name].IPAdress =
ProxyLog.IPAdress
GROUP BY [IPAdress Name].User, ProxyLog.Date, ProxyLog.Web, ProxyLog.IPAdress
ORDER BY [IPAdress Name].User, ProxyLog.Date;

I think you want to get rid of the Distinct in the query and
use Group By instead:

SELECT username, site, Count(*) As CountOfSite
FROM thetable
GROUP BY username, site