How to remove duplicate values when multiple tables are joined (1 Viewer)

karmvir

New member
Local time
Tomorrow, 02:59
Joined
Nov 22, 2024
Messages
6
I have made an access database containing several tables like basic details table having details of person like IS as primary key name, parent name, address in second table I'd as foreign key and person description details as gender, height, weight, features in third table his relatives details Id as foreign key, relative name, relative parentage, relative address in fourth table I have made the friends details in which Id is foreign key and name of friend his address and age. After creating these tables I merged all the fields by using SQL code inner join function in query the result shows the repetation of person details and his features in every row also it shows the repetation of last data of family members field if friends are more than family members. Due to this repetation I am unable to create a form as it is showing multiple duplicate values. Please help
 
Hi. Welcome to AWF!

That is normal behavior of joined queries. What do you intend to do with the form? Typically, it's best to base the form to a single table, as much as possible, rather than using multiple tables as the form's source.
 
The important thing to understand is that the schema you described is not hierarchical. Therefore, including tables from multiple branches in a single query will always result in "duplicating" data.

You need to use a form for each separate table rather than a form bound to a query that joins all the tables. You can use subforms so you have one main form with person information and three subforms, one with each other type of data.

Think about your data. relatives are related to persons and friends are related to persons but relatives are not related to friends. Therefore relatives * friends = duplicates. If you have 3 relatives and 2 friends, you end up with 6 rows. 3 * 2 = 6. If you have 5 relatives and 10 friends, you end up with 50 rows. 5 * 10 = 50

Also, there is a larger problem and that is that people are people and so you should not have three tables to hold people, you should have 1. Then you can have a relationship table that links people rows to other people rows with a relationship code attached so you know if the related record is a friend or a mother.
 
The important thing to understand is that the schema you described is not hierarchical. Therefore, including tables from multiple branches in a single query will always result in "duplicating" data.

You need to use a form for each separate table rather than a form bound to a query that joins all the tables. You can use subforms so you have one main form with person information and three subforms, one with each other type of data.

Think about your data. relatives are related to persons and friends are related to persons but relatives are not related to friends. Therefore relatives * friends = duplicates. If you have 3 relatives and 2 friends, you end up with 6 rows. 3 * 2 = 6. If you have 5 relatives and 10 friends, you end up with 50 rows. 5 * 10 = 50

Also, there is a larger problem and that is that people are people and so you should not have three tables to hold people, you should have 1. Then you can have a relationship table that links people rows to other people rows with a relationship code attached so you know if the related record is a friend or a mother.
i am attaching the file with the reply will you please suggest the way it can be done. I need all the data in one print out without repetation. please help if you can suggest the code also i will be greatful. At least please suggest layout. big problem of data in one table is that which coloumn will be primary key. I am suffering from this problem a long but no body is giving a solution. please suggest
 

Attachments

I am suffering from this problem a long but no body is giving a solution. please suggest

Your problem is due to a data design issue. You are getting the duplication you mention because of what SQL "really" does. It is a program to find matching combinations. When you use more than one table for data sources, you get multiple combinations that match up to the same records and get that duplication. What it means in practical terms is that you are trying to join things together incorrectly.

You need to look up "database normalization" to help you design a better data structure. You need to look up "permutation join" to understand why you are getting what you are getting.

From the description, you have a setup that is more like an Excel flat file than a relational database. This will frequently give you that repetition that bothers you.
 
The solution is for you to learn about database normalization and how data is related.

No primary keys are defined. Which table is the "master" and which tables are dependent and will have foreign key field?
 
Last edited:
You're table and fields are not set up properly. The method for doing that is called normalization:


Give that link a read, google a few tutorials, apply what you learn to your data and then post back here your updated database. With that said, here's the big things I see wrong with your data:

1. PersonDescriptiveRoll should not be a seperate table. Sex, Height, Complexion, etc. is just like a person's name--they have only one. That means all that data should be in personaldetails, not PersonDescriptiveRoll.

2. Tables with the same/similar structure. Your 4 Detailsof_ tables should only be 1 table. And instead of storing part of the data in the table name (e.g. _Associates, _Friends, _FamilyMembers) you would make a new field in that 1 table to hold the type of relationship (e.g [RelationshipType]) and in it you would store all those table name suffixes.

3. Storing calculated values. Age should not be a field in your table if you are storing DateOfBirth. You can calculate someone's age when you need it as long as you have their birth date, so no need to store age.

Again those are the big things I see. Read up on normalization, give it another shot with your database and then post back here what you have and we will get you there.
 
i am attaching the file with the reply will you please suggest the way it can be done. I need all the data in one print out without repetation. please help if you can suggest the code also i will be greatful. At least please suggest layout. big problem of data in one table is that which coloumn will be primary key. I am suffering from this problem a long but no body is giving a solution. please suggest
Hi Karmvir

Your table design should be on the lines of that shown in the attached screenshot.
 

Attachments

  • RI.png
    RI.png
    38.2 KB · Views: 54
I need all the data in one print out without repetation.
I told you exactly how to accomplish that. ONE form per table. You can combine several forms onto a main form if that makes sense. Please read my suggestion again. The others have also added further suggestions on how to normalize the tables.
 
i am attaching the file with the reply will you please suggest the way it can be done. I need all the data in one print out without repetation. please help if you can suggest the code also i will be greatful. At least please suggest layout. big problem of data in one table is that which coloumn will be primary key. I am suffering from this problem a long but no body is giving a solution. please suggest
Hi
The Forms you need to create based on the suggested Relationship diagram are as follows.

The Main Form based on PersonDetails with a Subform based on the Associations table.
 

Attachments

  • MainSubForm.png
    MainSubForm.png
    43.5 KB · Views: 38
I need all the data in one print out without repetation.
Forms are for data entry and edit. Reports are for "print out".

If you don't want "repetition" on form, then use form/subform(s) as advised.
 
as per the advise i have made the table however i have made two tables insted of many but still the problem is same and there is repetation. i am attaching the file can anyone suggest what can be done now.
 

Attachments

as per the advise i have made the table however i have made two tables insted of many but still the problem is same and there is repetation. i am attaching the file can anyone suggest what can be done now.
Hi
You do not use a query to display data that has field repetition.

See the Report in the attached.
 

Attachments

Hi
You do not use a query to display data that has field repetition.

See the Report in the attached.
can you please elaborate the reply that either you have used the query or tables on the report
 
The query was used to create the Report
. As you have solved my problem exactly How you have stopped repetation can you please tell the way how you did that.have you used any code or something else. Please tell.
 
. As you have solved my problem exactly How you have stopped repetation can you please tell the way how you did that.have you used any code or something else. Please tell.
1 Person can have 1 or more Associates.

Your query will show repeating Person details for every Associate.

When you create a Report based on the query you have an option to Group by. In this case Group by Person.

The Person details are placed in the GroupBy Header and the Associate details are placed in the Details area of the Report.

No Code needed.
 
as per the advise i have made the table however i have made two tables insted of many but still the problem is same and there is repetation. i am attaching the file can anyone suggest what can be done now.
ONE TABLE PER FORM!!!!!!! Not sure what part of that statement was ambiguous. Once you understand database design, it is possible to include "lookup tables" in the query a form is based on but that is not what you are doing. You need to stick to ONE main table per form.

The exact number of tables is dictated by the data and the relationships. NOT your decision. If you have two feet, you can't get by with ONE shoe because you think it looks more attractive. This is a case of "it is what it is".

Please post your properly normalized tables again and once the schema is correct as we understand it, then and only then should you proceed to designing other objects.
 

Users who are viewing this thread

Back
Top Bottom