semi-unique record

[mangeloni]

I want to set up a semi-unique entry in a table...let me explain:

Participants Table (Access 2000):
-Email field (key field)
-GuesserUN field

I want to allow the user to enter as many records as needed with their email address but only once in combination with an entry number in GuesserUN. Other records can also share the same GuesserUN, but only once each.

For example:
Email value GuesserUN value
(entered by user) (entered with hidden field SV from previous login)
1@2.com G001 >> OK
1@2.com G002 >> OK, same email, different GuesserUN
1@2.com G001 >> NOT ok, since this value combo has already been entered
A@B.com G001 >> OK, different email with this
GuesserUN

How can I accomplish this? I thought of having another field which combines the values of Email (excluding everything after @) and GuesserUN into one field, creating a unique combination. Does anybody know how to accomplish my idea - or do you have another approach?

Using DW4, ASP/JavaScript

Your help is greatly appreciated!!

~MVA

[Ribeyed]

hi,
in database trems your looking for a composite key. This means that both combine are unique. When creating your primary key in Access 2000 instead of selecting one field in design mode select the 2 you want to be a composite key. You will then see both fields have the primary key symbol beside them, making them both part of the composite key.

Hope this helps.

[Frank Blissett]

Here's another (more computation intensive, less fool-proof) method:

Select all records from the email and store in a dataset. Then loop through all rows of the dataset, and if myRow("GuesserUN") = newGuesserUN.text, then flip the value of a boolean to false.

Next, if BOO then input the new record, else give error message.

[mangeloni]

Tried looking into the Composite Key approach...but I need further help with this-

EMail - when making this a primary key, Access automatically disallows duplicates
GuesserUN - cannot find a way to make this a 'composite' key with EMail (no help in Access on this)

I want to allow duplicates for both these fields - just not when the pair combined is repeated.

Also, do you know anything about UltraDev4? It has a behavior for checking unique fields - but I don't think it has one for composite keys. I'll look around.

Thanks again

~MVA

[Ribeyed]

Hi,
please look at the screen shot in the zip file bellow. To creat a composite key in access you need to select the 2 fields you want and then click the primary key icon on the tool bar.