Jump to content


Need help to make table (having duplicate values) relationship

relationship between tables

  • Please log in to reply
1 reply to this topic

#1 Ayaz



  • Members
  • Pip
  • 8 posts
  • LocationSaudi Arabia

Posted 03 June 2017 - 09:28 PM



I am having difficulty to make relationship between tables which are having duplicate values, please help me to make so.


I need to make relationship between table Payroll and Purchase_Ord.


In Purchase_Order one PO issued for more than one month and for many categories, I tried to have unique ID by concetinating PO+Month but didn't work.


In Payroll many employees work under different categories, I tried to have unique ID here too like above but didn't work too.





Attached Files

#2 Jonathan


    Forums Administrator

  • Administrators
  • 887 posts

Posted 04 June 2017 - 11:17 AM

Hi Ayaz,


I  have taken a look at your workbook and I think what you're trying to do is get a summary for each PO Number. However, both of your tables have duplicate PO numbers, meaning a relationship cannot be created directly between them. Relationships are almost always "One to Many", for example one Customer has many Invoices or one Teacher has many Students.


You can see a thorough explanation of this in the video lesson: Expert Skills Lesson 6-9 Understand many-to-many relationships.


From what I can see, this doesn't apply to the data in your workbook. It looks like there is actually one corresponding record in each table - a "One to One" relationship. Unfortunately I can't see any keys that would allow you to match these two records together. The only apparent link between the tables is the PO No. and the Description/Categories and I can see that these are also duplicated in places.


If this data has been extracted from another database is is likely that additional keys do exist and just need to be extracted. If the Emp_No field could be included in your second table you would be able to use this along with the PO No to create a working relationship. With the data as it is I do not think this will be possible.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users