Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2017, 11:25 AM   #1
jlavin
Newly Registered User
 
Join Date: Jul 2017
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
jlavin is on a distinguished road
Cool 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.

jlavin is offline   Reply With Quote
Old 07-17-2017, 11:45 AM   #2
plog
Newly Registered User
 
Join Date: May 2011
Posts: 7,866
Thanks: 10
Thanked 1,900 Times in 1,861 Posts
plog has a spectacular aura about plog has a spectacular aura about plog has a spectacular aura about
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.
plog is offline   Reply With Quote
The Following User Says Thank You to plog For This Useful Post:
jlavin (07-18-2017)
Old 07-17-2017, 12:36 PM   #3
moke123
Newly Registered User
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 343
Thanks: 0
Thanked 107 Times in 101 Posts
moke123 is on a distinguished road
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.

moke123 is offline   Reply With Quote
Old 07-18-2017, 10:19 AM   #4
jlavin
Newly Registered User
 
Join Date: Jul 2017
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
jlavin is on a distinguished road
Re: Multiple records question

Quote:
Originally Posted by moke123 View Post
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.

jlavin is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Vba inserting multiple records from multiple unbound text boxes jackie77 Modules & VBA 5 05-08-2014 02:16 AM
Question: Counting records in multiple tables in one querry Top Fuel Friday Queries 1 09-23-2010 10:42 AM
Question [Urgent] Filtering and saving multiple records in multiple columns. sx1 General 9 06-03-2009 12:37 AM
How to show multiple records on form only if multiple records exist? duckster Reports 5 09-21-2005 08:05 PM
Question on Saving in multiple records jazzscreamer General 4 03-27-2004 01:04 PM




All times are GMT -8. The time now is 12:44 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


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