By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,578 Members | 826 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,578 IT Pros & Developers. It's quick & easy.

Tricky query

P: n/a
Pea
I'm working with a system usage database. I want to filter out
repetitive logins. The query I have retrieves data like this:

USER_DATE USER_TIME1 USER_USERID USER_ACCOUNT
10/01/2004 19:56 y708ga27 The Capital Group
10/01/2004 19:58 y708ga27 The Capital Group
10/01/2004 19:59 y708ga27 The Capital Group
10/01/2004 20:19 y708ga27 The Capital Group

In a case like this - with consecutive logins for the same user, if
the same user logged in more than once within 30 minutes, I want to
only retrieve one (remove duplicates), provided that the USER_ACCOUNT
is the same:

USER_DATE USER_TIME1 USER_USERID USER_ACCOUNT
10/01/2004 19:56 y708ga27 The Capital Group

Is the best way to do this through a query, report or form?

Thank you,
Tara
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Pea wrote:
I'm working with a system usage database. I want to filter out
repetitive logins. The query I have retrieves data like this:

USER_DATE USER_TIME1 USER_USERID USER_ACCOUNT
10/01/2004 19:56 y708ga27 The Capital Group
10/01/2004 19:58 y708ga27 The Capital Group
10/01/2004 19:59 y708ga27 The Capital Group
10/01/2004 20:19 y708ga27 The Capital Group

In a case like this - with consecutive logins for the same user, if
the same user logged in more than once within 30 minutes, I want to
only retrieve one (remove duplicates), provided that the USER_ACCOUNT
is the same:

USER_DATE USER_TIME1 USER_USERID USER_ACCOUNT
10/01/2004 19:56 y708ga27 The Capital Group

Is the best way to do this through a query, report or form?

Thank you,
Tara


Yeah, it's tricky. This might be a possibility.

Maybe create a Totals query that would select all records for the UserID
that groups on UserID and counts all records for that userID where the
Date/Time - 30 minutes has a count of 0.

Then create a query to select all records from the table where the
Totals query count is 0 for that useruid, date, time

This may not cover all bases. You'll have to experiment. In the above
example it would.
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.