Prev: Time Calculation
Next: Running a stored parameter query from a form without pop up boxes.
From: Jeff on 27 Mar 2010 08:07
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.
--
Jeff
From: Stefan Hoffmann on 27 Mar 2010 08:26
hi Jeff,

On 27.03.2010 13:07, Jeff wrote:
> 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 <--
From: Jeff on 27 Mar 2010 11:05
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.
--
Jeff


"Stefan Hoffmann" 來函：

> hi Jeff,
>
> On 27.03.2010 13:07, Jeff wrote:
> > 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 <--
> .
>
From: Stefan Hoffmann on 27 Mar 2010 11:27
hi Jeff,

On 27.03.2010 16:05, Jeff wrote:
> 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 <--
From: Jeff on 27 Mar 2010 20:01
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.
--
Jeff


"Stefan Hoffmann" 來函：

> hi Jeff,
>
> On 27.03.2010 16:05, Jeff wrote:
> > 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 <--
> .
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Time Calculation
Next: Running a stored parameter query from a form without pop up boxes.