Creating a Unique ID by combining data from two other fields

  • Thread starter Thread starter dcornett63
  • Start date Start date
D

dcornett63

I would like to create a unique primary key by combining two fields within
the same table, the CU # (5-digit numeric) with the Task # (single digit
numeric), preferrable separated by a hyphen. The CU # is what will link to
other tables. The Task is limited to this table only. However, I want to
allow the user to select a certain Task # only once for any given CU #. For
Example, CU # 55555 may have a Task 1, a Task 2, a Task 3, etc. However, that
CU cannot have two Task 1s. In this case, the unique primary keys would be
"55555-1" for Task 1, "55555-2" for task 2 and so on.
Can this be done automatically?
 
What do you mean by "automatically"? There is no such built-in
functionality in Access; but you can do it with VBA code. That means you do
not give your users direct access to tables or queries in datasheet view,
but you shouldn't be doing that, anyway, as it opens too many opportunities
for human error resulting in an "OH ****" occurrence of one sort or another.

The question would be "Why?" If you want to show the value to the users,
combine the fields in your form or report -- that doesn't mean it has to be
the PK. There's a good deal to be said for surrogate keys (e.g., Autonumber)
as Primary Keys for ease of linking and relationships.

Larry Linson
Microsoft Office Access MVP
 
Open your table in design view, select both of the fields and
set them as a combined PK. As far as the dash, that would
only be for display purposes. You could create a concatenated
field in a query with a dash in between the two field values
(i.e for the record source of a report) or on a form you could
just place a label in between the two text boxes.
 
Back
Top