Prev: Time Calculation
Next: Running a stored parameter query from a form without pop up boxes.
From: Jeff on 30 Mar 2010 07:38
Hi Stefan,

Access still pops up and requests me to eneter MaxInjectionDate.InjectionDate.
--
Jeff


"Stefan Hoffmann" 來函：

> 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 <--
> .
>
From: Stefan Hoffmann on 30 Mar 2010 08:00
hi Jeff,

On 30.03.2010 13:38, Jeff wrote:
> Access still pops up and requests me to eneter MaxInjectionDate.InjectionDate.
It must be a typo. Check the spelling of your field and table names
carefully.


mfG
--> stefan <--
From: Jeff on 1 Apr 2010 08:29
Hi Stefan,
Yes, it's a typo. I finally get it work as follow:

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

Thank you so much.
--
Jeff


"Stefan Hoffmann" 來函：

> hi Jeff,
>
> On 30.03.2010 13:38, Jeff wrote:
> > Access still pops up and requests me to eneter MaxInjectionDate.InjectionDate.
> It must be a typo. Check the spelling of your field and table names
> carefully.
>
>
> mfG
> --> stefan <--
> .
>
From: Jeff on 1 Apr 2010 08:30
Thank you, John.
--
Jeff


"Jeff" 來函：

> 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.
> > .
> >
First  |  Prev  | 
Pages: 1 2 3
Prev: Time Calculation
Next: Running a stored parameter query from a form without pop up boxes.