table: using multiple fields to define a duplicate

[Nick M]

Hello,

I have a table called IUList.

It has the following 3 data fields:

SerNo Type Channel

'IUList' SerNo is a relationship to a table 'IUMaster' SerNo all unique ~10k
records and consists of 2 fields: SerNo Type

Type is one of two possibilities type 11 or type 12 this translates into
channel usage.

Type 11 has only one channel. Channel 0 So I can have no duplicate entries
in IUList

Type 12 has two channels Channel 0 Channel 1 So I can have exactly 2 entries
(one duplicate) for each SerNo in the IUList

Is there a way to create a table that will not allow me to add duplicate
type 11 and allow me to add only 1 duplicate type12?

Thank you for your time.

Regards

-N

 

[Bob Quintal]

Hello,

I have a table called IUList.

It has the following 3 data fields:

SerNo Type Channel

'IUList' SerNo is a relationship to a table 'IUMaster' SerNo all
unique ~10k records and consists of 2 fields: SerNo Type

Type is one of two possibilities type 11 or type 12 this
translates into channel usage.

Type 11 has only one channel. Channel 0 So I can have no duplicate
entries in IUList

Type 12 has two channels Channel 0 Channel 1 So I can have exactly
2 entries (one duplicate) for each SerNo in the IUList

Is there a way to create a table that will not allow me to add
duplicate type 11 and allow me to add only 1 duplicate type12?

Thank you for your time.

Regards

-N

define all three fields as part of the primary key.Then set a
validation rule on the table.
(right-click in design view->properties->validation rule)

([type] = 11 and [channel] = 0) or ([type] = 12 and ([channel] = 0 or
[channel] = 1))

 

[Guest]

You can also enforce it by means of a referential operation, which allows for
greater portability. Create another table, TypeChannels say with columns
Type and Channel. Give this table 3 rows as follows:

Type Channel
11 0
12 0
12 1

Make the two columns the table's primary key. Create a relationship between
IUList and TypeChannels on the Type and Channel columns and enforce
referential integrity. All three columns of IUIList should be designated as
its primary key.

It might be advisable to avoid Type as a column name, however, as it is the
name of a VBA statement, an Access property for various objects, and both ADO
and DAO properties, so could be confused with one of these in some
circumstances. If it is used then it would be prudent to enclose it in
brackets, [Type], when referencing it in code or SQL.

Ken Sheridan
Stafford, England

 

[Nick M]

Thanks you for the information. I'll give it a try!
I can change the 'Type' column.
Regards
-N

You can also enforce it by means of a referential operation, which
allows for greater portability. Create another table, TypeChannels
say with columns Type and Channel. Give this table 3 rows as follows:

Type Channel
11 0
12 0
12 1

Make the two columns the table's primary key. Create a relationship
between IUList and TypeChannels on the Type and Channel columns and
enforce referential integrity. All three columns of IUIList should
be designated as its primary key.

It might be advisable to avoid Type as a column name, however, as it
is the name of a VBA statement, an Access property for various
objects, and both ADO and DAO properties, so could be confused with
one of these in some circumstances. If it is used then it would be
prudent to enclose it in brackets, [Type], when referencing it in
code or SQL.

Ken Sheridan
Stafford, England

Hello,

I have a table called IUList.

It has the following 3 data fields:

SerNo Type Channel

'IUList' SerNo is a relationship to a table 'IUMaster' SerNo all
unique ~10k records and consists of 2 fields: SerNo Type

Type is one of two possibilities type 11 or type 12 this translates
into channel usage.

Type 11 has only one channel. Channel 0 So I can have no duplicate
entries in IUList

Type 12 has two channels Channel 0 Channel 1 So I can have exactly 2
entries (one duplicate) for each SerNo in the IUList

Is there a way to create a table that will not allow me to add
duplicate type 11 and allow me to add only 1 duplicate type12?

Thank you for your time.

Regards

-N