Table relationship question

[jcrmit]

Hi,

I have created a 3 tables;

1) client information (e.g. name, age, address, etc)

2) medication injection info that a client is on (these change over time - so there will be multiple enties for a client, and some clients will have none) - e.g. Injection type, dose, frequency, start & end date, and an active [tick box] that indicates that a record is the current prescribed injection. This allows for the tracking of previous injections over time.

3) This table hold the records for every instance of an injection given (e.g. to who (client number - which is used in all three tables, injection type, dose, date given and by who).

I have created forms and subforms for entry of the information.

My worry is about generating meaning ful queiries and reports from these tables;

1. I want to able to return a query of the injections a client has recieved over a time period (e.g. 1 year) and be able to see the changes in dose, when an injection end, etc and I am not sure that my tables are organised in the best way to achieve this.

Am I making this too complicated or not enough?

Sorry if I am not being - am happy to provide more info.

 

[giacomo]

seems as if you may have an issue with the dose field, why is it in both tables? If the dosage needs to change at the time of the injection then maybe you should call the dose in the medication_injection_info table recommended_dose or something like that.

Beyond that your concept looks good to me at first glance... if there is a specific query you're trying to run and having trouble please post.

hth,
Giacomo