Omit duplicate entries from a count by query (1 Viewer)

AAshley · Feb 13, 2018

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 · Feb 13, 2018

Perhaps GROUP by User ?

AAshley · Feb 13, 2018

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

plog · Feb 13, 2018

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 · Feb 13, 2018

plog said:
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 · Feb 14, 2018

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 · Feb 14, 2018

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 · Feb 14, 2018

plog said:
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 · Feb 14, 2018

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

AAshley · Feb 14, 2018

arnelgp said:
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 · Feb 14, 2018

plog said:
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 · Feb 14, 2018

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 · Feb 14, 2018

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 · Feb 15, 2018

plog said:
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 · Feb 15, 2018

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 · Feb 15, 2018

plog said:
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 · Feb 15, 2018

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_ · Feb 15, 2018

@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

Code:

SELECT TBL_SIGNIN.ID AS SIGNIN_ID

you will make it read

Code:

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 · Feb 21, 2018

Mark_ said:
@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

Code:

SELECT TBL_SIGNIN.ID AS SIGNIN_ID

you will make it read

Code:

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!

Omit duplicate entries from a count by query (1 Viewer)

New member

Enthusiastic Amateur

New member

Banishment Pending

New member

Banishment Pending

..forever waiting... waiting for jellybean!

New member

Attachments

Banishment Pending

New member

New member

Banishment Pending

Super Moderator

New member

Attachments

Banishment Pending

New member

Banishment Pending

Longboard on the internet

New member

Similar threads

Users who are viewing this thread