Expression where it shows me what test hasn't been done?

[yepwingtim]

I have a Tests Field Combo Box having data:
Bloodwork, Hormonal Levels, Screening, Monitoring

lets say for a patient, she only did bloodwork and hormonal.

Is it possible to make an expression where it tells me hormonal levels and monitoring not done in a form or report?
________
EASY VAPE DIGITAL

 

[jal]

Assuming your table is called Patients and looks like this (I'll pretend you are using the patient name as the patient ID).

ID..................TestedFor
Sally Smith.....Bloodwork
Sally Smith.....Hormonal
Pete Hicks......Screening
Jack Purvy.....Monitoring

Then you could probably do the following. First a simple version which is very readable but less performant:


SELECT Patients.* , 'Needs Hormonal' as Deficiency FROM Patients
WHERE Patients.ID NOT IN
(
SELECT P.ID FROM Patients as P WHERE P.TestedFor = 'Hormonal'
)

UNION ALL

SELECT Patients.*, , 'Needs Monitoring' as Deficiency FROM Patients
WHERE Patients.ID NOT IN
(
SELECT P.ID FROM Patients as P WHERE P.TestedFor = 'Monitoring'
)

The following version is less readable but more performant:


SELECT Patients.* , 'Needs Hormonal' as Deficiency FROM Patients
LEFT JOIN
(
SELECT 'Hormonal' as TestedFor
) as RequiredTests
ON RequiredTests.TestedFor = Patients.TestedFor
WHERE RequiredTests.TestedFor IS NULL

UNION ALL

SELECT Patients.* , 'Needs Monitoring' as Deficiency FROM Patients
LEFT JOIN
(
SELECT 'Monitoring' as TestedFor
) as RequiredTests
ON RequiredTests.TestedFor = Patients.TestedFor
WHERE RequiredTests.TestedFor IS NULL

 

[yepwingtim]

Hi Jal Thanks for replying - I'm not that familar with union alls
- I need this printed in my report is that possible?
________
Lamborghini cheetah history

 

[jal]

I don't do much reporting, but first things first. Get this working as a query (open a new query and paste it into SQL view after changing the column names to your actual names).

Then use the query as the basis for a form or report. Once you have the query working, just start another thread with any form-questions or report-questions you have.

 

[jal]

By the way UNION ALL simply means 'append the following'. Suppose you have 2 tables with the same column names, and you want to make them into one table.

SELECT * FROM table1
UNION ALL
SELECT * FROM table2

 

[yepwingtim]

HI Jal maybe i'm not explaining this too welll. Here is the concept database I made.

http://75.127.124.193/Database7.mdb

if you go into the demographic report you will see that "Daniel Li" has done "Hormonal", "Prog", "Blood" Tests.

But he has not yet done (lets just say) "HIV/AIDS", "Sonogram", "Baseline", Tests.

It is possible for that information to go into the report of the page in an unbound text?
________
Toyota Tf108 History

 

[jal]

Oh, sorry, I see syntax errors, give me a moment

 

[jal]

I think I got the syntax error fixed above.

 

[jal]

Ok, I looked at your Results table, it is exactly what I expected to find. You named it "Results" although I thought it might be called "Patients". My query above seems correct, although you may wish to inner join to the demographics table as to include that information in the results - you can do this in the same query or, alternatively, you can first get my version working and then inner join my query to the demographics table (in a seprate query). As you like. Maybe I'll do it for you.

 

[yepwingtim]

OK i'll try this =D thanks alot
I'll probably won't know how to render this to my report though =x
________
WINNING

 

[yepwingtim]

mm i don't think its working

http://75.127.124.193/Database7.mdb - I have the union query in their but if you take a look at ID 2 where I put "Monitoring" - some of the other id 2's says need hormonal. T_T
________
Civic Si

 

[jal]

You're not translating my code correctly. As I stated, the "ID" column is supposed to identify the patient. In your case it is called "DemograhicsID".

In the translation, therefore, replace my use of "ID" with "DemographicsID".

 

[jal]

As a separate issue, though, I do see a problem with my code. I should have done another left join. Give me a moment....

 

[jal]

I see why I missed that - at first I assumed it was only one table. Give me a moment...

 

[jal]

By the way, let's revert back to your table name "Results" - I like that better.

 

[jal]

And let's rename the demographics table "Patients"

 

[jal]

Revised version (I changed some column names and table names).

SELECT Patients.* , 'Needs Hormonal' as Deficiency FROM Patients
WHERE Patients.PatientID NOT IN
(
SELECT Results.PatientID FROM Results WHERE Results.TestedFor = 'Hormonal'
)
UNION ALL
SELECT Patients.*, 'Needs Monitoring' as Deficiency FROM Patients
WHERE Patients.PatientID NOT IN
(
SELECT Results.PatientID FROM Results WHERE Results.TestedFor = 'Monitoring'
);

 

[yepwingtim]

wow this is great Jal - now I only need to join it in a report somehow =D
________
Yamaha Yp400 Majesty

 

[jal]

wow this is great Jal - now I only need to join it in a report somehow =D

Access reporting is a very broad topic because Access has a lot of features.

Play around with the reporting wizards. If you don't make any headway, maybe start a thread in the Reports forum. Although I do fine with basic queries, I'm not very skilled with reports.

 

[jal]

Also, if you have a lot of patients, you might want to sort the list. You can do this by addning two ORDER BY clauses (one for each of the two queries unioned above:

SELECT...
(
)
ORDER BY LastName, FirstName