Record (A, B) is the same as (B, A): how to prevent this kind of duplicates (1 Viewer)

tvu732

Registered User.
Local time
Today, 08:32
Joined
Feb 14, 2017
Messages
10
Hi,
I am designing a vocabulary database. I want to create relationships between words within the database, so I make a junction table, add two fields (Word1, Word2) and another field for relations.

I want to avoid the situation in which a word pair, for example (Word1 = A; Word2 = B) is accidentally repeated in reverse order (Word1 = B; Word2 = A). Since each word pair only has one relationship, the combination of (A, B) is the same as (B, A), but Access wouldn't know it. It recognizes these as two distinct records.

How to avoid this situation? Maybe a Before Change Event of some sort?
 

bob fitz

AWF VIP
Local time
Today, 01:32
Joined
May 23, 2011
Messages
4,717
You could test for the duplication using a query. Something like:
Code:
SELECT YourTable.Word1, YourTable.Word2
FROM YourTable AS YourTable_1 INNER JOIN YourTable ON (YourTable.Word1 = YourTable_1.Word2) AND (YourTable_1.Word1 = YourTable.Word2);
You would need to do this after the form has updated and then delete the last entry if a match is found
 

tvu732

Registered User.
Local time
Today, 08:32
Joined
Feb 14, 2017
Messages
10
You could test for the duplication using a query. Something like:
Code:
SELECT YourTable.Word1, YourTable.Word2
FROM YourTable AS YourTable_1 INNER JOIN YourTable ON (YourTable.Word1 = YourTable_1.Word2) AND (YourTable_1.Word1 = YourTable.Word2);
You would need to do this after the form has updated and then delete the last entry if a match is found
Thank you. It worked. But do I have to do this manually every time I add new records? I'm still new to Access, and although I've learnt some basic lessons, still trying to recall and apply them.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Feb 19, 2013
Messages
16,553
you could use the dcount function in the form before update event. something like

Code:
if dcount("*","myTable","[Word1]='" & me.word2 & "' AND [Word2]='" & me.word1 & "'")>0 then 'this combo already exists, so advise and cancel
    msgbox "not updated as reverse combination already exists"
    cancel=true
end if
 

Beetle

Duly Registered Boozer
Local time
Yesterday, 19:32
Joined
Apr 30, 2011
Messages
1,808
Have you tried just using a composite key on Word1 and Word2 in that table?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Sep 12, 2006
Messages
15,614
am easy way, is to ensure that word1 has to precede word2 lexically.

this sort of thing.

Code:
if word1>word2 then
   myfunc(word2,word1)
else
   myfunc(word1,word2)
end if
 

bob fitz

AWF VIP
Local time
Today, 01:32
Joined
May 23, 2011
Messages
4,717
Thank you. It worked. But do I have to do this manually every time I add new records? I'm still new to Access, and although I've learnt some basic lessons, still trying to recall and apply them.
I would use some code to do the check for duplication and the deleting of those records
 

tvu732

Registered User.
Local time
Today, 08:32
Joined
Feb 14, 2017
Messages
10
you could use the dcount function in the form before update event. something like

Code:
if dcount("*","myTable","[Word1]='" & me.word2 & "' AND [Word2]='" & me.word1 & "'")>0 then 'this combo already exists, so advise and cancel
    msgbox "not updated as reverse combination already exists"
    cancel=true
end if
Hi,
Could you explain the criteria "[Word1]='" & me.word2 & "' AND [Word2]='" & me.word1 & "'". I'm still learning and not very familiar with expressions. Thank you.
 

tvu732

Registered User.
Local time
Today, 08:32
Joined
Feb 14, 2017
Messages
10
am easy way, is to ensure that word1 has to precede word2 lexically.

this sort of thing.
Hi, I got the part word1>word2, however could you explain more about myfunc(word2,word1)? I know virtually nothing about codes. :(
 

stopher

AWF VIP
Local time
Today, 01:32
Joined
Feb 1, 2006
Messages
2,396
An alternative option is you can add a constraint to your table like this:

Code:
ALTER TABLE tblWords ADD CONSTRAINT ckWords 
CHECK	(
	NOT EXISTS (
		SELECT * FROM tblWords AS t 
		WHERE t.Word1=tblWords.Word2 and t.Word2=tblWords.Word1
		)
	) ;

So the above only allows you add/change a record providing there are no other records with the words swapped.

Note you need to set your database SQL Mode to ANSI 92 to implement constraints like this.

Not sure what your table is called so I called it tblWords.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Sep 12, 2006
Messages
15,614
Hi, I got the part word1>word2, however could you explain more about myfunc(word2,word1)? I know virtually nothing about codes. :(

I am not sure how you are using the 2 words, but I meant always ensure that the first word comes before the second one alphabetically, whether searching for words, or inserting word pairs in your table - so you can never get duplicates.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Feb 19, 2013
Messages
16,553
Could you explain the criteria "[Word1]='" & me.word2 & "' AND [Word2]='" & me.word1 & "'".
the assumption is you are using a form and not entering data directly in a table

[Word1] and [Word2] are the names of the fields in your table. You haven't told us what it is, so I called it 'myTable'.

me.word1 and me.word2 are the names of the controls on your form

the double and single quotes are used to construct a string which is your criteria.

dcount will return 0 if it finds no records and 1 or more if it find records that already exist.

my post was in response to

But do I have to do this manually every time I add new records?

by using my code, if you try to enter a combination that already exists, the insert is aborted
 

tvu732

Registered User.
Local time
Today, 08:32
Joined
Feb 14, 2017
Messages
10
Have you tried just using a composite key on Word1 and Word2 in that table?
I see what you meant, but the composite key prevents the duplicate of the combo (Word1, Word2) in the exact order. If the new record is (Word2, Word1) it doesn't trigger any alert because this by definition is not a duplicate.
 

tvu732

Registered User.
Local time
Today, 08:32
Joined
Feb 14, 2017
Messages
10
I am not sure how you are using the 2 words, but I meant always ensure that the first word comes before the second one alphabetically, whether searching for words, or inserting word pairs in your table - so you can never get duplicates.
I got the "first word comes before the second one" part, but don't understand what exactly myfunc is (googled and didn't find it). I'm still learning the ropes of the expression builder, let alone codes. :banghead:

I really appreciate every answer in this post, each helped improve my knowledge on MS Access. But... can you give me a word of advice, I got the feeling that it's necessary for me to learn SQL codes before I can build my first database? Up until now I just finished a basic Access course online.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Sep 12, 2006
Messages
15,614
myfunc was nothing.

it was just an example that if a user enters words in the wrong order, and then clicks a button to do some stuff - eg in this case a function called myfunc() - then the code behind the button calls the function with the words in the correct order.

Alternatively, you can test if the words are in the right order, and if not, give the user an appropriate warning, and tell him the first word must be before the second one.
 

tvu732

Registered User.
Local time
Today, 08:32
Joined
Feb 14, 2017
Messages
10
An alternative option is you can add a constraint to your table like this:

Code:
ALTER TABLE tblWords ADD CONSTRAINT ckWords 
CHECK	(
	NOT EXISTS (
		SELECT * FROM tblWords AS t 
		WHERE t.Word1=tblWords.Word2 and t.Word2=tblWords.Word1
		)
	) ;

So the above only allows you add/change a record providing there are no other records with the words swapped.

Note you need to set your database SQL Mode to ANSI 92 to implement constraints like this.

Not sure what your table is called so I called it tblWords.
Hi,
Does this mean that I need to create a query and run it every time I want to check for duplicates? I still don't understand how to run the check automatically each time I update the table, because as far as I've learnt up until this point, running a query is a manual task. That's why I really like your solution and bob_fits' one (using INNER JOIN), but I don't know how to apply them.

On a side note, CJ_London's solution (if DCount>0) works but it needs a form. I'm not sure if I need forms for such a simple 3-field table (I'm new to Access, maybe creating form to input data is a good practice?)
 

Minty

AWF VIP
Local time
Today, 01:32
Joined
Jul 26, 2013
Messages
10,355
The golden rule is Always use a form for data input, never allow anyone to get to a table directly.

Forms allow you complete control over how and why data is entered, updated or even deleted.
 

tvu732

Registered User.
Local time
Today, 08:32
Joined
Feb 14, 2017
Messages
10
myfunc was nothing.

it was just an example that if a user enters words in the wrong order, and then clicks a button to do some stuff - eg in this case a function called myfunc() - then the code behind the button calls the function with the words in the correct order.

Alternatively, you can test if the words are in the right order, and if not, give the user an appropriate warning, and tell him the first word must be before the second one.
I forgot to mention that some relations I will set for word pairs are hierarchical, so [word1] is kind of "broader" than [word2] (but not always). Therefore using alphabetical order doesn't work, but thanks for the idea.
 

tvu732

Registered User.
Local time
Today, 08:32
Joined
Feb 14, 2017
Messages
10
The golden rule is Always use a form for data input, never allow anyone to get to a table directly.

Forms allow you complete control over how and why data is entered, updated or even deleted.
Uhm, thanks. I kind of know that, but I'm more familiar with Excel and still love the ability to see everything in a spreadsheet and move quickly between columns/rows when inputting data.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 01:32
Joined
Feb 19, 2013
Messages
16,553
still love the ability to see everything in a spreadsheet and move quickly between columns/rows when inputting data.
that's what datasheet forms are used for. Just be aware that access is not a bigger excel. Data is stored differently and presentation/input is done separately through forms and reports, whereas excel mixes data and presentation in one spreadsheet.

good luck with your project
 

Users who are viewing this thread

Top Bottom