Query Problem

[Jeff]

Hi,
I have 2 tables, tbBaby and tbVaccineInjection. tbBaby stores the babies'
personal data like Name, BirthDate etc., tbVaccine stores VaccineName,
DoseNumber (1st, 2nd, 3rd, etc.) and InjectionDate, etc. These 2 tables are
one to many in relation, because each baby may have several vaccinations on
different dates.

I'd like to build a query to retrieve babies with their most recent
injection date, i.e. only one record for each baby with latest injection date.

my query is as follow
SELECT tbBaby.*, tbVaccine.*
FROM tbBaby INNER JOIN tbVaccine ON tbBaby.ID=tbVaccine.BabyID
WHERE tbBaby.BirthDate Between Me!StartDate And Me!EndDate
ORDER BY tbVaccine.InjectDate DESC;

With this query, each baby may have more than one record. I tried to use
DISTINCT or DISTINCTROW, but still unable to get it work. Your help will be
apprecited.

 

[Stefan Hoffmann]

hi Jeff,

I have 2 tables, tbBaby and tbVaccineInjection. tbBaby stores the babies'
personal data like Name, BirthDate etc., tbVaccine stores VaccineName,
DoseNumber (1st, 2nd, 3rd, etc.) and InjectionDate, etc. These 2 tables are
one to many in relation, because each baby may have several vaccinations on
different dates.

I think your mixing two things into your tbVaccine:

1) the vaccine itself

and

2) the application/medication of it.

So I would store in tbVaccine onle the name.
Use a third table tbMedication for the InjectionDate:

tbMedication:
ID AutoNumber,
idBaby,
idVaccine,
InjectionDate,
DoseNumber

All fields must be not null. btw, is the dose number not calculable from
the InjectionDate? If so, you don't need to store it.

I'd like to build a query to retrieve babies with their most recent
injection date, i.e. only one record for each baby with latest injection date.

To get the latest date you simply need an aggregate query on your table
storing the injection date, in your case this should work:

SELECT idBaby, MAX(InjectionDate)
FROM tbVaccine
GROUP BY idBaby;


mfG
--> stefan <--

 

[Jeff]

Hi Stefan,

I am sorry I use tbVaccine in stead of tbVaccineInjection in the 2nd line of
my last post. I do have tbVaccine that stores the names, company, lot number
for each vaccine. My tbVaccineInjection is like your tbMedication that stores
BabyID, VaccineName (I need to change this to VaccineID), DoseNo,
InjectionDate...., I also have a tbBaby that stores babies' personal data
like ChartNo, BabyName, BirthDate, Address etc.

I'd like to build a query to retrieve babies' personal date with their most
recent injection date, i.e. only one record for each baby with latest
injection date. My codes are as follow:

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;

This will retrieve babies' BabyID from tbVaccineInjection who were born
within a period of days. Can you help me to retreive those babies' personal
data from tbBaby? Thank you.

 

[Stefan Hoffmann]

hi Jeff,

This will retrieve babies' BabyID from tbVaccineInjection who were born
within a period of days. Can you help me to retreive those babies' personal
data from tbBaby? Thank you.

Create a query using the simple aggregat query and use this stored query
to retrieve your data.

btw, what does your query returns? It looks quite well.

mfG
--> stefan <--

 

[Jeff]

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.

 

[Stefan Hoffmann]

hi Jeff,

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 <--

 

[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]

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]

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

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.

.

 

[Stefan Hoffmann]

hi Jeff,

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 <--

 

[Jeff]

Hi Stefan,

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

 

[Stefan Hoffmann]

hi Jeff,

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 <--

 

[Jeff]

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]

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

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.

.