Finding duplicates out of differents fields

[Bakta]

Sorry for the question's wording.

Here is what bugs me:

- I have one table tblRefctc with the followings fields :
LCtcRef,../..,LRefServ0, LrefServ1,..LrefServ16.

Where all those Lref are long; LCtcRef being the primary key. This table describe a contact from different branches (account, etc..) so while each branch has the relevant information about this contact, they know that that branch too is in contact with the same person.

- I have this other table with describes Events, TblIdxEvt (meeting, whatever) :

LRefidx,IdxEvt,IdxCtc

where IdxCtc is filled with LCtcRef once an event is created.

What I must check for is : For the same IdxEvt, should another branch makes an appointement for LCtcRef (i), I must check if one of the LRefServ0 to LRefServ16 are not already added in the event table.

At this time, I am stuck with making functions which test the existence of the LRefServ(i) (i ranging from 0 to 16) in tblIdxEvt, but I wonder if there is a simpler way to do it with SQL (which, as you could guess, Im not good at).

Thanks in advance for any pointers.

 

[Pat Hartman]

Your table is not properly normalized. That is why you are having trouble using it. You have created a repeating group by flattening a 1-many relationship similar to what you would do in a spreadsheet. Relational tables are not spreadsheets and should not be designed to look like them. You need a second table to handle the many-side of the relationship. If you do not change your design, you will be stuck with writing your own functions to work with the multiple occurances of the repeating group. SQL will not help you because the relational theory on which it is based, specifically prohibits repeating groups.

Search for topics on normalization to understand what I am talking about.

 

[Bakta]

Many thanks.