Report is duplicating

[jeridbohmann]

I have a strange request here. We are trying to lockdown internet usage at my
work and they asked me to take the logs 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!

 

[t t via AccessMonster.com]

Hello,
try to make group by username as the first and visited web pages as the
second. After groupping visited web pages you can create third field on
your report to count visiting times.

at print preview of your report you will get it like

username visited web page(s) total visit
-------- ------------------ -----------
user1 www.google.com 1
www.accessmonster.com 2
user2 www.google.com 5 etc.

 

[Jerid Bohmann via AccessMonster.com]

MS won't let me reply to your post. I am kind of confused.
I have 2 Tables:
Table 1 has IP Address and WebSite. That's all imported from a log (50
users, so it's a huge log).
Table 2 has Username and IP Address. I have a list of users with their
IP's. I then connect the 2 IP's as a relationship.
Now on the report I get:
Joe Smith - www.google.com
Joe Smith - www.google.com
Joe Smith - www.msn.com
Alan Johnson - www.msn.com
Alan Johnson - www.msn.com
etc... However there's 50 names and we have some serious "web broswers"
here. Now there is thousands and thousands of records. I have to import the
new data daily it's so large.

Anyway...was giving you more info so you know what I have to do daily. How
would you approach this? I am confused by your answer. I am far from a
novice Access user. I took a 3 day beginner class and the rest self taught
so you could call me Access Helpless.
Thanks! Jerid

 

[George Nicholson]

As I understand it: you already have a query that outputs UserName, UserIP,
Website. Sorted/grouped by User (at least).

Set the "Unique Values" property of the query to Yes. (In query design view:
View>Properties) This changes the SQL of your query from SELECT to SELECT
DISTINCT. Result: for any given User, any given website should only be
listed once in the query output.

If you now want to show how many times that website was hit by that User
then add a "Hits" column to the query and do a count on Website.

HTH,

 

[jeridbohmann]

That wad the answer!!! Thank you so much for the info and ease in which you
explained it!!