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

 

[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