Access Yes/No: IF record exists in another table then Yes, ELSE No (1 Viewer)

[wlc-cw]

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!

 

[Gasman]

Research DLookUp() function.

 

[Minty]

You don't need the vaccinated field in the patient records.
You simply join the two tables together with a left join and then in the query you can make a calculated field

Iif(Vaccinations.Patient_ID IS Null, "No", "Yes")

To display it on your patient record, you could use a couple of subforms, one for vaccinations, one for tests.

 

[Pat Hartman]

There is no reason to store a Vaccinated flag when you can determine if the patient is vaccinated using the other table.

Minty's method is fine or you can create a query that counts the vaccinations by person so you can tell if a person had 1, 2, 3, 100 COVID vaccinations and then join to that query. That way you can analyze based on the number of vaccinations

There probably shouldn't be a table named COVID results. Wouldn't those go into the test database?

I know this is specifically for "COVID" but if you can make it for any disease related to a vaccination, why would you limit yourself?

 

[wlc-cw]

There is no reason to store a Vaccinated flag when you can determine if the patient is vaccinated using the other table.

Minty's method is fine or you can create a query that counts the vaccinations by person so you can tell if a person had 1, 2, 3, 100 COVID vaccinations and then join to that query. That way you can analyze based on the number of vaccinations

There probably shouldn't be a table named COVID results. Wouldn't those go into the test database?

I know this is specifically for "COVID" but if you can make it for any disease related to a vaccination, why would you limit yourself?

Thank you very much for your response!

My intention was to have something like this:

Vaccinations

Patient_ID​	Vaccination Type​	Vaccination Date​
1​	Johnson & Johnson​	10/11/2021​
3​	Pfizer​	10/11/2021​

Patient Tests

Patient_ID​	Vaccinated​	Test Type​	Test Date​
1​	Yes​	Lateral Flow​	13/11/2021​
2​	No​	Lateral Flow​	13/11/2021​
3​	Yes​	Lateral Flow​	13/11/2021​
4​	No​	PCR​	14/11/2021​

Instead of having to manually open the Vaccinations table, manually having to find the same Patient ID then manually ticking the "Vaccinated" attribute Yes/No checkbox in the Patient Tests table, it would be desirable if the table could automatically detect if the patient has been vaccinated or not simply from the Patient ID, by detecting if it is, or isn't, in the Vaccinations table. If it is, tick (or Yes), if it isn't, empty checkbox (or No).

The COVID Results table exists because my college course scenario has asked me to design a database for a (fake) GP Surgery to record COVID tests and COVID test results. I think I'm therefore required to have two separate tables. What's more, not all patients will have (or will record) their results on the same day they take the test, so it makes sense (I believe) to have two separate tables that are linked by the Patient ID. I think this is better for Normalisation too, for example some patients might not record their results at all so there would be blank spaces, whereas having two tables prevents these potential blank spaces.

Finally, I could make this database for any disease, but this would go outside the scope of my college assignment and require wasted effort.

 

[Pat Hartman]

You do not need to put the vaccination status in the tests table. PERIOD. You pick up the vaccination status as you need it in the query your report is based on.

If tests are shown as a subform and you want to see the COVID vaccination status, you can add a field to the parent form that included a dLookup() or dCount() to show the status.

The test table should also include the results of the test. Postive/Negative.

 

[gemma-the-husky]

The problem with storing the yes/no value, is that if the vaccination record gets deleted subsequently, then the yes/no value will become incorrect. If you don't store the result you can't get the problem.

There are other issues, too.

The vaccinated status yes/no has NOTHING to do with the lateral flow tests, and shouldn't really be stored in the LFT record. You could conceivably have a patient where vaccinated is no on some records and yes on others for the same patient, as it would be dependent on the date of the lateral flow test, and the date of the vaccination.

In fact it's also dependent on the date the vaccination was entered into the database rather than the date of the vaccination, which is even more dangerous, especially for medical records.

It's better to look up the vaccination status whenever you need it. eg, In the future you may need to know the date of a booster, or even the date of the last vaccination, if the powers that be decide that vaccinations will time-expire, in which case an expired vaccination is NO vaccination.

In passing there may be some cases where it IS worth storing something of this nature, but it does need careful consideration, and generally would be something that would never change.

 

[GPGeorge]

Thank you very much for your response!

My intention was to have something like this:

Vaccinations

Patient_ID​	Vaccination Type​	Vaccination Date​
1​	Johnson & Johnson​	10/11/2021​
3​	Pfizer​	10/11/2021​

Patient Tests

Patient_ID​	Vaccinated​	Test Type​	Test Date​
1​	Yes​	Lateral Flow​	13/11/2021​
2​	No​	Lateral Flow​	13/11/2021​
3​	Yes​	Lateral Flow​	13/11/2021​
4​	No​	PCR​	14/11/2021​

Instead of having to manually open the Vaccinations table, manually having to find the same Patient ID then manually ticking the "Vaccinated" attribute Yes/No checkbox in the Patient Tests table, it would be desirable if the table could automatically detect if the patient has been vaccinated or not simply from the Patient ID, by detecting if it is, or isn't, in the Vaccinations table. If it is, tick (or Yes), if it isn't, empty checkbox (or No).

The COVID Results table exists because my college course scenario has asked me to design a database for a (fake) GP Surgery to record COVID tests and COVID test results. I think I'm therefore required to have two separate tables. What's more, not all patients will have (or will record) their results on the same day they take the test, so it makes sense (I believe) to have two separate tables that are linked by the Patient ID. I think this is better for Normalisation too, for example some patients might not record their results at all so there would be blank spaces, whereas having two tables prevents these potential blank spaces.

Finally, I could make this database for any disease, but this would go outside the scope of my college assignment and require wasted effort.

Frank Sinatra said it first, but it does apply here, unfortunately.

 

[Pat Hartman]

The COVID Results table exists because my college course scenario has asked me to design a database for a (fake) GP Surgery to record COVID tests and COVID test results. I think I'm therefore required to have two separate tables.

Your professor is more likely to be impressed that you did it right than that you followed instructions literally. That is assuming he actually knows what is right especially if you can justify your solution as being more flexible. If the former programmers had done it right the first time, you would not need to be creating a new, separate db just for COVID. The existing app would have handled it fine by just adding a new test type of COVID

If you have a table with tests, that is where the results will go and "COVID" in test type with a "Positive" result is what you will look for doing certain reports.