Data validation between two tables

[usr33t]

Hi there!

I wonder if someone can help me please? I have an Access 2000 database used to record the daily treatments received by patients in our Intensive Care Unit. The database consists of 2 tables;

Table 1 has these fields; [First Name], [Surname], [Hospital Number], [ICU Admission Number] (Primary Key), [Date of admission], [Date of discharge].

Table 2 has these fields; [Date], [ICU Admission number], and various treatment fields requiring a true or false response.

The tables are linked through the [ICU Admission Number] field.

I enter data in the first instance through Table 1 and then click on the '+' in the far left hand column to access Table 2 for each record (Patient), whereupon I enter the daily treatments - each record in this table accounting for 1 day on the unit (i.e. there must be no duplicate values).

When I open Table 2 I need three things to happen to try and avoid mistakes being made.

(1) The first record in the [Date] field must only be equal to the [Date of admission] in Table 1. It must not be possible to enter anything else

(2) In subsequent records in Table 2 the date must increase by one day until it equals the [Date of discharge] in Table 1.

(3) Once this value has been reached it must not be possible to enter subsequent dates or duplicate dates.


If anybody can help me with this query I would be delighted to hear from you. I have tried fiddling around with Validation rules but have had no luck.

If you can help then that would be fantastic.

Best regards
Usr33t

 

[Pat Hartman]

The primary key of the second table should contain two fields:
TreatmentDate and ICUAdmissionNumber

To make a two-field pk, open the table in design view, highlight the first column, while holding the control key, highlight the second. Press the key icon.

Never use "Date" as a column name. Date is the name of a function and in certain situations Access won't be able to tell if you mean your date or its date. "Name" is another common problem word. Name is the name of a property and will also cause problems with VBA. There are lists of reserved words if you care to search for them so you'll know which words to avoid. Also, don't use embedded spaces in your object names. Again, VBA will actually change your object names if you include spaces or special characters.

 

[usr33t]

Thanks,

That solved the problem of being able to enter the duplicate dates. However, what is most important is that the TreatmentDate field in Table 2 cannot be less than the ICUAdmissionDate or greater than the ICUDischargeDate in Table 1 for each patient, as these are the dates when the treatments we are concerned with start and stop, respectively.

Any ideas?

Thanks again for your help.

Best regards
Usr33t

 

[Pat Hartman]

These are not they types of checks you can perform via validation rules and Jet does not support triggers. That leaves you with using the BeforeUpdate events of your forms to perform any final or multi-field validation. Cancel the update event if any errors are found.