Query Duplicate Records (1 Viewer)

Steve G

Registered User.
Local time
Tomorrow, 01:41
Joined
Aug 16, 2019
Messages
24
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:41
Joined
May 7, 2009
Messages
19,169
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")
 
Last edited:

Steve G

Registered User.
Local time
Tomorrow, 01:41
Joined
Aug 16, 2019
Messages
24
Hi Arnelgp, thanks for the quick response. What does 1 & 3 and 2 mean? Sorry for my ignorance?
 

isladogs

MVP / VIP
Local time
Today, 14:41
Joined
Jan 14, 2017
Messages
18,186
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.
 
Last edited:

Steve G

Registered User.
Local time
Tomorrow, 01:41
Joined
Aug 16, 2019
Messages
24
Hi Isladogs, I've been filting the same results as Arnel...I'll give your suggestion a crack....thanks mate!
 

Steve G

Registered User.
Local time
Tomorrow, 01:41
Joined
Aug 16, 2019
Messages
24
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 :D
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:41
Joined
May 7, 2009
Messages
19,169
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
 

isladogs

MVP / VIP
Local time
Today, 14:41
Joined
Jan 14, 2017
Messages
18,186
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;
 

Steve G

Registered User.
Local time
Tomorrow, 01:41
Joined
Aug 16, 2019
Messages
24
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....:D
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:41
Joined
May 7, 2009
Messages
19,169
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.
 

isladogs

MVP / VIP
Local time
Today, 14:41
Joined
Jan 14, 2017
Messages
18,186
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?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Feb 19, 2002
Messages
42,970
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.
 

Steve G

Registered User.
Local time
Tomorrow, 01:41
Joined
Aug 16, 2019
Messages
24
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.netdna-ssl.com/forums/images/icons/icon14.gif

Kind regards,
Steve
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom