Finding duplicate combinations of two fields

johndoomed

VBA idiot
Local time
Today, 08:22
Joined
Nov 4, 2004
Messages
174
Hello!

I've made a "match making"-database for an B2B-event. In short: About 65 companies is getting 10 meetings each. I've made tbl_meeting with 3 columns:

meetID
Company1
Company2

The question is: How to I search for duplicate combinations? Look at the attached image for example - meetID 2 and 5 is a duplicate combo.

I just can't get my head around how to identify the duplicate combinations - so any help would really be appreciated!
 

Attachments

  • Access.PNG
    Access.PNG
    4.1 KB · Views: 157
I would not store the two companies in the same row. A relational database gets its power from modelling the relationships between things, so if one meet has two companies then the best model of that problem uses two tables with a one-to-many relationship.
tMeet
MeetID (Primary Key)

tMeetCompany
MeetCompanyID (Primary Key)
MeetID (Foreign Key)
Company
In this model each meet has many, maybe two, rows, and all the companies are in the SAME COLUMN! Therefore you can write a GROUP BY query that uses aggregate functions like Max, Min, Count, Avg, to compare summaries of the components of a meet.

Does that make sense?

For more info, read up on the "principles of database normalization."
 
SELECT company.id,company.company1,company.company2 from company, company as t1 where (company.company1 = t1.company1 and company.company2 = t1.company2 and company.id <> t1.id) or (company.company1=t1.company2 and company.company2=t1.company1 )
 
Follow Mark's advice on the structure. Note this also allows more than two companies to be in the same meeting.

Put a composite no duplicates index on MeetID and CompanyID. This will make it impossible to enter the same company twice. Personally I would also make this index the Primary Key.
 

Users who are viewing this thread

Back
Top Bottom