Query problem

[Guest]

Hi everyone,

I have 2 tables that are one to many in relation. The first table stores the
personal data like name, date of birth, sex etc. of the babies. The second
table stores the vaccine injection records like vaccine name, injection date
etc. for those babies. Each baby may have none, one or more records of
injection. I'd like to build a query to retrieve those babies who are older
than a specific age and has NOT been given a specific vaccine after a
specific date. I put the following criteria in my query:

date of birth > #specific date# AND injection date > #specifc date# AND
vaccine name <> “Flu vaccineâ€

OR

date of birth > #specific date# AND injection date Is Null

With these criteria I can retrieve the babies who has not been given Flu
vaccine after a specific date and the babies who do not have any record of
vaccination. However, those babies with only vaccinated records BEFORE that
specific date, no matter what the vaccine is, can not be retrieved. Does
anyone have good idea to help me make up this part? Thank you.

 

[Brian]

Hi everyone,

I have 2 tables that are one to many in relation. The first table stores the
personal data like name, date of birth, sex etc. of the babies. The second
table stores the vaccine injection records like vaccine name, injection date
etc. for those babies. Each baby may have none, one or more records of
injection. I'd like to build a query to retrieve those babies who are older
than a specific age and has NOT been given a specific vaccine after a
specific date. I put the following criteria in my query:

date of birth > #specific date# AND injection date > #specifc date# AND
vaccine name <> "Flu vaccine"

OR

date of birth > #specific date# AND injection date Is Null

With these criteria I can retrieve the babies who has not been given Flu
vaccine after a specific date and the babies who do not have any record of
vaccination. However, those babies with only vaccinated records BEFORE that
specific date, no matter what the vaccine is, can not be retrieved. Does
anyone have good idea to help me make up this part? Thank you.

You can't do it just by modifying your criteria, you need to use an
appropriate join as well. Something like this:

SELECT something FROM personal_data PD LEFT JOIN vaccinations V ON
(PD.personal_id = V.personal_ID AND V.vaccine_name = "Flu vaccine" AND
V.injection_date > #specific_date#) WHERE PD.[date_of_birth] >
#specific_date#
AND V.personal_ID IS NULL

 

[John Spencer (MVP)]

The easiest way to do this is to find all the babies that have been given the
injection and then eliminate them from the list of babies. Easiest implementation

SELECT Babies.BabyId
FROM Babies INNER JOIN Injections
WHERE Babies.DOB < #SomeDate#
AND InjectionDate > #SomeOtherDate#
AND VaccineName = "Flu"

Save that as queryA

Then do an unmatched query on Babies table and QueryA

SELECT Babies.*
FROM Babies LEFT JOIN queryA
ON Babies.BabyID = queryA.Babyid
WHERE QueryA.BabyID is Null

That can all be done in one query, although it will be slower

SELECT Babies.*
FROM Babies
WHERE Babies.BabyId NOT IN
(SELECT Babies.BabyId
FROM Babies INNER JOIN Injections
WHERE Babies.DOB < #SomeDate#
AND InjectionDate > #SomeOtherDate#
AND VaccineName = "Flu")

 

[Brian]

The easiest way to do this is to find all the babies that have been given the
injection and then eliminate them from the list of babies. Easiest implementation

SELECT Babies.BabyId
FROM Babies INNER JOIN Injections
WHERE Babies.DOB < #SomeDate#
AND InjectionDate > #SomeOtherDate#
AND VaccineName = "Flu"

Save that as queryA

Then do an unmatched query on Babies table and QueryA

SELECT Babies.*
FROM Babies LEFT JOIN queryA
ON Babies.BabyID = queryA.Babyid
WHERE QueryA.BabyID is Null

That can all be done in one query, although it will be slower

SELECT Babies.*
FROM Babies
WHERE Babies.BabyId NOT IN
(SELECT Babies.BabyId
FROM Babies INNER JOIN Injections
WHERE Babies.DOB < #SomeDate#
AND InjectionDate > #SomeOtherDate#
AND VaccineName = "Flu")

Using a more sophisticated join, as I suggested, needs only one query, and
is infinitely quicker than the dreaded correlated sub-query.

 

[John Spencer (MVP)]

Brian,

Nice solution. I didn't see it when I responded.

I would agree with your premise without reservation, but sometimes in Access the
more sophisticated join you proposed seems to have problems. I'm not saying it
won't work; I am saying I haven't tested it for this situation. And I do agree
that the NOT IN subquery is slow.

I usually find in Access with Jet (not MS SQL or Oracle) that the two-step query
approach is faster in these cases. Oh well, the idea of these groups is to give
advice and in this case I would say the original poster received several methods
to solve the problem.

 

[Brian]

Brian,

Nice solution. I didn't see it when I responded.

I would agree with your premise without reservation, but sometimes in Access the
more sophisticated join you proposed seems to have problems. I'm not saying it
won't work; I am saying I haven't tested it for this situation. And I do agree
that the NOT IN subquery is slow.

I usually find in Access with Jet (not MS SQL or Oracle) that the two-step query
approach is faster in these cases. Oh well, the idea of these groups is to give
advice and in this case I would say the original poster received several methods
to solve the problem.

Hi John,

I came across this join technique when working with SQL Server, and I have
since used it extensively without problem. The main thing to beware of is
that the join expression MUST be enclosed in parentheses - it will not work
otherwise (unlike in SQL Server, which is much less fussy about parentheses
throughout the FROM clause).

Correlated subqueries are awful in Access, performance-wise. Interestingly,
FWIW, they work just fine in SQL Server.

Brian

 

[Guest]

Dear Brian & John,

Thank you very much for your suggestions and discussion on my question. I
use John's second method and test. I note that the babies who are younger
than #SomeDate# and have not any record of injection can not be eliminated.
Therefore, I modify the criteria by adding

OR Babies.DOB > #SomeDate# to the end of the criteria

Then the problem is solved. I am still testing with my database to see if
there is other problem. Thank you.

Jeff