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)
-   -   Multiple records question (https://www.access-programmers.co.uk/forums/showthread.php?t=294786)

jlavin 07-17-2017 11:25 AM

Multiple records question
 
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 07-17-2017 11:45 AM

Re: Multiple records question
 
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 07-17-2017 12:36 PM

Re: Multiple records question
 
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 07-18-2017 10:19 AM

Re: Multiple records question
 
Quote:

Originally Posted by moke123 (Post 1539547)
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.


All times are GMT -8. The time now is 08:54 PM.

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