Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-19-2019, 11:24 PM   #1
Steve G
Newly Registered User
 
Join Date: Aug 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
Steve G is on a distinguished road
Query Duplicate Records

Hi guys, I've hit a bit of a "Pickle", so I'm hoping some boffins might be able to assist me? I am trying to use a query to filter my ParticipantTable to find relevant information. In my ParticipantTable I have the following fields of interest:

1. ID (Primary Key)
2. UniqueParticipantNumber (provides flexibility to track duplicate participants)
3. ParticipantFirstName
4. Transferred ("In" or "Out" combo box)

Participants are essentially tenants who can stay in a property for indefinite periods of transfer "In" and "Out" of properties frequently. The data I am trying to extract are participants who have either Transferred "Out" (not "In") or have NOT Transferred "In" (not become a tenant). I believe what I need to do (which I don't know how to) is:

- Search participants who do NOT have duplicate UniqueParticipantNumbers
- Have Transferred "Out" (but not transferred "In" another property)
- Have not yet become tenants

Looking forward to your help....thanks guys....

Kind regards, Steve

Steve G is offline   Reply With Quote
Old 08-19-2019, 11:57 PM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,900
Thanks: 63
Thanked 2,510 Times in 2,410 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query Duplicate Records

use Union Query:
Code:
1 & 3:
SELECT participantTable.UniqueParticipantNumber, participantTable.ParticipantFirstName
FROM participantTable
WHERE ((((SELECT Count("1") From participantTable As T1 Where T1.UniqueParticipantNumber=participantTable.UniqueParticipantNumber))=1));

2:
SELECT DISTINCT participantTable.UniqueParticipantNumber, participantTable.ParticipantFirstName
FROM participantTable
WHERE (SELECT Count("1") From participantTable As T1 Where T1.UniqueParticipantNumber=participantTable.UniqueParticipantNumber AND T1.Transferred="OUT") >= 
(SELECT Count("1") From participantTable As T1 Where T1.UniqueParticipantNumber=participantTable.UniqueParticipantNumber AND T1.Transferred="IN")
__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 08-20-2019 at 01:52 AM.
arnelgp is offline   Reply With Quote
Old 08-20-2019, 12:08 AM   #3
Steve G
Newly Registered User
 
Join Date: Aug 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
Steve G is on a distinguished road
Re: Query Duplicate Records

Hi Arnelgp, thanks for the quick response. What does 1 & 3 and 2 mean? Sorry for my ignorance?

Steve G is offline   Reply With Quote
Old 08-20-2019, 12:20 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,056
Thanks: 110
Thanked 2,728 Times in 2,491 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query Duplicate Records

I see Arnel has already given one solution using a union query
As union queries give you the results from each part of the query I'm not clear how that would work.
I just tested it on sample data and it didn't work for me anyway. In fact it gave those with only one record whether In or Out

Here's a different approach adapting the duplicates query wizard to select non-dupes.

Code:
SELECT ParticipantTable.UniqueParticipantNumber, ParticipantTable.ParticipantFirstName, ParticipantTable.Transferred
FROM ParticipantTable
WHERE (((ParticipantTable.UniqueParticipantNumber) In (SELECT [UniqueParticipantNumber] FROM [ParticipantTable] As Tmp GROUP BY [UniqueParticipantNumber] HAVING Count(*)=1 )) AND ((ParticipantTable.Transferred)="Out"))
ORDER BY ParticipantTable.UniqueParticipantNumber;
It lists those with only one record and where it is Out
Using that approach, you don't need to separately filter for those not In.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 08-20-2019 at 12:26 AM.
isladogs is offline   Reply With Quote
Old 08-20-2019, 01:04 AM   #5
Steve G
Newly Registered User
 
Join Date: Aug 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
Steve G is on a distinguished road
Re: Query Duplicate Records

Hi Isladogs, I've been filting the same results as Arnel...I'll give your suggestion a crack....thanks mate!
Steve G is offline   Reply With Quote
Old 08-20-2019, 01:26 AM   #6
Steve G
Newly Registered User
 
Join Date: Aug 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
Steve G is on a distinguished road
Thumbs up Re: Query Duplicate Records

Quote:
Originally Posted by isladogs View Post
I see Arnel has already given one solution using a union query
As union queries give you the results from each part of the query I'm not clear how that would work.
I just tested it on sample data and it didn't work for me anyway. In fact it gave those with only one record whether In or Out

Here's a different approach adapting the duplicates query wizard to select non-dupes.

Code:
SELECT ParticipantTable.UniqueParticipantNumber, ParticipantTable.ParticipantFirstName, ParticipantTable.Transferred
FROM ParticipantTable
WHERE (((ParticipantTable.UniqueParticipantNumber) In (SELECT [UniqueParticipantNumber] FROM [ParticipantTable] As Tmp GROUP BY [UniqueParticipantNumber] HAVING Count(*)=1 )) AND ((ParticipantTable.Transferred)="Out"))
ORDER BY ParticipantTable.UniqueParticipantNumber;
It lists those with only one record and where it is Out
Using that approach, you don't need to separately filter for those not In.
Absolute legend.....Thank you very much.....top of the pop charts for you isladogs
Steve G is offline   Reply With Quote
Old 08-20-2019, 01:29 AM   #7
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,056
Thanks: 110
Thanked 2,728 Times in 2,491 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query Duplicate Records

You're welcome!

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-20-2019, 01:36 AM   #8
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,900
Thanks: 63
Thanked 2,510 Times in 2,410 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query Duplicate Records

are you sure? there are three conditition.
look at your logic?
it only return where the tenant has out 1.

you are also looking for a tenat who got out but has not got in again:

tenant name in out
-----------------------------------------
tenant a 1 1

what about those who has not yet been a tenant?

tenant name in out
-----------------------------------------------------
tenant b 0 0
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Steve G (08-20-2019)
Old 08-20-2019, 01:46 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,056
Thanks: 110
Thanked 2,728 Times in 2,491 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query Duplicate Records

Quote:
what about those who has not yet been a tenant?
Good point.
I had assumed they wouldn't have a record.
If not yet a tenant, they are neither In nor Out (could be doing the hokey cokey ) i.e. Null
Modified query which works for me

Code:
SELECT ParticipantTable.UniqueParticipantNumber, ParticipantTable.ParticipantFirstName, ParticipantTable.Transferred
FROM ParticipantTable
WHERE (((ParticipantTable.UniqueParticipantNumber) In (SELECT [UniqueParticipantNumber] FROM [ParticipantTable] As Tmp GROUP BY [UniqueParticipantNumber] HAVING Count(*)=1 )) AND ((ParticipantTable.Transferred)="Out" Or (ParticipantTable.Transferred) Is Null))
ORDER BY ParticipantTable.UniqueParticipantNumber;
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-20-2019, 01:49 AM   #10
Steve G
Newly Registered User
 
Join Date: Aug 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
Steve G is on a distinguished road
Re: Query Duplicate Records

Quote:
Originally Posted by arnelgp View Post
are you sure? there are three conditition.
look at your logic?
it only return where the tenant has out 1.

you are also looking for a tenat who got out but has not got in again:

tenant name in out
-----------------------------------------
tenant a 1 1

what about those who has not yet been a tenant?

tenant name in out
-----------------------------------------------------
tenant b 0 0
Hi Arnel, my filtering kept producing duplicate participants. I adapted Isladogs suggestion to suit. Thanks for your assistance. I really appreciated your prompt response....
Steve G is offline   Reply With Quote
Old 08-20-2019, 02:01 AM   #11
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,900
Thanks: 63
Thanked 2,510 Times in 2,410 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query Duplicate Records

its your query not mine.
better check for your 3 criteria you made in post #1.
"you have tenants of indefinite "in" / "out". not just 1 in or 1 out.
the query you adopted only count 1 out. supposed there are many in's and out's?
goodluck.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-20-2019, 05:10 AM   #12
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,056
Thanks: 110
Thanked 2,728 Times in 2,491 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Query Duplicate Records

Arnel
As I understand the original conditions, multiple entries have the same unique participant number (UPN) so are duplicates to be excluded.

So a count where UPN=1 and Transferred=out or null satisfies all 3 conditions.

Please can you explain how your UNION query can do that

Steve
Does my modified query work as you wanted?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 08-20-2019, 08:00 AM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,930
Thanks: 13
Thanked 1,527 Times in 1,453 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Query Duplicate Records

Why not redo the schema so that it is a proper many-many relationship? Then all you need is a left join against a query that selects rows from the junction table where OutDate is Null so that only the occupied properties are returned by the right side query.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-20-2019, 01:49 PM   #14
Steve G
Newly Registered User
 
Join Date: Aug 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
Steve G is on a distinguished road
Thumbs up Re: Query Duplicate Records

Quote:
Originally Posted by isladogs View Post
Arnel
As I understand the original conditions, multiple entries have the same unique participant number (UPN) so are duplicates to be excluded.

So a count where UPN=1 and Transferred=out or null satisfies all 3 conditions.

Please can you explain how your UNION query can do that

Steve
Does my modified query work as you wanted?
HI Isladogs, yes it does - so thank you very much. Kudos to you also Arnel, because I inadvertently neglected to mention that the participants who were not yet allocated a property were actually being entered in the ParticipantListTable (feeding into the ParticipantTable). So, in essence, I combined Isladog's solution with a Union Query (which was your suggestion).

Pat, I work for a government agency, so in designing this database, I am creating a "No Wrong Door" process because all work orders (for data entering) would be driven through a workflow pipeline (essentially only one door). I tried your suggestion with many different iif statements but the query kept filtering participants who had transferred "Out" and/or "In". I only wanted to filter participants' UPN's where Transferred = "Out" (not "In") and where UPN's were neither "Out" or "In". By combining Isladog and Arnel's suggestions, I was able to achieve this goal.

Thank you again guys. This forum is an essential resource for MS Access global communities..... https://accessworld-accessworld.netd...ons/icon14.gif

Kind regards,
Steve
Steve G is offline   Reply With Quote
Old 08-20-2019, 08:21 PM   #15
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,930
Thanks: 13
Thanked 1,527 Times in 1,453 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Query Duplicate Records

If you have both In and Out on the same record (which they should be), the query should select only records where the "out" is null meaning that the participant is actively at the location. No IIF() is necessary.

That query needs to be the right side of a left join. It is the left join part that gets you the participants that are not currently "in" a location.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
query 3 columms table

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append query duplicate records hardyd44 Queries 2 04-12-2010 04:34 AM
need help to query without duplicate records help smig Queries 9 04-03-2010 09:09 AM
help in query. duplicate records zibzaba Queries 2 02-16-2010 06:48 AM
Query for duplicate records zaphod2003 Queries 3 07-09-2007 06:57 AM
Eliminate Duplicate Records in a Query jereece Queries 2 06-06-2007 05:32 AM




All times are GMT -8. The time now is 02:44 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World