ensuring two fields do not have same values

[Access Sortof Newb]

Good morning all,

I have a table that has a unique identifier key, ID, but there are two
other fields PNID and PLID that cannot be the same in more than one
record:
Example
ID PNID PLID
1 5 5
2 5 3
3 5 5 duplicated with ID = 1, so not
allowed.

Is there any way to check for these inconsistencies within the table
properties, or would I have to create a query to ensure these
duplicates are not present? PNID and PLID are not keyed.

thanks for the help

Tim

 

[Douglas J. Steele]

Create an index on PNID and PLID and set its Unique property to True.

 

[Allen Browne]

You can create a unique index on the combination of PNID + PLID.

1. Open the table in design view.

2. Open the Indexes dialog (toolbar.)

3. On a fresh row in the dialog, enter a name for the index and the first
field name. Then in the lower pane of the dialog, set Unique to Yes.

4. On the next line of the dialog, leave the Index Name column blank
(indicating the previous name continues on this row), and enter the 2nd
field name. The entry will look something like this:
IndexName Field
======== ====
PnidPlid PNID
PLID

5. Save.

 

[Access Sortof Newb]

You can create a unique index on the combination of PNID + PLID.

1. Open the table in design view.

2. Open the Indexes dialog (toolbar.)

3. On a fresh row in the dialog, enter a name for the index and the first
field name. Then in the lower pane of the dialog, set Unique to Yes.

4. On the next line of the dialog, leave the Index Name column blank
(indicating the previous name continues on this row), and enter the 2nd
field name. The entry will look something like this:
IndexName Field
======== ====
PnidPlid PNID
PLID

5. Save.

GENIUS!!! Thank you so much for that help! I have been wrapping my
head around how to do this for a while now, since I have a few tables
that could use this indexing.

Many thanks

Tim

 

[Guest]

What if the table that you want to create an index for is a linked table (to
Oracle)? Access won't allow me to change the index property. Indexing a
query that just copies the table would seem like an option, though I don't
think you can create an index on a query either.

My intent is to create a query that does not allow unique combinations of
two fields. To make things even more complicated. The query is using the
same field twice to gather before and after information. I'm querying plant
information collected in monitoring plots over time. Each time the plot is
visited, a new record is created. I want to see the values of plot X at time
1 and time 2 in one row of a query.

I've linked the table to itself and joined the plot field. Then I add all
records to the query twice (once from each table). Then I say that one of
the Year columns (time 2) has to be greater than the other year column (time
1). If the plot has been visited more than once, I might get this (in this
case, the plot has been visited 3 times):

Plot Year1 Value1 Year2 Value2
----------------------------------------------------
X 2001 a 2003 b
X 2001 a 2005 c
X 2003 b 2005 c

I want to exclude the 2001 - 2005 combination. I think I can do this by
combining the Year2 and Value2 fields into a unique combination (such as
through indexing). No can do with the table being linked, though.
Suggestions?

 

[Guest]

CORRECTION: I would combine the Year2 and PLOT fields if it were possible to
do so. Sorry...