Criteria to find what isn't there.

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

I have a table has several lab tests.
I'll call it "tbl_LabTests".

Within this table:
ClientID
LabTests
LabTestDates
LabTestResults

The field 'LabTests' has several types:
PPD
RPR
GC
Toxo
RBC
WBC

The field 'LabTestResults has several types:
Pos
Neg
ABNL
WNL
**********************
Issue:
I am able to run a query to find clients due for lab
tests depending on their last lab test.

MAX[LabTestDate]
<=Date()-365
LabTests
Like "*" & [Enter Screen] & "*"
LabTestResults
Like "*" & [To exclude positives type "neg", to see only
positive type "pos", to see all press 'enter'] & "*"
-------------
What I need is to be able to find the clients that have
never had the test done.

I hope I am clear with the request.
Thank you in advanced.

Glenn
 
Presumably you also have a table of clients, with ClientID as primary key.

To select the clients who have never had the PPD test:

SELECT tblClient.* FROM tblClient
WHERE NOT EXISTS
( SELECT ClientID FROM tbl_LabTests
WHERE tbl_LabTests.ClientID = tblClient.ClientID
AND tbl_LabTests.LabTests = "PPD" );

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209066
 
Thank you for the timely response.

I am working on using the suggestion, and will reply
shortlythe results.

Glenn
-----Original Message-----
Presumably you also have a table of clients, with ClientID as primary key.

To select the clients who have never had the PPD test:

SELECT tblClient.* FROM tblClient
WHERE NOT EXISTS
( SELECT ClientID FROM tbl_LabTests
WHERE tbl_LabTests.ClientID = tblClient.ClientID
AND tbl_LabTests.LabTests = "PPD" );

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/default.aspx?scid=kb;en- us;209066
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table has several lab tests.
I'll call it "tbl_LabTests".

Within this table:
ClientID
LabTests
LabTestDates
LabTestResults

The field 'LabTests' has several types:
PPD
RPR
GC
Toxo
RBC
WBC

The field 'LabTestResults has several types:
Pos
Neg
ABNL
WNL
**********************
Issue:
I am able to run a query to find clients due for lab
tests depending on their last lab test.

MAX[LabTestDate]
<=Date()-365
LabTests
Like "*" & [Enter Screen] & "*"
LabTestResults
Like "*" & [To exclude positives type "neg", to see only
positive type "pos", to see all press 'enter'] & "*"
-------------
What I need is to be able to find the clients that have
never had the test done.

I hope I am clear with the request.
Thank you in advanced.

Glenn
Click to expand...


.
Click to expand...
 
Here are the actual table names:

cptPatientDemographics
cptVaccinations

'cptPatientDemographics' contains the 'PatientID' as the
primary key.

'cptVaccinations' contains the 'VaccineType' field which
has several values. One being 'Flu'.

Using the subquery suggested:
(SELECT cptPatientDemographics.* FROM
cptPatientDemographics
WHERE NOT EXISTS
( SELECT PatientID FROM cptVaccinations
WHERE cptVaccinations.PatientID =
cptPatientDemographics.PatientID
AND cptVaccinations.VaccineType = "Flu" ))

I get the following error:
"You have written a subquery that can return more than one
field without using the EXISTS reserved word in the mail
query's FROM clause. Revise the SELECT statement of the
subquery to request only one field."
*************************
What I am trying to do is list the patients that have
never received a flu shot along with the patients that
have not received a flu shot within the last 365 days.

Once again thank you
for whatever attention
you can give to this

Glenn
-----Original Message-----
Thank you for the timely response.

I am working on using the suggestion, and will reply
shortly the results.

Glenn
-----Original Message-----
Presumably you also have a table of clients, with ClientID as primary key.

To select the clients who have never had the PPD test:

SELECT tblClient.* FROM tblClient
WHERE NOT EXISTS
( SELECT ClientID FROM tbl_LabTests
WHERE tbl_LabTests.ClientID = tblClient.ClientID
AND tbl_LabTests.LabTests = "PPD" );

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/default.aspx?scid=kb;en- us;209066
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table has several lab tests.
I'll call it "tbl_LabTests".

Within this table:
ClientID
LabTests
LabTestDates
LabTestResults

The field 'LabTests' has several types:
PPD
RPR
GC
Toxo
RBC
WBC

The field 'LabTestResults has several types:
Pos
Neg
ABNL
WNL
**********************
Issue:
I am able to run a query to find clients due for lab
tests depending on their last lab test.

MAX[LabTestDate]
<=Date()-365
LabTests
Like "*" & [Enter Screen] & "*"
LabTestResults
Like "*" & [To exclude positives type "neg", to see only
positive type "pos", to see all press 'enter'] & "*"
-------------
What I need is to be able to find the clients that have
never had the test done.

I hope I am clear with the request.
Thank you in advanced.

Glenn
Click to expand...


.
Click to expand...
.
Click to expand...
 
If that's the whole query, try removing the outer brackets:
SELECT cptPatientDemographics.*
FROM cptPatientDemographics
WHERE NOT EXISTS
( SELECT PatientID FROM cptVaccinations
WHERE cptVaccinations.PatientID =
cptPatientDemographics.PatientID
AND cptVaccinations.VaccineType = "Flu" );

The *main* query now does have the EXISTS keyword, so it should be okay.

Where you want the subquery to return something, you can add TOP 1 to the
subquery, and subquery's ORDER BY the primary key so it will only have one
thing to return.

There may be a date field in the subquery's WHERE clause as well, to
restrict this to the last year?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Glenn said:
Here are the actual table names:

cptPatientDemographics
cptVaccinations

'cptPatientDemographics' contains the 'PatientID' as the
primary key.

'cptVaccinations' contains the 'VaccineType' field which
has several values. One being 'Flu'.

Using the subquery suggested:
(SELECT cptPatientDemographics.* FROM
cptPatientDemographics
WHERE NOT EXISTS
( SELECT PatientID FROM cptVaccinations
WHERE cptVaccinations.PatientID =
cptPatientDemographics.PatientID
AND cptVaccinations.VaccineType = "Flu" ))

I get the following error:
"You have written a subquery that can return more than one
field without using the EXISTS reserved word in the mail
query's FROM clause. Revise the SELECT statement of the
subquery to request only one field."
*************************
What I am trying to do is list the patients that have
never received a flu shot along with the patients that
have not received a flu shot within the last 365 days.

Once again thank you
for whatever attention
you can give to this

Glenn
-----Original Message-----
Thank you for the timely response.

I am working on using the suggestion, and will reply
shortly the results.

Glenn
-----Original Message-----
Presumably you also have a table of clients, with ClientID as primary key.

To select the clients who have never had the PPD test:

SELECT tblClient.* FROM tblClient
WHERE NOT EXISTS
( SELECT ClientID FROM tbl_LabTests
WHERE tbl_LabTests.ClientID = tblClient.ClientID
AND tbl_LabTests.LabTests = "PPD" );

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/default.aspx?scid=kb;en- us;209066
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table has several lab tests.
I'll call it "tbl_LabTests".

Within this table:
ClientID
LabTests
LabTestDates
LabTestResults

The field 'LabTests' has several types:
PPD
RPR
GC
Toxo
RBC
WBC

The field 'LabTestResults has several types:
Pos
Neg
ABNL
WNL
**********************
Issue:
I am able to run a query to find clients due for lab
tests depending on their last lab test.

MAX[LabTestDate]
<=Date()-365
LabTests
Like "*" & [Enter Screen] & "*"
LabTestResults
Like "*" & [To exclude positives type "neg", to see only
positive type "pos", to see all press 'enter'] & "*"
-------------
What I need is to be able to find the clients that have
never had the test done.

I hope I am clear with the request.
Thank you in advanced.

Glenn
Click to expand...
Click to expand...
Click to expand...
 
SELECT cptPatientDemographics.MedicalRecordID AS [M R N],
[LastName] & ", " & [FirstName] AS Client,
cptStatus.PatientStatus
FROM cptPatientDemographics INNER JOIN cptStatus ON
cptPatientDemographics.PatientID=cptStatus.PatientID
WHERE (((Exists (SELECT PatientID FROM cptVaccinations
WHERE cptVaccinations.PatientID =
cptPatientDemographics.PatientID AND
((cptVaccinations.VaccineType) Like "*" & [Enter vaccine]
& "*")))=False) AND ((cptStatus.PatientStatus)="active"))
ORDER BY [LastName] & ", " & [FirstName];
**************
Works great alone.

Thank you Allen

Now all I need to do is get it to work with finding
everyone that haven't had the vaccine within 365 days.

I am currently using two queries. The first to find all
that have not received vaccinations within 365 days. The
second to find all that have never been vaccinated.

Glenn
-----Original Message-----
If that's the whole query, try removing the outer brackets:
SELECT cptPatientDemographics.*
FROM cptPatientDemographics
WHERE NOT EXISTS
( SELECT PatientID FROM cptVaccinations
WHERE cptVaccinations.PatientID =
cptPatientDemographics.PatientID
AND cptVaccinations.VaccineType = "Flu" );

The *main* query now does have the EXISTS keyword, so it should be okay.

Where you want the subquery to return something, you can add TOP 1 to the
subquery, and subquery's ORDER BY the primary key so it will only have one
thing to return.

There may be a date field in the subquery's WHERE clause as well, to
restrict this to the last year?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here are the actual table names:

cptPatientDemographics
cptVaccinations

'cptPatientDemographics' contains the 'PatientID' as the
primary key.

'cptVaccinations' contains the 'VaccineType' field which
has several values. One being 'Flu'.

Using the subquery suggested:
(SELECT cptPatientDemographics.* FROM
cptPatientDemographics
WHERE NOT EXISTS
( SELECT PatientID FROM cptVaccinations
WHERE cptVaccinations.PatientID =
cptPatientDemographics.PatientID
AND cptVaccinations.VaccineType = "Flu" ))

I get the following error:
"You have written a subquery that can return more than one
field without using the EXISTS reserved word in the mail
query's FROM clause. Revise the SELECT statement of the
subquery to request only one field."
*************************
What I am trying to do is list the patients that have
never received a flu shot along with the patients that
have not received a flu shot within the last 365 days.

Once again thank you
for whatever attention
you can give to this

Glenn
-----Original Message-----
Thank you for the timely response.

I am working on using the suggestion, and will reply
shortly the results.

Glenn

-----Original Message-----
Presumably you also have a table of clients, with
ClientID as primary key.

To select the clients who have never had the PPD test:

SELECT tblClient.* FROM tblClient
WHERE NOT EXISTS
( SELECT ClientID FROM tbl_LabTests
WHERE tbl_LabTests.ClientID = tblClient.ClientID
AND tbl_LabTests.LabTests = "PPD" );

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/default.aspx? scid=kb;en-
us;209066
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

message
I have a table has several lab tests.
I'll call it "tbl_LabTests".

Within this table:
ClientID
LabTests
LabTestDates
LabTestResults

The field 'LabTests' has several types:
PPD
RPR
GC
Toxo
RBC
WBC

The field 'LabTestResults has several types:
Pos
Neg
ABNL
WNL
**********************
Issue:
I am able to run a query to find clients due for lab
tests depending on their last lab test.

MAX[LabTestDate]
<=Date()-365
LabTests
Like "*" & [Enter Screen] & "*"
LabTestResults
Like "*" & [To exclude positives type "neg", to see only
positive type "pos", to see all press 'enter'] & "*"
-------------
What I need is to be able to find the clients that have
never had the test done.

I hope I am clear with the request.
Thank you in advanced.

Glenn
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top