Hello,
I am a student currently studying an IT course at college, of which includes a unit on Database Design.
My project is to design and develop a relational database for a (fake) GP Surgery so they can record COVID-19 test results and vaccination records.
I have five tables in this database, but I just need help with two (denoted with *):
- Patients (Primary Key: Patient_ID)
- Staff
* Vaccinations (Foreign Key: Patient_ID)
* Patient Tests (Foreign Key: Patient_ID)
- COVID Results
In the Vaccinations table, I have the records of patients who have had a vaccine.
In the Patient Tests table, I have the records of patients, both vaccinated and not-vaccinated, who have taken Lateral Flow and/or PCR tests.
In both tables, the attribute Patient_ID is a foreign key (the primary key of the Patients table).
In the Patient Tests table, I have the attribute "Vaccinated", with the Yes/No data type, to determine if the patient who has taken a COVID-19 test is vaccinated or not. However, instead of having to manually tick the checkbox for every patient (i.e. record as YES), I would like the Vaccinated attribute to detect the Patient_ID entered in the Patient Tests table and search for it in the Vaccinations table, then automatically tick the checkbox (i.e. record as YES) if the record exists in the Vaccinations table.
Is this possible? Do I have to use a query? If I need to use a query, how would I implement it it in the Patient Tests Vaccinated attribute? I have a feeling this can be done in SQL. I have tiny tiny bit of MySQL knowledge and I know an IF statement could somehow be used, but Access is confusing in that it's software based. I imagine a SQL statement would be sparsely similar to this:
IF EXISTS (Patient Tests.Patient_ID, Vaccinations.Patient_ID) then Yes, ELSE No.
Or, can this simply not be done in Access?
Thanks in advance for your time!
I am a student currently studying an IT course at college, of which includes a unit on Database Design.
My project is to design and develop a relational database for a (fake) GP Surgery so they can record COVID-19 test results and vaccination records.
I have five tables in this database, but I just need help with two (denoted with *):
- Patients (Primary Key: Patient_ID)
- Staff
* Vaccinations (Foreign Key: Patient_ID)
* Patient Tests (Foreign Key: Patient_ID)
- COVID Results
In the Vaccinations table, I have the records of patients who have had a vaccine.
In the Patient Tests table, I have the records of patients, both vaccinated and not-vaccinated, who have taken Lateral Flow and/or PCR tests.
In both tables, the attribute Patient_ID is a foreign key (the primary key of the Patients table).
In the Patient Tests table, I have the attribute "Vaccinated", with the Yes/No data type, to determine if the patient who has taken a COVID-19 test is vaccinated or not. However, instead of having to manually tick the checkbox for every patient (i.e. record as YES), I would like the Vaccinated attribute to detect the Patient_ID entered in the Patient Tests table and search for it in the Vaccinations table, then automatically tick the checkbox (i.e. record as YES) if the record exists in the Vaccinations table.
Is this possible? Do I have to use a query? If I need to use a query, how would I implement it it in the Patient Tests Vaccinated attribute? I have a feeling this can be done in SQL. I have tiny tiny bit of MySQL knowledge and I know an IF statement could somehow be used, but Access is confusing in that it's software based. I imagine a SQL statement would be sparsely similar to this:
IF EXISTS (Patient Tests.Patient_ID, Vaccinations.Patient_ID) then Yes, ELSE No.
Or, can this simply not be done in Access?
Thanks in advance for your time!