Trying to duplicate data

[lucy31]

I have a medical database (Access 2003) containing information about patients. There is 1 form that is used to enter the information and a check box to say whether the patient has cancer.

I run a simple query to select the cancer patients.

I want to be able to add a large amount of information about (only) the cancer patients through a separate form. I also want a neighbouring hospital to be able to add cancer patients.

The ideal solution (I think) is to create an extra table to contain the extra information for cancer patients, and some of the informationfrom the original table. Cancer patients need to be automatically added to the cancer table once the "cancer" box is checked.

So far I have not ben able to achieve this - a create table query does not update automatically. I have tried creating a new table with the cancer information and creating a relationship between the tables and basing the form on the cancer table and a select cancer query. This does not work either.

Please can anyone suggest a solution! Do I need another database?

Thanks

 

[workmad3]

you should be able to do it fairly easily. What you need is a table with all the information required for cancer patients, along with a foreign key to link to the primary key of the main table. You can then tell if a patient is a cancer patient or not simply by if they are in the cancer patients table, rather than having extra information.

 

[lucy31]

Foreign key

What is a foreign key?