View Single Post
Old 08-20-2019, 01:46 AM   #9
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 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