Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Queries (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=8)
-   -   Query Duplicate Records (https://www.access-programmers.co.uk/forums/showthread.php?t=306428)

Steve G 08-19-2019 11:24 PM

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

arnelgp 08-19-2019 11:57 PM

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")


Steve G 08-20-2019 12:08 AM

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

isladogs 08-20-2019 12:20 AM

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.

Steve G 08-20-2019 01:04 AM

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 08-20-2019 01:26 AM

Re: Query Duplicate Records
 
Quote:

Originally Posted by isladogs (Post 1636168)
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

isladogs 08-20-2019 01:29 AM

Re: Query Duplicate Records
 
You're welcome!

arnelgp 08-20-2019 01:36 AM

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

isladogs 08-20-2019 01:46 AM

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;


Steve G 08-20-2019 01:49 AM

Re: Query Duplicate Records
 
Quote:

Originally Posted by arnelgp (Post 1636182)
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 08-20-2019 02:01 AM

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.

isladogs 08-20-2019 05:10 AM

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?

Pat Hartman 08-20-2019 08:00 AM

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.

Steve G 08-20-2019 01:49 PM

Re: Query Duplicate Records
 
Quote:

Originally Posted by isladogs (Post 1636223)
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

Pat Hartman 08-20-2019 08:21 PM

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.


All times are GMT -8. The time now is 06:59 PM.

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