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
How to remove duplicate values when multiple tables are joined (3 Viewers)
- Thread starter karmvir
- Start date
- Local time
- Today, 05:27
- Joined
- Oct 29, 2018
- Messages
- 22,084
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.
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.
- Local time
- Today, 08:27
- Joined
- Feb 19, 2002
- Messages
- 45,488
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.
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
- Local time
- Today, 07:27
- Joined
- Feb 28, 2001
- Messages
- 28,738
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.
June7
AWF VIP
- Local time
- Today, 04:27
- Joined
- Mar 9, 2014
- Messages
- 5,617
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?
What is PersonDescriptiveRoll table supposed to be for?
No primary keys are defined. Which table is the "master" and which tables are dependent and will have foreign key field?
What is PersonDescriptiveRoll table supposed to be for?
You're table and fields are not set up properly. The method for doing that is called normalization:
en.wikipedia.org
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.
Database normalization - Wikipedia
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.
