Multi-Table Query Question

[DFlynn24]

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]

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]

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]

@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]

@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]

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 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]

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]

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]

Should I just post it as an attachment to a reply?

 

[theDBguy]

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]

Yep, just put in some quick test data. For the Full Name parameter, use "John Smith".

 

[theDBguy]

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...

 

[DFlynn24]

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]

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]

So would the query that I had already created in the sample database suffice?

 

[theDBguy]

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]

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]

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.

 

[DFlynn24]

That one worked perfectly. How were you able to eliminate the SubjectID parameter?