Multi-Table Query Question (1 Viewer)

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:15
Joined
Feb 28, 2001
Messages
27,001
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.
 

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
@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)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
@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.
 

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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!
 

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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.
 

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
Should I just post it as an attachment to a reply?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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.
 

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
Yep, just put in some quick test data. For the Full Name parameter, use "John Smith".
 

Attachments

  • Sample.zip
    25.5 KB · Views: 102

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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...
 

Attachments

  • Sample.zip
    32.6 KB · Views: 112

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
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.
 

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
So would the query that I had already created in the sample database suffice?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
So would the query that I had already created in the sample database suffice?
It could, but you could also just create a new one based only on the Person table rather than on multiple tables.
 

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
So I just created a new query quickly using only the Person table. When I ran it I was prompted for "SubjectID" as well as "Full Name". I have a couple of questions:

How can I make it so SubjectID is not a parameter?

Also, did you link the Address and Phones reports to the Person report by simply using the subform control?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:15
Joined
Oct 29, 2018
Messages
21,358
So I just created a new query quickly using only the Person table. When I ran it I was prompted for "SubjectID" as well as "Full Name". I have a couple of questions:

How can I make it so SubjectID is not a parameter?

Also, did you link the Address and Phones reports to the Person report by simply using the subform control?
Hi. Yes, I linked the subforms using the SubjectID field. Okay, try the attached again.
 

Attachments

  • Sample.zip
    31.9 KB · Views: 94

DFlynn24

Registered User.
Local time
Today, 04:15
Joined
May 1, 2019
Messages
29
That one worked perfectly. How were you able to eliminate the SubjectID parameter?
 

Users who are viewing this thread

Top Bottom