Multiple records question (1 Viewer)

jlavin

New member
Local time
Today, 01:47
Joined
Jul 17, 2017
Messages
4
I am looking at a table that could have the same person on it more than once. A visit table. I have a unique ID and DOB on this table.

We have had some data entry issues and I would like to look at this moving forward as well, so what I am trying to do is list all the people and all their records that have DOBs that are not all the same.

For example - the filter should show both Person A records from below.

Name DOB

Person A 1/1/1950
Person B 2/2/1960
Person C 3/5/1999
Person A 1/2/1950

I hope this makes sense. I really appreciate any help. I tried searching for this but didn't find any "multiple record" posts where having multiple records was a good thing.
 

plog

Banishment Pending
Local time
Today, 00:47
Joined
May 11, 2011
Messages
11,613
You are going to need 2 subqueries to do this. You didn't provide your table name, so replace all instances of 'YourTableNameHere' below with your table's name:


Code:
SELECT [Name], DOB FROM YourTableNameHere GROUP BY [Name], DOB

Paste the above into a new query object and name it 'sub1'. It gets all unique permutations of Name and DOB.

Code:
SELECT [Name] FROM sub1 GROUP BY [Name] HAVING COUNT([Name])>1

Paste that into a new query and name it 'sub2'. It finds all the Names with multiple DOB values. Finally, you create a new query using sub1 and sub2. You JOIN them via their name fields and bring down all the records from sub1.
 

moke123

AWF VIP
Local time
Today, 01:47
Joined
Jan 11, 2013
Messages
3,852
it may be a good idea for you to post your tables or a screenshot of your relationships. Why is DOB in the visits table? it should be in the table with people names and only a foreign key in the visits table.
 

jlavin

New member
Local time
Today, 01:47
Joined
Jul 17, 2017
Messages
4
it may be a good idea for you to post your tables or a screenshot of your relationships. Why is DOB in the visits table? it should be in the table with people names and only a foreign key in the visits table.

Yeah, DOB is not. I was just wording it in a way to hopefully make sense of what my end objective was here. What plog posted basically got me moving in the right direction now. Thanks for chiming in.
 

Users who are viewing this thread

Top Bottom