Disallow duplicates in combination of fields

  • Thread starter Thread starter dee
  • Start date Start date
D

dee

I have based a form on a query. I don't want to allow duplicates of certain
field combinations. For example:

Qstnaire ID PPtID ResponseNo
Other fields
1 6 1

1 5 2
1 6 3

I have created a field in the query that concatenates the QstnaireID and the
PptID and am trying to add something in the criteria that won't allow a
duplicate of this combination to be entered.

I know I can use a Find Duplicates query after the fact, but wish to avoid
duplicates in the first place, as they will be the result of incorrect data
entry and should not appear at all. The entire record won't be a duplicate,
just the combination of the two fields noted above, so I can't use the unique
values in the query properties.

I may be approaching this incorrectly. Perhaps I should be modifying
controls on my form...

Any assistance will, as always, be greatly appreciated!
 
Create a unique index on the combination of the 2 fields.

1. Open the table in design view.

2. Open the Indexes box (Toolbar.)

3. On the first blank line of the Indexes box, enter a name for the index,
and the first field.

4. In the lower pane if the dialog, set Unique to Yes.

5. On the next row of the dialog, leave the index name blank (indicating
this row is part of the same index), and enter the 2nd field name.

It should now look something like this:
Index Name Field Name Sorting
========= ========= =====
QstnairePPt Qstnaire ID Ascending
PPtID Ascending
 
You certainly work hard in these newsgroups! Thanks so much - it worked
perfectly!
--
Thanks!

Dee


Allen Browne said:
Create a unique index on the combination of the 2 fields.

1. Open the table in design view.

2. Open the Indexes box (Toolbar.)

3. On the first blank line of the Indexes box, enter a name for the index,
and the first field.

4. In the lower pane if the dialog, set Unique to Yes.

5. On the next row of the dialog, leave the index name blank (indicating
this row is part of the same index), and enter the 2nd field name.

It should now look something like this:
Index Name Field Name Sorting
========= ========= =====
QstnairePPt Qstnaire ID Ascending
PPtID Ascending
Click to expand...
 
Back
Top