A query problem

[Guest]

Hi everyone,

I have a database that keep records of vaccination for children. There are
two tables, tblMatser keeps the personal profile of children, tblVaccine
keeps the vaccination record such as date of vaccination, vaccine name &
vaccine dose number etc. The relation between tblMaster and tblVaccine is one
to many. I’d like to build a query to retrieve those children who have
received VaccineA dose 1 but not yet VaccineA dose 2. I know I have to
retrieve a population who have received VaccineA dose 1 first (this
population may include those who have both VaccineA dose 1 & dose 2), then
from this population I sort out those who haven’t received VaccineA dose 2.
Can anyone tell me how to do this? Thank you very much.

My query will be something like:
SELECT *
FROM tblMaster
WHERE tblMaster.ID IN (SELECT tblMaster.ID FROM tblMaster LEFT JOIN
tblDetail ON tblMaster.ID = tblDetail.MasterID WHERE tblDetail.Vacc =
"VaccineA" And tblDetail.VaccNth <>"2") ---???
(SELECT tblMaster.ID FROM tblMaster LEFT JOIN tblDetail ON tblMaster.ID =
tblDetail.MasterID WHERE tblMaster.BabyBirth Between
[Forms]![qryForm].[birthdate1] And [Forms]![qryForm].[birthdate2] AND
tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1");

 

[Jeff Boyce]

I believe the relationship you are describing requires (in most simplified
version) three tables:

tblPerson (your "Master" table)
PersonID
FirstName
LastName
DOB
...

tblVaccine (NOT the same as yours)
VaccineID
VaccineName
VaccineSequenceNumber (e.g., dose1)
...

trelVaccination
VaccinationID
PersonID
VaccineID
DateAdministered

Create a query that all PersonIDs in trelVaccination where VaccineID =
(whatever your VaccineA, dose 1 ID is). Create another query that uses the
results of the first query as a source, and finds all the PersonIDs in your
Query1 that are NOT in trelVaccination where VaccineID = (whatever your V-a,
dose 2 ID is).

The query wizard has a "unmatched" query as a pattern.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John Spencer]

Easiest way is to identify all those that have had the second shot and
then eliminate them

That can be done with a query like the following.

SELECT tblMaster.*
FROM tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND tblMaster.ID Not IN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2")


The problem is that Not In queries are S L O W with large recordsets.
You can try the following which should be faster (if it works)

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID)
LEFT JOIN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2") as Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND Has2.MasterID is Null



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Hi Jeff,

I made a wrong description when I posted the problem. I should have
mentioned my two tables as tblMaster and tblDetail and their relation is one
to many. I only need these two tables to retrieve the information I need this
time. I do have a third table in my database and it contains the information
of each vaccine, like vaccine name, manufacturer, product number etc. The
VaccineSequenceNumber you mentioned is in tblDetail in my database, because
one child may have several doses of one vaccine on different date. My
tblDetail holds PersonID, VaccineID, VaccineSequenceNumber, and
DateAdministered. Thank you very much for your help.

Jeff

--
Jeff


"Jeff Boyce" 來函：

I believe the relationship you are describing requires (in most simplified
version) three tables:

tblPerson (your "Master" table)
PersonID
FirstName
LastName
DOB
...

tblVaccine (NOT the same as yours)
VaccineID
VaccineName
VaccineSequenceNumber (e.g., dose1)
...

trelVaccination
VaccinationID
PersonID
VaccineID
DateAdministered

Create a query that all PersonIDs in trelVaccination where VaccineID =
(whatever your VaccineA, dose 1 ID is). Create another query that uses the
results of the first query as a source, and finds all the PersonIDs in your
Query1 that are NOT in trelVaccination where VaccineID = (whatever your V-a,
dose 2 ID is).

The query wizard has a "unmatched" query as a pattern.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi everyone,

I have a database that keep records of vaccination for children. There are
two tables, tblMatser keeps the personal profile of children, tblVaccine
keeps the vaccination record such as date of vaccination, vaccine name &
vaccine dose number etc. The relation between tblMaster and tblVaccine is one
to many. I’d like to build a query to retrieve those children who have
received VaccineA dose 1 but not yet VaccineA dose 2. I know I have to
retrieve a population who have received VaccineA dose 1 first (this
population may include those who have both VaccineA dose 1 & dose 2), then
from this population I sort out those who haven’t received VaccineA dose 2.
Can anyone tell me how to do this? Thank you very much.

My query will be something like:
SELECT *
FROM tblMaster
WHERE tblMaster.ID IN (SELECT tblMaster.ID FROM tblMaster LEFT JOIN
tblDetail ON tblMaster.ID = tblDetail.MasterID WHERE tblDetail.Vacc =
"VaccineA" And tblDetail.VaccNth <>"2") ---???
(SELECT tblMaster.ID FROM tblMaster LEFT JOIN tblDetail ON tblMaster.ID =
tblDetail.MasterID WHERE tblMaster.BabyBirth Between
[Forms]![qryForm].[birthdate1] And [Forms]![qryForm].[birthdate2] AND
tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1");

 

[Guest]

Hi John,

Thank you very much for your help. The first part of your answer is exactly
what I tried to write. I use tblDetail.MasterID in stead of tblMaster.ID in
your fifth row “AND tbleMaster.ID Not INâ€, and it works perfectly although in
a very slow speed. And I add “tblMaster.BabyBirth Between
[Forms]![qryForm].[BirthDate1] And [Forms]![qryForm].[BirthDate2]†to the
last line WHERE……., because the children I need to retrieve fall within a
range of age.

I tried the second part of your suggestion with the addition of the
parameters for a specific age range, but there seems to have error in the
code. I am still working hard to solve this problem and appreciated if you
can give me a hand. Thank you.

--
Jeff


"John Spencer" 來函：

Easiest way is to identify all those that have had the second shot and
then eliminate them

That can be done with a query like the following.

SELECT tblMaster.*
FROM tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND tblMaster.ID Not IN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2")


The problem is that Not In queries are S L O W with large recordsets.
You can try the following which should be faster (if it works)

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID)
LEFT JOIN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2") as Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND Has2.MasterID is Null



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi everyone,

I have a database that keep records of vaccination for children. There are
two tables, tblMatser keeps the personal profile of children, tblVaccine
keeps the vaccination record such as date of vaccination, vaccine name &
vaccine dose number etc. The relation between tblMaster and tblVaccine is one
to many. I’d like to build a query to retrieve those children who have
received VaccineA dose 1 but not yet VaccineA dose 2. I know I have to
retrieve a population who have received VaccineA dose 1 first (this
population may include those who have both VaccineA dose 1 & dose 2), then
from this population I sort out those who haven’t received VaccineA dose 2.
Can anyone tell me how to do this? Thank you very much.

My query will be something like:
SELECT *
FROM tblMaster
WHERE tblMaster.ID IN (SELECT tblMaster.ID FROM tblMaster LEFT JOIN
tblDetail ON tblMaster.ID = tblDetail.MasterID WHERE tblDetail.Vacc =
"VaccineA" And tblDetail.VaccNth <>"2") ---???
(SELECT tblMaster.ID FROM tblMaster LEFT JOIN tblDetail ON tblMaster.ID =
tblDetail.MasterID WHERE tblMaster.BabyBirth Between
[Forms]![qryForm].[birthdate1] And [Forms]![qryForm].[birthdate2] AND
tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1");

 

[Guest]

Hi John,

I just post a reply to you and find that I have another problem. What if I
also want to retrive the DateAdminstered of dose 1 of VaccineA? The field
DateAdministered is in tblDetail. Thank you.

Jeff


"John Spencer" 來函：

Easiest way is to identify all those that have had the second shot and
then eliminate them

That can be done with a query like the following.

SELECT tblMaster.*
FROM tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND tblMaster.ID Not IN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2")


The problem is that Not In queries are S L O W with large recordsets.
You can try the following which should be faster (if it works)

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID)
LEFT JOIN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2") as Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND Has2.MasterID is Null



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi everyone,

I have a database that keep records of vaccination for children. There are
two tables, tblMatser keeps the personal profile of children, tblVaccine
keeps the vaccination record such as date of vaccination, vaccine name &
vaccine dose number etc. The relation between tblMaster and tblVaccine is one
to many. I’d like to build a query to retrieve those children who have
received VaccineA dose 1 but not yet VaccineA dose 2. I know I have to
retrieve a population who have received VaccineA dose 1 first (this
population may include those who have both VaccineA dose 1 & dose 2), then
from this population I sort out those who haven’t received VaccineA dose 2.
Can anyone tell me how to do this? Thank you very much.

My query will be something like:
SELECT *
FROM tblMaster
WHERE tblMaster.ID IN (SELECT tblMaster.ID FROM tblMaster LEFT JOIN
tblDetail ON tblMaster.ID = tblDetail.MasterID WHERE tblDetail.Vacc =
"VaccineA" And tblDetail.VaccNth <>"2") ---???
(SELECT tblMaster.ID FROM tblMaster LEFT JOIN tblDetail ON tblMaster.ID =
tblDetail.MasterID WHERE tblMaster.BabyBirth Between
[Forms]![qryForm].[birthdate1] And [Forms]![qryForm].[birthdate2] AND
tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1");

 

[John Spencer]

Can you post the SQL of the query you are trying to run?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

Thank you very much for your help. The first part of your answer is
exactly
what I tried to write. I use tblDetail.MasterID in stead of tblMaster.ID
in
your fifth row "AND tbleMaster.ID Not IN", and it works perfectly although
in
a very slow speed. And I add "tblMaster.BabyBirth Between
[Forms]![qryForm].[BirthDate1] And [Forms]![qryForm].[BirthDate2]" to the
last line WHERE..., because the children I need to retrieve fall within a
range of age.

I tried the second part of your suggestion with the addition of the
parameters for a specific age range, but there seems to have error in the
code. I am still working hard to solve this problem and appreciated if you
can give me a hand. Thank you.

--
Jeff


"John Spencer" ??:

Easiest way is to identify all those that have had the second shot and
then eliminate them

That can be done with a query like the following.

SELECT tblMaster.*
FROM tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND tblMaster.ID Not IN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2")


The problem is that Not In queries are S L O W with large recordsets.
You can try the following which should be faster (if it works)

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID)
LEFT JOIN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2") as Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND Has2.MasterID is Null



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi everyone,

I have a database that keep records of vaccination for children. There
are
two tables, tblMatser keeps the personal profile of children,
tblVaccine
keeps the vaccination record such as date of vaccination, vaccine name
&
vaccine dose number etc. The relation between tblMaster and tblVaccine
is one
to many. I'd like to build a query to retrieve those children who have
received VaccineA dose 1 but not yet VaccineA dose 2. I know I have to
retrieve a population who have received VaccineA dose 1 first (this
population may include those who have both VaccineA dose 1 & dose 2),
then
from this population I sort out those who haven't received VaccineA
dose 2.
Can anyone tell me how to do this? Thank you very much.

My query will be something like:
SELECT *
FROM tblMaster
WHERE tblMaster.ID IN (SELECT tblMaster.ID FROM tblMaster LEFT JOIN
tblDetail ON tblMaster.ID = tblDetail.MasterID WHERE tblDetail.Vacc =
"VaccineA" And tblDetail.VaccNth <>"2") ---????
(SELECT tblMaster.ID FROM tblMaster LEFT JOIN tblDetail ON tblMaster.ID
=
tblDetail.MasterID WHERE tblMaster.BabyBirth Between
[Forms]![qryForm].[birthdate1] And [Forms]![qryForm].[birthdate2] AND
tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1");

 

[Guest]

Hi John,

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN
(SELECT tblDetail.MasterID FROM tblDetail WHERE tblDetail.Vacc = "VaccineA"
And tblDetail.VaccNth = "2") AS Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1"
AND Has2.MasterID is Null;

Your original code as above works well and in high speed. When I add
parameters for entry of a specific age range as shown below, there seems to
have error in the code. Please note that Access automatically change the “()â€
to “[]†and add “.†Before AS Has2.

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN
[SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblMaster.BabyBirth Between Forms!qryForm.Date1 And
Forms!qryForm.Date2 And tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth =
"2"]. AS Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1" And
Has2.MasterID is Null;

I try to retrieve the field DateAdministered using the first part of your
code which works well before. The field DateAdministered is included in my
tblDetail, the code shown below doesn’t work then.

SELECT tblDetail.DateAdministered, tblMaster.*
FROM tblDetail, tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID
WHERE tblMaster.BabyBirth Between [Forms]![qryForm].[Date1] And
[Forms]![qryForm].[Date2] AND tblDetail.Vacc = "VaccineA" AND
tblDetail.VaccNth ="1"
AND tblDetail.MasterID Not In
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "2");.

Thank you very much.

--
Jeff


"John Spencer" 來函：

Can you post the SQL of the query you are trying to run?

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

Thank you very much for your help. The first part of your answer is
exactly
what I tried to write. I use tblDetail.MasterID in stead of tblMaster.ID
in
your fifth row "AND tbleMaster.ID Not IN", and it works perfectly although
in
a very slow speed. And I add "tblMaster.BabyBirth Between
[Forms]![qryForm].[BirthDate1] And [Forms]![qryForm].[BirthDate2]" to the
last line WHERE..., because the children I need to retrieve fall within a
range of age.

I tried the second part of your suggestion with the addition of the
parameters for a specific age range, but there seems to have error in the
code. I am still working hard to solve this problem and appreciated if you
can give me a hand. Thank you.

--
Jeff


"John Spencer" ??:

Easiest way is to identify all those that have had the second shot and
then eliminate them

That can be done with a query like the following.

SELECT tblMaster.*
FROM tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND tblMaster.ID Not IN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2")


The problem is that Not In queries are S L O W with large recordsets.
You can try the following which should be faster (if it works)

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID = TblDetail.MasterID)
LEFT JOIN
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE Vacc = "VaccineA" and VaccNth = "2") as Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" and VaccNth = "1"
AND Has2.MasterID is Null



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Jeff wrote:
Hi everyone,

I have a database that keep records of vaccination for children. There
are
two tables, tblMatser keeps the personal profile of children,
tblVaccine
keeps the vaccination record such as date of vaccination, vaccine name
&
vaccine dose number etc. The relation between tblMaster and tblVaccine
is one
to many. I'd like to build a query to retrieve those children who have
received VaccineA dose 1 but not yet VaccineA dose 2. I know I have to
retrieve a population who have received VaccineA dose 1 first (this
population may include those who have both VaccineA dose 1 & dose 2),
then
from this population I sort out those who haven't received VaccineA
dose 2.
Can anyone tell me how to do this? Thank you very much.

My query will be something like:
SELECT *
FROM tblMaster
WHERE tblMaster.ID IN (SELECT tblMaster.ID FROM tblMaster LEFT JOIN
tblDetail ON tblMaster.ID = tblDetail.MasterID WHERE tblDetail.Vacc =
"VaccineA" And tblDetail.VaccNth <>"2") ---????
(SELECT tblMaster.ID FROM tblMaster LEFT JOIN tblDetail ON tblMaster.ID
=
tblDetail.MasterID WHERE tblMaster.BabyBirth Between
[Forms]![qryForm].[birthdate1] And [Forms]![qryForm].[birthdate2] AND
tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1");

 

[John Spencer]

It appears that you may have put the WHERE clause in the wrong place. I
think the following may work for you.

SELECT tblMaster.*, tblDetail.DateAdministered
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN
[SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblDetail.Vacc = "VaccineA"
And tblDetail.VaccNth = "2"]. AS Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1"
And Has2.MasterID is Null
And tblMaster.BabyBirth Between Forms!qryForm.Date1 And
Forms!qryForm!Date2

The addition of the square brackets and the period is Access method of
handling a subquery in the from clause of a query. It causes problems if
you need to use square brackets in the subquery.

Another way to handle this situation is to use nested queries. That is
build the subquery as a standalone query and save it. Then use the subquery
as if it were a table in another query. The advantage of a stacked query is
that you can use square brackets and parameters in the query that you will
be using.

In the above situation
Build a query as follows and save it as Has2
SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblDetail.Vacc = "VaccineA"
And tblDetail.VaccNth = "2"

Then use that to build the needed query

SELECT tblMaster.*, tblDetail.DateAdministered
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1"
And Has2.MasterID is Null
And tblMaster.BabyBirth Between Forms!qryForm.Date1 And
Forms!qryForm!Date2

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN
(SELECT tblDetail.MasterID FROM tblDetail WHERE tblDetail.Vacc =
"VaccineA"
And tblDetail.VaccNth = "2") AS Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1"
AND Has2.MasterID is Null;

Your original code as above works well and in high speed. When I add
parameters for entry of a specific age range as shown below, there seems
to
have error in the code. Please note that Access automatically change the
"()"
to "[]" and add "." Before AS Has2.

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN
[SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblMaster.BabyBirth Between Forms!qryForm.Date1 And
Forms!qryForm.Date2 And tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth
=
"2"]. AS Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1" And
Has2.MasterID is Null;

I try to retrieve the field DateAdministered using the first part of your
code which works well before. The field DateAdministered is included in my
tblDetail, the code shown below doesn't work then.

SELECT tblDetail.DateAdministered, tblMaster.*
FROM tblDetail, tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID
WHERE tblMaster.BabyBirth Between [Forms]![qryForm].[Date1] And
[Forms]![qryForm].[Date2] AND tblDetail.Vacc = "VaccineA" AND
tblDetail.VaccNth ="1"
AND tblDetail.MasterID Not In
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "2");.

Thank you very much.

 

[Guest]

Thank you very much, John. It works perfectly.
--
Jeff


"John Spencer" 來函：

It appears that you may have put the WHERE clause in the wrong place. I
think the following may work for you.

SELECT tblMaster.*, tblDetail.DateAdministered
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN
[SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblDetail.Vacc = "VaccineA"
And tblDetail.VaccNth = "2"]. AS Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1"
And Has2.MasterID is Null
And tblMaster.BabyBirth Between Forms!qryForm.Date1 And
Forms!qryForm!Date2

The addition of the square brackets and the period is Access method of
handling a subquery in the from clause of a query. It causes problems if
you need to use square brackets in the subquery.

Another way to handle this situation is to use nested queries. That is
build the subquery as a standalone query and save it. Then use the subquery
as if it were a table in another query. The advantage of a stacked query is
that you can use square brackets and parameters in the query that you will
be using.

In the above situation
Build a query as follows and save it as Has2
SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblDetail.Vacc = "VaccineA"
And tblDetail.VaccNth = "2"

Then use that to build the needed query

SELECT tblMaster.*, tblDetail.DateAdministered
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1"
And Has2.MasterID is Null
And tblMaster.BabyBirth Between Forms!qryForm.Date1 And
Forms!qryForm!Date2

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN
(SELECT tblDetail.MasterID FROM tblDetail WHERE tblDetail.Vacc =
"VaccineA"
And tblDetail.VaccNth = "2") AS Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1"
AND Has2.MasterID is Null;

Your original code as above works well and in high speed. When I add
parameters for entry of a specific age range as shown below, there seems
to
have error in the code. Please note that Access automatically change the
"()"
to "[]" and add "." Before AS Has2.

SELECT tblMaster.*
FROM (tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID)
LEFT JOIN
[SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblMaster.BabyBirth Between Forms!qryForm.Date1 And
Forms!qryForm.Date2 And tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth
=
"2"]. AS Has2
ON tblMaster.ID = Has2.MasterID
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "1" And
Has2.MasterID is Null;

I try to retrieve the field DateAdministered using the first part of your
code which works well before. The field DateAdministered is included in my
tblDetail, the code shown below doesn't work then.

SELECT tblDetail.DateAdministered, tblMaster.*
FROM tblDetail, tblMaster INNER JOIN tblDetail
ON tblMaster.ID=tblDetail.MasterID
WHERE tblMaster.BabyBirth Between [Forms]![qryForm].[Date1] And
[Forms]![qryForm].[Date2] AND tblDetail.Vacc = "VaccineA" AND
tblDetail.VaccNth ="1"
AND tblDetail.MasterID Not In
(SELECT tblDetail.MasterID
FROM tblDetail
WHERE tblDetail.Vacc = "VaccineA" And tblDetail.VaccNth = "2");.

Thank you very much.