Duplicate values question

[Spidey3721]

Is there a way that I can limit a table to only having unique values in
one field FOR EACH different value in another field ?

For Example, if I have a table that holds cost info for different Projects,
[Project],

tbl Project Costs
[ProjectID] - name of project
[CostID] (Examples: Plumbing cost, Drywall cost, roofing cost, etc...) -
linked to a CostCode table with all possible costs
[Cost Amount] - Dollar figure for that cost type

I want to limit a user from being able to enter in two different values for
drywall cost for the same job....

I would also like to avoid having to keep separate tables for each project,
although maybe that is the only way ?

 

[Wayne Morgan]

You can set up a unique index on more than one field. This would require the
combination of the two fields to be unique.

Example
1,1
1,2
2,1
2,2
would be acceptable.

1,1
1,1
1,2
2,1
2,2
2,1
would not be acceptable.

To do this, open the table in design mode. Go to View|Indexes. Type in a
name for the index in the left column. In the Field Name column select the
first field, then select a sort order. On the next row, skip the Index Name
column and go to the Field Name column and select the next field then its
Sort Order. Place the cursor back in the first row and set the Unique value
at the bottom to Yes.

 

[George Nicholson]

Create a single index that includes both fields and set its Unique property
to Yes.

In the Indexes design dialog box:
Index Name: ProjectCost
FieldName (on same line): ProjectID
FieldName (on next line): CostID (leave IndexName blank on this line)
Then set the Unique property of this Index to Yes. You'll need to deal with
any pre-existing duplicates before this step will "take", but once it is
set, it will only allow unique combinations of those 2 fields to be entered.

Hope this helps,