relationships

[Guest]

What is the best way to handle this situation.

Value Table has the following fields
SerialNo | EquipmentDescription

124 | HW0001
134 | HW0001
541 | HW0007
321 | HW0008


Client table has following fields amoung others. SerialNo can be blank
(I.E. this client may not have this equipment from above table in their
possession,
but no two clients can have the same equipment)

Client | SerialNo
1 | 124
2 |
3 |
4 | 541
5 | 321
6 | 321 NOT ALLOWED

Is there a way to handle this with indexes or relationships, rather than
programatically? If so, how? If not, what is the best way to approach this?

 

[Guest]

Open the client table in design view. Set index property --

Yes (No Duplicates)

 

[Guest]

Create a unique index on the Client and Serial Number columns in the Clients
table. To do this open the Clients table in design view and select the
View|Indexes menu item. In one row of the first column of the Indexes
dialogue give the index a name, e.g ClientEquipment and in the second column
enter Client, in the third column Ascending. On the next row leave the first
column blank and enter SerialNo and Ascending in the second and third
columns. Select Yes for Unique.

 

[Guest]

This won't work because multiple blank fields are permitted.

 

[Guest]

This is what I needed, however I can't find where I'm supposed to select Yes
for unique.

I went to design view for the table.
I selected Veiw|Indexes from the menu.
I added the following information
IndexName | FieldName | SortOrder
Row 1: ClientEquip | ClientID | Ascending
Row 2: | SerialNo | Ascending

But now what? I don't see where to select "Yes"

 

[Guest]

Disregard my previous post. I'm just that blind.

 

[Guest]

After thinking about this I realized that this won't work anyway.

ClientID is unique by itself so ClientID and Equipment will never be the
same together regardless of the individual value of Equipment.

Client1 Equip1
Client2 Equip1

With the index you suggested would still produce a unique value but is
incorrect because Equip1 can only be used once per table.

ClientID is unique in this table already.
Client | SerialNo
1 | 124
2 |
3 |
4 | 541
5 | 321
6 | 321 NOT ALLOWED

Client 6 should not be allowed to have same item as client 5 however making
the serial number as an index with no duplicates won't work because blanks
are allowed and there can be multiple blanks. There will not be multiple
clients that index is already taken care of. There cannot be multiple serial
numbers, this is what I need to take care of.

 

[Guest]

I think I dismissed this answer too quickly because I'm used to programming
in another language. I forgot that Access also has the "Required" and allow
zero length data. Setting required to no and allowing zero length with no
duplicates does make this the solution to my problem. Thank you.

 

[Jack MacDonald]

As you look at the design of the table, you will see where you can set
the index on each field. But if you open View > Indexes, you will see
each of the indexes for the whole table.

Select the index for the SerialNumber field. It should be:
Primary - No
Unique - Yes
Ignore Nulls - Yes

After thinking about this I realized that this won't work anyway.

ClientID is unique by itself so ClientID and Equipment will never be the
same together regardless of the individual value of Equipment.

Client1 Equip1
Client2 Equip1

With the index you suggested would still produce a unique value but is
incorrect because Equip1 can only be used once per table.

ClientID is unique in this table already.
Client | SerialNo
1 | 124
2 |
3 |
4 | 541
5 | 321
6 | 321 NOT ALLOWED

Client 6 should not be allowed to have same item as client 5 however making
the serial number as an index with no duplicates won't work because blanks
are allowed and there can be multiple blanks. There will not be multiple
clients that index is already taken care of. There cannot be multiple serial
numbers, this is what I need to take care of.

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Guest]

Oops, my brain had obviously atrophied completely. All you need is to index
the SeialNo column uniquely. The multiple 'blanks' don't matter because,
strange as it might seem at first sight, Null does not equal Null. This is
because Null is not a value, but an absence of a value, an unknown. So the
answer to the question 'does Null = Null?' is Null, which if you think about
it makes sense as the answer to 'does unknown = unknown?' is obviously
'unknown'.