Prev: Time Calculation
Next: Running a stored parameter query from a form without pop up boxes.
From: Stefan Hoffmann on 28 Mar 2010 06:54
hi Jeff,

On 28.03.2010 01:01, Jeff wrote:
> It works, but the number of records it returns are many more than the number
> of records as using the first query. Access doesn't GROUP BY
> tbVaccineInjection.BabyID with Max(tbVaccineInjection.InjectionDate), becuase
> it still returns several record for each baby.
You have included tbVaccineInjection, which is the cause. This should be
sufficient:

SELECT B.*, MVI.InjectionDate
FROM tbBaby B
INNER JOIN
(
SELECT VI.BabyID, Max(VI.InjectionDate)
FROM tbVaccineInjection VI
GROUP BY VI.BabyID
) AS MVI
ON B.ID = MVI.BabyID
WHERE B.BabyBirth BETWEEN Me!StartDate AND Me!EndDate;


mfG
--> stefan <--
From: John Spencer on 28 Mar 2010 12:07
The query should look more like the following. You join tbVaccineInjection to
the results from the subquery on both BabyID and the Injectiondate. That
limits the records returned for tbVaccineInjection to just those that match
the babyid and the last injectionDate for each babyid.

SELECT tbBaby.*
, tbVaccineInjection.InjectionDate
FROM (tbBaby INNER JOIN tbVaccineInjection
ON tbBaby.ID = tbVaccineInjection.BabyID)
INNER JOIN
[SELECT tbVaccineInjection.BabyID,
Max(tbVaccineInjection.InjectionDate) as LastInjected
FROM tbVaccineInjection
GROUP BY tbVaccineInjection.BabyID]. AS MaxInjectionDate
ON tbVaccineInjection.BabyID = MaxInjectionDate.BabyID
AND tbVaccineInjection = MaxInjectionDate.LastInjected
WHERE tbBaby.BabyBirth Between Me!StartDate And Me!EndDate;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Jeff wrote:
> My query returns BabyID and InjectionDate, but I want it returns babies'
> personal data and InjectionDate. I rewrite it as :
>
> SELECT tbBaby.*, tbVaccineInjection.InjectionDate
> FROM (tbBaby INNER JOIN tbVaccineInjection ON tbBaby.ID =
> tbVaccineInjection.BabyID) INNER JOIN [SELECT tbVaccineInjection.BabyID,
> Max(tbVaccineInjection.InjectionDate) FROM tbVaccineInjection GROUP BY
> tbVaccineInjection.BabyID]. AS MaxInjectionDate ON tbBaby.ID =
> MaxInjectionDate.BabyID
> WHERE tbBaby.BabyBirth Between Me!StartDate And Me!EndDate;
>
> It works, but the number of records it returns are many more than the number
> of records as using the first query. Access doesn't GROUP BY
> tbVaccineInjection.BabyID with Max(tbVaccineInjection.InjectionDate), becuase
> it still returns several record for each baby.
>
> I really get very confused with INNER JOIN, LEFT JOIN & RIGHT JOIN. Thank
> you.
From: Jeff on 29 Mar 2010 20:10
Hi Stefan,

I rewrite your query in a complete form as:
SELECT tbBaby.*, MaxInjectionDate.InjectionDate
FROM tbBaby
INNER JOIN [SELECT tbVaccineInjection.BabyID,
Max(tbVaccineInjection.InjectionDate)
FROM tbVaccineInjection
GROUP BY tbVaccineInjection.BabyID]. AS MaxInjectionDate
ON tbBaby.ID = MaxInjectionDate.BabyID
WHERE tbBaby.BabyBirth BETWEEN Me!StartDate AND Me!EndDate;

It works and returns the same number of records as using the original simple
one:

SELECT tbVaccineInjection.BabyID, Max(tbVaccineInjection.InjectionDate)
FROM tbBaby
INNER JOIN tbVaccineInjection ON tbBaby.ID = tbVaccineInjection.BabyID
WHERE tbBaby.BabyBirth Between Me!StartDate And Me!EndDate
GROUP BY tbVaccineInjection.BabyID;

But Access pop up and request me to eneter MaxInjectionDate, we must have
missed something.
--
Jeff


"Stefan Hoffmann" 來函：

> hi Jeff,
>
> On 28.03.2010 01:01, Jeff wrote:
> > It works, but the number of records it returns are many more than the number
> > of records as using the first query. Access doesn't GROUP BY
> > tbVaccineInjection.BabyID with Max(tbVaccineInjection.InjectionDate), becuase
> > it still returns several record for each baby.
> You have included tbVaccineInjection, which is the cause. This should be
> sufficient:
>
> SELECT B.*, MVI.InjectionDate
> FROM tbBaby B
> INNER JOIN
> (
> SELECT VI.BabyID, Max(VI.InjectionDate)
> FROM tbVaccineInjection VI
> GROUP BY VI.BabyID
> ) AS MVI
> ON B.ID = MVI.BabyID
> WHERE B.BabyBirth BETWEEN Me!StartDate AND Me!EndDate;
>
>
> mfG
> --> stefan <--
> .
>
From: Jeff on 29 Mar 2010 20:25
Hi John,

Your query works after I correct tbVaccineInjection to
tbVaccineInjection.InjectionDate in line 11, but Access returns more records
as using the original simple one:

SELECT tbVaccineInjection.BabyID, Max(tbVaccineInjection.InjectionDate)
FROM tbBaby
INNER JOIN tbVaccineInjection ON tbBaby.ID = tbVaccineInjection.BabyID
WHERE tbBaby.BabyBirth Between Me!StartDate And Me!EndDate
GROUP BY tbVaccineInjection.BabyID;

I have to checked into it.

--
Jeff


"John Spencer" 來函：

> The query should look more like the following. You join tbVaccineInjection to
> the results from the subquery on both BabyID and the Injectiondate. That
> limits the records returned for tbVaccineInjection to just those that match
> the babyid and the last injectionDate for each babyid.
>
> SELECT tbBaby.*
> , tbVaccineInjection.InjectionDate
> FROM (tbBaby INNER JOIN tbVaccineInjection
> ON tbBaby.ID = tbVaccineInjection.BabyID)
> INNER JOIN
> [SELECT tbVaccineInjection.BabyID,
> Max(tbVaccineInjection.InjectionDate) as LastInjected
> FROM tbVaccineInjection
> GROUP BY tbVaccineInjection.BabyID]. AS MaxInjectionDate
> ON tbVaccineInjection.BabyID = MaxInjectionDate.BabyID
> AND tbVaccineInjection = MaxInjectionDate.LastInjected
> WHERE tbBaby.BabyBirth Between Me!StartDate And Me!EndDate;
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Jeff wrote:
> > My query returns BabyID and InjectionDate, but I want it returns babies'
> > personal data and InjectionDate. I rewrite it as :
> >
> > SELECT tbBaby.*, tbVaccineInjection.InjectionDate
> > FROM (tbBaby INNER JOIN tbVaccineInjection ON tbBaby.ID =
> > tbVaccineInjection.BabyID) INNER JOIN [SELECT tbVaccineInjection.BabyID,
> > Max(tbVaccineInjection.InjectionDate) FROM tbVaccineInjection GROUP BY
> > tbVaccineInjection.BabyID]. AS MaxInjectionDate ON tbBaby.ID =
> > MaxInjectionDate.BabyID
> > WHERE tbBaby.BabyBirth Between Me!StartDate And Me!EndDate;
> >
> > It works, but the number of records it returns are many more than the number
> > of records as using the first query. Access doesn't GROUP BY
> > tbVaccineInjection.BabyID with Max(tbVaccineInjection.InjectionDate), becuase
> > it still returns several record for each baby.
> >
> > I really get very confused with INNER JOIN, LEFT JOIN & RIGHT JOIN. Thank
> > you.
> .
>
From: Stefan Hoffmann on 30 Mar 2010 05:56
hi Jeff,

On 30.03.2010 02:10, Jeff wrote:
> But Access pop up and request me to eneter MaxInjectionDate, we must have
> missed something.
I think the problem is the missing alias name:

SELECT B.*, MVI.LastInjectionDate
FROM tbBaby B
INNER JOIN
(
SELECT VI.BabyID, Max(VI.InjectionDate) AS LastInjectionDate
FROM tbVaccineInjection VI
GROUP BY VI.BabyID
) AS MVI
ON B.ID = MVI.BabyID
WHERE B.BabyBirth BETWEEN Me!StartDate AND Me!EndDate;


mfG
--> stefan <--
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Time Calculation
Next: Running a stored parameter query from a form without pop up boxes.