Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-23-2019, 04:53 AM   #1
DFlynn24
Newly Registered User
 
Join Date: May 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
DFlynn24 is on a distinguished road
Multi-Table Query Question

I am in the process of building a database that contains tables for Personal Info (Name, DOB, License #), Addresses (Street, City, County, State), and Phone Numbers. I have done this because one person often has multiple addresses and/or phone numbers

I have successfully linked the tables with a primary and foreign keys and created a form with subforms to enter data into every table at once.

The one problem I am running into is with my queries. Ideally, I would like to try and have it that when I search someone's full name it gives me all of their information without repeating.

For example, John Smith has lived at 123 Main St and 54 1st St. He also has had the phone numbers 123-456-7890 and 555-555-555 attached to him.

When I run my query now, the output looks like this:

123 Main St 123-456-7890
123 Main St 555-555-5555
54 1st St 123-456-7890
54 1st St 555-555-5555


Is there anyway I can mitigate these duplicates? Thank you.

DFlynn24 is offline   Reply With Quote
Old 07-23-2019, 06:59 AM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Multi-Table Query Question

Hi. When you use a query to join multiple tables, it will return the matching data from each table. To remove the duplicate information, what would you prefer to see? Would you expect to see blank lines? How about if you could have a single row for each person and a combined list of all phone numbers in one column? Would that work for you? If so, you could take a look at this simple function.
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 07-23-2019, 07:29 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,352
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Multi-Table Query Question

I have a different question. Was one of those numbers attached to one address and the other number to the other address? If so, what you showed us looks like a permutation join (a.k.a. Cartesian join). I won't go farther on this until you answer because I don't want to jump to confusions. However, If I am right, I have a structural question for you to consider.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 07-23-2019, 07:33 AM   #4
DFlynn24
Newly Registered User
 
Join Date: May 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
DFlynn24 is on a distinguished road
Re: Multi-Table Query Question

@DBGuy Blank lines would be fine. I just don't want the addresses to duplicate if there are other columns that have multiple entries for one subject

@Doc_Man No the numbers are not attached to the addresses at all. I have all my tables linked by a subjectID (primary key in the subjects table and foreign key in the others)
DFlynn24 is offline   Reply With Quote
Old 07-23-2019, 07:37 AM   #5
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Multi-Table Query Question

Quote:
Originally Posted by DFlynn24 View Post
@DBGuy Blank lines would be fine. I just don't want the addresses to duplicate if there are other columns that have multiple entries for one subject
Hi. Unfortunately, that answer is not a "database" friendly answer. A query will return something if there's data or information in the field. If the field is empty, then it will return as blank. Since there are matching address for each person, the query will return this for every matching phone number as well. The only way to clean this up is to use a Report (for display purposes). The database assumes you were going to use the query to manipulate the data. But if you just want to display it, then you can use a report. Either that or you can export the query to Excel, and you can then clean it up from there.
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
DFlynn24 (07-23-2019)
Old 07-23-2019, 07:39 AM   #6
DFlynn24
Newly Registered User
 
Join Date: May 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
DFlynn24 is on a distinguished road
Re: Multi-Table Query Question

Quote:
Originally Posted by theDBguy View Post
Hi. Unfortunately, that answer is not a "database" friendly answer. A query will return something if there's data or information in the field. If the field is empty, then it will return as blank. Since there are matching address for each person, the query will return this for every matching phone number as well. The only way to clean this up is to use a Report (for display purposes). The database assumes you were going to use the query to manipulate the data. But if you just want to display it, then you can use a report. Either that or you can export the query to Excel, and you can then clean it up from there.

I think I would rather use report if that is the case. Thank you for the prompt response.
DFlynn24 is offline   Reply With Quote
Old 07-23-2019, 07:42 AM   #7
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Multi-Table Query Question

Quote:
Originally Posted by DFlynn24 View Post
I think I would rather use report if that is the case. Thank you for the prompt response.
Right. When you design the report, you can select the box for the data you don't want to see duplicated and set the Hide Duplicates property to Yes. Good luck!

__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 07-23-2019, 08:44 AM   #8
DFlynn24
Newly Registered User
 
Join Date: May 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
DFlynn24 is on a distinguished road
Re: Multi-Table Query Question

Hey so I designed my reports and set Hide Duplicates to Yes. However, I am still having a problem. In my actual report the person has 4 addresses and two phone numbers. After hiding the duplicates I am getting the following output:

123 Main St 123-456-7890
54 1st St
9 State St
12 Park Ave
123 Main St 555-555-5555
54 1st St
9 State St
12 Park Ave
DFlynn24 is offline   Reply With Quote
Old 07-23-2019, 08:56 AM   #9
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Multi-Table Query Question

Quote:
Originally Posted by DFlynn24 View Post
Hey so I designed my reports and set Hide Duplicates to Yes. However, I am still having a problem. In my actual report the person has 4 addresses and two phone numbers. After hiding the duplicates I am getting the following output:

123 Main St 123-456-7890
54 1st St
9 State St
12 Park Ave
123 Main St 555-555-5555
54 1st St
9 State St
12 Park Ave
You may have to add a grouping to your report. If you could post a sample copy of your db with test data, someone here might be able to give it a try.
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 07-23-2019, 09:02 AM   #10
DFlynn24
Newly Registered User
 
Join Date: May 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
DFlynn24 is on a distinguished road
Re: Multi-Table Query Question

Should I just post it as an attachment to a reply?
DFlynn24 is offline   Reply With Quote
Old 07-23-2019, 09:07 AM   #11
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Multi-Table Query Question

Quote:
Originally Posted by DFlynn24 View Post
Should I just post it as an attachment to a reply?
Make sure there are no sensitive data in it (just test data), do a compact and repair, zip it up, and then attach to your post.
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 07-23-2019, 09:20 AM   #12
DFlynn24
Newly Registered User
 
Join Date: May 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
DFlynn24 is on a distinguished road
Re: Multi-Table Query Question

Yep, just put in some quick test data. For the Full Name parameter, use "John Smith".
Attached Files
File Type: zip Sample.zip (25.5 KB, 7 views)
DFlynn24 is offline   Reply With Quote
Old 07-23-2019, 09:32 AM   #13
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Multi-Table Query Question

Quote:
Originally Posted by DFlynn24 View Post
Yep, just put in some quick test data. For the Full Name parameter, use "John Smith".
Hi. Actually, I was thinking you might like a different approach using subreports. Take a look at the attached and open the Person report. Hope it helps...
Attached Files
File Type: zip Sample.zip (32.6 KB, 10 views)
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
DFlynn24 (07-23-2019)
Old 07-23-2019, 09:37 AM   #14
DFlynn24
Newly Registered User
 
Join Date: May 2019
Posts: 19
Thanks: 2
Thanked 0 Times in 0 Posts
DFlynn24 is on a distinguished road
Re: Multi-Table Query Question

That definitely looks a lot better. So if I decide to use your approach how do I set up a parameter so that when I want to generate a report by searching by name it outputs in the format you just created?
DFlynn24 is offline   Reply With Quote
Old 07-23-2019, 09:41 AM   #15
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Multi-Table Query Question

Quote:
Originally Posted by DFlynn24 View Post
That definitely looks a lot better. So if I decide to use your approach how do I set up a parameter so that when I want to generate a report by searching by name it outputs in the format you just created?
You simply modify the record source for the Person report to use a parameter query. Give it a try and let us know how it goes. If it doesn't work, I'll make a change to my copy and reupload it.

__________________
Just my 2 cents...

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.
theDBguy 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
Newbie question...data entry form, multi-table PeterZ7 Forms 4 05-01-2017 04:48 PM
Multi-table query question mstng07 Queries 7 04-10-2008 09:26 AM
Multi Record/Query/Multi Table/Going Crazy Issues smbarney Queries 5 09-16-2007 05:13 PM
Multi-table multi-option query George Bowyer Queries 10 06-15-2004 09:20 AM




All times are GMT -8. The time now is 06:09 AM.


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

Featured Forum post


Sponsored Links


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