Junction Table No Duplicates - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Junction Table No Duplicates

Jim Baldner

5 years ago

I have a junction table joining four tables. I want to ensure there are no duplicated records of the combined four tables.

What I'm doing now:

I created a calculated field in the junction table. The calculation is "[tbl1*1000]+[tbl2*100]+[tbl3*10]+tbl4]"... This field is has no duplicates allowed.

Is there any other way of accomplishing that no combination is replicated?

	Adam Schwanz @Reply 5 years ago
I would use just use a before update event to check if the record exists myself. Use DCount and the ID criteria and if its >0 it exists.

	Scott Axton @Reply 5 years ago
Even easier would be to use a Composite Key

	Adam Schwanz @Reply 5 years ago
Indeed. I thought there was something like that but i cant think straight tonight ;)

	Jim Baldner OP @Reply 5 years ago
Thx Scott, composite key it is

	Scott Axton @Reply 5 years ago
Jim - If you want to have a more customized error message for your users check out this article: Customize Access Error Message

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View