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