Omit duplicate entries from a count by query

[AAshley]

Hi everyone,

I used to be a member of this forum a few years ago and haven't had to create any databases in quite some time. I can't remember my old login so I created a new one. In any event I'm hoping I can get some help with a problem I'm having with a new database I'm working on.

I have a report that uses a query to count the number of sign-in's we get from students at our tech center. On the main report the information is supposed break down the demographics of the students by gender, languages spoken, etc. The sub-report evaluates the number of sign-in's received by the day of the week and hour of the day. The two parts of the report run off of different queries that come from the same table.

The table logs a username, an id, a sign-in date, sign-in time, and a unique id assigned to that sign-in. The query is supposed to filter this data by date. The problem I'm having with the query for the main report is that it's counting the total number of sign-ins. For the life of me I cannot figure out how to get it to only count a user once, no matter how many times they've signed in. Can someone help me figure out how to eliminate the duplicate sign-ins and only get it to show me 1 sign-in from a user during those given dates? Thanks in advance everyone!

 

[Gasman]

Perhaps GROUP by User ?

 

[AAshley]

I've tried that but it continues to give a full count of all sign-in's. :banghead:

 

[plog]

Can you post sample data to demonstrate what you hope to achieve? Post 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what you hope to end up with from your query when you feed it the data from A.

 

[AAshley]

Can you post sample data to demonstrate what you hope to achieve? Post 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what you hope to end up with from your query when you feed it the data from A.

Okay sure.
So the table (Tbl_SignIn) has the following fields:
SignInDate, SignInTime, ID

The query (Qry_SignIn) then includes those above and a few more, making the complete list of fields:
SignInDate, SignInTime, ID, Start Date, End Date, Gender, Race, Lang1, Lang2, Age

What I'd like is to be able to show is only one sign-in from the ID field during the specified date range. So if a user with ID# 32 has signed in 12 times during a 2 week period I only want to show their information counted 1 time as opposed to 12 times.

 

[plog]

Nope, show me with data, not an explanation.

Put both sets of data (A&B) in a spreadsheet or post it in the forum like so:

tableNameHere
Field1Name, Field2Name, Field3Name, ...
Dave, 12/13/2017, 14
Sally, 1/13/2018, 92
Tim, 12/18/2017, 4

 

[arnelgp]

create an Unbound Textbox on the Main Report.
populate the textbox on the Reports Pageheader Format event:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
dim counter as long
counter = nz(dcount("*", "queryname", "id=" & me.id)
Me.UnboundTextbox = IIF(counter<>0,1, 0)
End Sub

 

[AAshley]

Nope, show me with data, not an explanation.

Put both sets of data (A&B) in a spreadsheet or post it in the forum like so:

tableNameHere
Field1Name, Field2Name, Field3Name, ...
Dave, 12/13/2017, 14
Sally, 1/13/2018, 92
Tim, 12/18/2017, 4

My apologies...

 

[plog]

I need 2 datasets, A and B. Which one is that? And where is the other?

 

[AAshley]

create an Unbound Textbox on the Main Report.
populate the textbox on the Reports Pageheader Format event:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
dim counter as long
counter = nz(dcount("*", "queryname", "id=" & me.id)
Me.UnboundTextbox = IIF(counter<>0,1, 0)
End Sub

No change using this method either. Still counts all of the entries.

I think it's not considering the records duplicates because each name has a unique date and time attached to them.

 

[AAshley]

I need 2 datasets, A and B. Which one is that? And where is the other?

The first data set are the SignInDate and Time fields, coming from a table names "Tbl_SignIn".

The second data set are the ID Numbers and Names, with all of the other data
fields attached, coming from a table called "Tbl_Main".

They share a common field, ID, which links the two tables together in the query.

 

[plog]

You provided a spreadsheet with 1 tab of data. I need 2 sets of data to afind out what you want. Here's my requirements again:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what you hope to end up with from your query when you feed it the data from A.

 

[Pat Hartman]

Before you can get a count of people, you need to get a list of people rather than a list of logins. I do this with a separate query. You can also do it with a subselect.

Select UserName, Count(*) As Signins
From YourTable Where SignInDate Between {StartDate] and [EndDate}
Group by UserName;

Then you can count this query to get the number of distinct logins and you can Sum the Signins field to get a total of tims anyone logged in.

T-SQL has an option that lets you do this directly but Access SQL does not

 

[AAshley]

You provided a spreadsheet with 1 tab of data. I need 2 sets of data to afind out what you want. Here's my requirements again:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what you hope to end up with from your query when you feed it the data from A.

Here is a spreadsheet with the edited data.

Essentially I just need their first, or any, sign-in recorded between the start and end date.

 

[plog]

First, your one tab is called 'Current Results'. I don't care about any attempt you have made thus far--I want some sample data from your table(s), then what you expect based on it.

Also, from what I see you have stored your data improperly. Date/time fields can hold date and time values, you shouldn't have seperate fields for the sign in date and the sign in time.

Lastly, when you want to pull a record from a bunch of records that share a lot of the same data, you need a way to uniquely identify a record. The data you have provided doesn't have that. You need some sort of autonumber primary key ID field in your data to do so.
That should be in the source data you provide.

 

[AAshley]

First, your one tab is called 'Current Results'. I don't care about any attempt you have made thus far--I want some sample data from your table(s), then what you expect based on it.

Also, from what I see you have stored your data improperly. Date/time fields can hold date and time values, you shouldn't have seperate fields for the sign in date and the sign in time.

Lastly, when you want to pull a record from a bunch of records that share a lot of the same data, you need a way to uniquely identify a record. The data you have provided doesn't have that. You need some sort of autonumber primary key ID field in your data to do so.
That should be in the source data you provide.

This is a brand new database. The data you see has been created to force entries and verify if it's generating the information necessary. What you see is what I have. The ID number is an AutoNumber generated by the name of the person. There are 2 people in the entire database at this time. There are separate fields for the date and time because as a single field I am not able to extract the information I need for the report based on which days of the week and which hours we have the most traffic. As a single field I am not able to separate the time data, so I had to go back and create a separate field.

 

[plog]

If you don't know how to extract just the date/time part you need, you could post a question on this forum or use the functions on this page:

https://www.techonthenet.com/access/functions/

In the data you provided, there was no autonumber. Can you post your database?

 

[Mark_]

@OP,

Do you have a query that drive your reporting?

If yes, then open the definition for your query in SQL view and type the word DISTINCT after the SELECT statement. So where it currently reads

SELECT TBL_SIGNIN.ID AS SIGNIN_ID

you will make it read

SELECT DISTINCT TBL_SIGNIN.ID AS SIGNIN_ID

This should only return unique results.

Also remember to REMOVE the following from your query;
SignInDate
SignInTime
Start Date
End Date

These values are not only not relevant (you want one record per ID, not one per ID per visit) but will only confuse your query.

The ONLY duplicates you should see would be if someone decided to change other values.

Let me know if this helps!

 

[AAshley]

@OP,

Do you have a query that drive your reporting?

If yes, then open the definition for your query in SQL view and type the word DISTINCT after the SELECT statement. So where it currently reads

SELECT TBL_SIGNIN.ID AS SIGNIN_ID

you will make it read

SELECT DISTINCT TBL_SIGNIN.ID AS SIGNIN_ID

This should only return unique results.

Also remember to REMOVE the following from your query;
SignInDate
SignInTime
Start Date
End Date

These values are not only not relevant (you want one record per ID, not one per ID per visit) but will only confuse your query.

The ONLY duplicates you should see would be if someone decided to change other values.

Let me know if this helps!

Thanks!

I went back in and made a few changes to the query and I was able to get it to populate everything as I wanted it. Thanks a mil!