Table Indexes

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

I currently have a table with Indexes as follows:

Key2 Computer Ascending
DBNum Ascending
InstNum Ascending
PrimaryKey ClientID Ascending


I would now like to restrict input further by restricting a CompanyID to a single Computer.
There can be multiple entries for a single CompanyID, but they must not be allowed to
enter multiple Computer choices for that CompanyID.

Can this be done in the Table Indexes area, or must I do this elsewhere?

Thanks,
Bernie
 
You can create a multiple field index. In the Index dialog put a name for
the index, under Field Name choose
CompanyID, move down one row and in the Field Name choose Computer (don't
put anything in the Index Name).

Move back to the row with the Index name and change the Unique property to
Yes.
 
What you suggested is exactly what I thought, only it doesn't work. As I look at my
original post, I see the tabs don't work as they should, so I guess I should do it again, to
try to make my problem more understandable.

Key3 CompanyID Ascending
Computer Ascending

Key2 Computer Ascending
DBNum Ascending
InstNum Ascending

PrimaryKey ClientID Ascending

Key2 is Unique.
PrimaryKey is Unique and Primary.
And now I make Key3 Unique also.

When saving the table, I get the message that the changes requested would create
duplicate values in the index, primary key or relationship.

A ClientID may have multiple "Computer assignments", but the Computer, DBNum and
InstNum must be unique.

Now I want to make sure that for any CompanyID, only one computer may be selected.

Some actual data so you can see what I'm trying to do.
ClientID CompanyID Computer DBNum InstNum
31 15 A14 95 2
37 15 A14 95 9
34 15 A14 95 6
33 15 A14 95 5
The following is data that I DO NOT want to allow because the Computer is different than
one already selected for that CompanyID:
39 15 A25 95 7

Any more suggestions?

Thanks,
Bernie
 
It sounds as though you are trying to store all your information in a single
table, when you need to create multiple tables. You said
"A ClientID may have multiple Computer assignments"

The computer information should, therefore, be in a separate table, and a 1
to many relationship established between the clients and the computers
table.


--
Joan Wild
Microsoft Access MVP

bw said:
What you suggested is exactly what I thought, only it doesn't work. As I look at my
original post, I see the tabs don't work as they should, so I guess I should do it again, to
try to make my problem more understandable.

Key3 CompanyID Ascending
Computer Ascending

Key2 Computer Ascending
DBNum Ascending
InstNum Ascending

PrimaryKey ClientID Ascending

Key2 is Unique.
PrimaryKey is Unique and Primary.
And now I make Key3 Unique also.

When saving the table, I get the message that the changes requested would create
duplicate values in the index, primary key or relationship.

A ClientID may have multiple "Computer assignments", but the Computer, DBNum and
InstNum must be unique.

Now I want to make sure that for any CompanyID, only one computer may be selected.

Some actual data so you can see what I'm trying to do.
ClientID CompanyID Computer DBNum InstNum
31 15 A14 95 2
37 15 A14 95 9
34 15 A14 95 6
33 15 A14 95 5
The following is data that I DO NOT want to allow because the Computer is different than
one already selected for that CompanyID:
39 15 A25 95 7

Any more suggestions?

Thanks,
Bernie
Click to expand...
 
Back
Top