Building Composit key on the fly, Boy Scout Data Base
John,
Yes, Yes, that worked perfectly. It was simpler than i thought.
Rule: Use the DBMS to do the work when ever you can!
Thank loads....
"John W. Vinson" wrote:
On Fri, 26 Mar 2010 13:40:01 -0700, Dick Patton
wrote:
Hi all,
I have two tables each is unique. One is the "Merit Badge" tabel the other
"person Id" (name address info) tabel, there is a link tabel "MB-ID_link".
This intent is to connect mert badges with the IDs of Councelors. That works
great, however, i find that i can add duplicate records (same Person, same
Merit badge many times) this is not good.
I created a "check_key" field in the Link tabel which i defined as primary
key. This will stop duplicates from being entered without a lot of code.
The problem is i am not sure how to populate the new key. I know of no way
of concatination in access or where to place the code, but it must be built
before update.
So - I tried: Check_key = trim([MB_code) & [Person_ID]) in the event calle
"before UPdate" that did not work!
It seems like such a simple thig to do!
It is simpler than you're making it, and you certainly do not need to
construct a new field to do it.
Instead, open the link table in design view. ctrl-click the Person ID and the
Merit Bage fields so they're both selected (darkened). Click the key icon to
make these two fields a joint, two-field Primary Key; this will require that
every record be unique for the combination, even though each field by itself
can have duplicates.
If you already have a primary key, you can instead use the Indexes tool on the
toolbar to create a unique twofield index. Put some distinctive index name in
the left column, and the person ID next to it; put the MB Code in the second
column just below the person ID, and check the "unique" checkbox.
--
John W. Vinson [MVP]
.
|