Simple question about multiple dulpicates in tables

[iTanas]

Hi,

I have a table that has 2 columns: software_name and computer_name, both of
which are cross-referenced from other tables (called softwares and computers
respectively).

How can I get rid of ability to enter data such as following:

office2003 computer01
windowxp computer01
office2003 computer02
windowsxp computer02
....
flash7 computer05
flash7 computer05

Everything before the "..." is fine, however it doesn't make sense to be
able to list "flash7" twice for the same computer, and yet I cannot just
simply tell it to "not allow duplicates" because I would like to list flash7
many times but for different computers, or similarly list the same computer
name multiple times only associated with different software. So is there a
way to have it check both columns and make sure that there is no same
combination of two software and computer?

Thanks.

 

[Douglas J. Steele]

You can create a unique index comprising of both software_name and
computer_name.

 

[Dirk Goldgar]

Hi,

I have a table that has 2 columns: software_name and computer_name,
both of which are cross-referenced from other tables (called
softwares and computers respectively).

How can I get rid of ability to enter data such as following:

office2003 computer01
windowxp computer01
office2003 computer02
windowsxp computer02
...
flash7 computer05
flash7 computer05

Everything before the "..." is fine, however it doesn't make sense to
be able to list "flash7" twice for the same computer, and yet I
cannot just simply tell it to "not allow duplicates" because I would
like to list flash7 many times but for different computers, or
similarly list the same computer name multiple times only associated
with different software. So is there a way to have it check both
columns and make sure that there is no same combination of two
software and computer?

You can create a unique index on the *combination* of the two field. If
your table doesn't already have a primary key, you could make that
two-field combination the primary key. In Table Design View, you do
that by selecting both fields (by shift-clicking their selectors) and
then clicking the "Primary Key" toolbar button (the one with the picture
of a key).

 

[John Vinson]

Everything before the "..." is fine, however it doesn't make sense to be
able to list "flash7" twice for the same computer, and yet I cannot just
simply tell it to "not allow duplicates" because I would like to list flash7
many times but for different computers, or similarly list the same computer
name multiple times only associated with different software. So is there a
way to have it check both columns and make sure that there is no same
combination of two software and computer?

Yes - a couple of ways in fact!

The simplest way would be to create a unique two-field index. Open the
table in design view; select the Indexes tool from the toolbar (looks
like lightning hitting a datasheet); put some distinctive name in the
left column and these two fieldnames in the right column, one on the
same row, one on the next. Specify Unique in the properties of the
index and save it.

If your table does not already have a Primary Key you can do the same
thing by making these two fields a joint PK. Ctrl-mouseclick both
fields so they are both selected and click the Key icon. This creates
a unique index as well.

John W. Vinson[MVP]

 

[iTanas]

Thank you all for your help.

Yes - a couple of ways in fact!

The simplest way would be to create a unique two-field index. Open the
table in design view; select the Indexes tool from the toolbar (looks
like lightning hitting a datasheet); put some distinctive name in the
left column and these two fieldnames in the right column, one on the
same row, one on the next. Specify Unique in the properties of the
index and save it.

If your table does not already have a Primary Key you can do the same
thing by making these two fields a joint PK. Ctrl-mouseclick both
fields so they are both selected and click the Key icon. This creates
a unique index as well.

John W. Vinson[MVP]