Multi-field Index

[itprog]

I am trying to set a multiple field index on a table. I would like to use 3 fields. One is a text field, the other two are date fields. The text field and one date field will always have a value. The other date field can either have a value or no value. The index will work if I only use the text field and date field that always have values. If I try to add the third field, it will not find a duplicate record. Can I not include a field that may have a null value?

 

[llkhoutx]

Why are you using dates and text as indicies? Are these fields indexed? Unique?

An index shouldn't be Null or empty.

Your method doesn't pass my smell test.

 

[itprog]

I have a patient database. The text field is the patient id, one date field is the admission date, the other the discharge date. A patient cannot have more than one admission record with a blank discharge date. That is what I would like to use the multi-field index to check for.

So each field in itself does not have a unique value, but combined the three fields would create a unique value.

 

[llkhoutx]

Be sure that the Required property for each index is set to No.

I just tried to build a table with

AutoNumber
dtAdmitted
dtDischarged

then selected all 3 as the primary key.

Tried to create a record with dtDischarged empty. I received an error message, a primary key cannot have a null value; an indexed field can.

You must be using the three fields as a primary key. In that event all fields must have values.

I've done medical work in the past and it's beyond me why you need a three key patient index. You're much smarter than I.

A patient, is a patient, is a patient and the patient can have a multitude of admissions and discharges. That's just two one to many relationships. It's quite simple to find the last admission, whether or not a discharged.

I think that you need to rethink your design.

What am I missing.

 

[itprog]

I cannot set the index as a primary key. I get an error message that I cannot have null values. I actually just need a 2 key index with the patient id and the discharge date to make sure the patient doesn't have more than one record with a blank discharge date.

The user has a form to enter admissions and discharges. I am trying to prevent the user from entering another admission for a patient if they haven't completed the discharge information on a previous admission for the same patient.

 

[llkhoutx]

You don't do that by setting a PatientID dtDischarged primary key, you merely check for a Null or empty dtDischarged for the PatientID before allowing a new record to be created for a patient.

Is a patient who dies while admitted disharged? I think not.