Most Recent 3

[HotRodSue]

I have weights for trucks recorded by date. For each TruckID I need to
average the 3 most recent weights. So, I believe for each truck I need to
query the most recent 3 by date, then sum the most recent 3, and then average
the most recent 3. Just not sure how to accomplish that.

My table:

TruckTareID (Primary Key)
TruckID
TareDate
TareWeight

Since the average weight changes over time, can this be accomplished with
running totals? For instance, I need average weight as of August 1st, and
the average weight as of August 5th, etc.

Any suggestions are appreciated.

 

[HotRodSue]

Thank you for your response. I'm somewhat new, this is my 2nd database
project. I've found forums to be a nice addition to my book and DVD
tutorials. That said, would you please give a little more detail regarding
your answer? This is probably not difficult, however, I immediately see 2
things I don't understand.

1) Build a function with "input argument" of the TruckID.
2) "In a loop" sum the first three records.

Many thanks for your time.

 

[Steve Sanford]

PMFJI,

You should be able to this with a query/subquery. Maybe this page will help:

http://allenbrowne.com/subquery-01.html#TopN

or (on the same page)

http://allenbrowne.com/subquery-01.html#Aggregation


HTH

 

[John Spencer]

Try a query that looks like the following.

SELECT TruckID
, Avg(TareWeight) as AvgWeight
, Min(TareDate) as StartDate
, Max(TareDate) as EndDate
FROM [YourTable]
WHERE TareDate =
(SELECT TOP 3 TareDate
FROM [YourTable] as Temp
WHERE Temp.TruckID = [YourTable].TruckID
ORDER BY Temp.TareDate Desc)
GROUP BY TruckId

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

 

[HotRodSue]

Thank you for posting an example. In my initial question I didn't note my
table names and did not give exact Field Name for those tables. Within your
code I tried replacing some of the code with my table and field names.
However, still unable to get it. I apologize for not posting proper
information. Here is my exact table info:

tblTrucks (Table Name)
pkTruckID (Data Type AutoNumber)
txtTruckNo (Data Type Text)

tblTruckTare (Table Name)
pkTruckTareID (Data Type Autonumber)
fkTruckID (Data Type Number - foreign key tbl Trucks)
TareDate (Data Type Date/Time)
TareWeight (Data Type Number)

Thank you for your assitance. It is very much appreciated.

Try a query that looks like the following.

SELECT TruckID
, Avg(TareWeight) as AvgWeight
, Min(TareDate) as StartDate
, Max(TareDate) as EndDate
FROM [YourTable]
WHERE TareDate =
(SELECT TOP 3 TareDate
FROM [YourTable] as Temp
WHERE Temp.TruckID = [YourTable].TruckID
ORDER BY Temp.TareDate Desc)
GROUP BY TruckId

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

I have weights for trucks recorded by date. For each TruckID I need to
average the 3 most recent weights. So, I believe for each truck I need to
query the most recent 3 by date, then sum the most recent 3, and then average
the most recent 3. Just not sure how to accomplish that.

My table:

TruckTareID (Primary Key)
TruckID
TareDate
TareWeight

Since the average weight changes over time, can this be accomplished with
running totals? For instance, I need average weight as of August 1st, and
the average weight as of August 5th, etc.

Any suggestions are appreciated.

 

[John Spencer]

Try the following:

SELECT tblTrucks.pkTruckID
, tblTrucks.txtTruckNo
, Avg(TareWeight) as AverageWeightForPeriod
, Min(TareDate) as PeriodStart
, Max(TareDate) as PeriodEnd
FROM TblTrucks INNER JOIN TblTruckTare
ON tblTrucks.PKTruckID = tblTruckTare.fkTruckID
WHERE tblTruckTare.TareDate in
(SELECT Top 3 Temp.TareDate
FROM tblTruckTare as Temp
WHERE Temp.fkTruckID = tblTruckTare.fkTruckID
ORDER BY Temp.TareDate DESC)
GROUP tblTrucks.pkTruckID, tblTrucks.txtTruckNo

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

Thank you for posting an example. In my initial question I didn't note my
table names and did not give exact Field Name for those tables. Within your
code I tried replacing some of the code with my table and field names.
However, still unable to get it. I apologize for not posting proper
information. Here is my exact table info:

tblTrucks (Table Name)
pkTruckID (Data Type AutoNumber)
txtTruckNo (Data Type Text)

tblTruckTare (Table Name)
pkTruckTareID (Data Type Autonumber)
fkTruckID (Data Type Number - foreign key tbl Trucks)
TareDate (Data Type Date/Time)
TareWeight (Data Type Number)

Thank you for your assitance. It is very much appreciated.

Try a query that looks like the following.

SELECT TruckID
, Avg(TareWeight) as AvgWeight
, Min(TareDate) as StartDate
, Max(TareDate) as EndDate
FROM [YourTable]
WHERE TareDate =
(SELECT TOP 3 TareDate
FROM [YourTable] as Temp
WHERE Temp.TruckID = [YourTable].TruckID
ORDER BY Temp.TareDate Desc)
GROUP BY TruckId

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

I have weights for trucks recorded by date. For each TruckID I need to
average the 3 most recent weights. So, I believe for each truck I need to
query the most recent 3 by date, then sum the most recent 3, and then average
the most recent 3. Just not sure how to accomplish that.

My table:

TruckTareID (Primary Key)
TruckID
TareDate
TareWeight

Since the average weight changes over time, can this be accomplished with
running totals? For instance, I need average weight as of August 1st, and
the average weight as of August 5th, etc.

Any suggestions are appreciated.

 

[HotRodSue]

Ken,

Thank you for your reply and detailed explanation in your posts. It sure is
helpful as I'm in the learning process here. I posted the code in SQL View
of blank query. When I click Run I get the message "Syntax error in FROM
clause." When I click OK on that dialouge box, the word JOIN is hi-lighted
within the code. Any ideas on what to do?

Many thanks,

Sue

 

[John Spencer]

Well
== INNNER Should be INNER


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

 

[HotRodSue]

Thanks so much, that did it! Now, I'm moving forward with my next query!

 

[HotRodSue]

Ken,

Well, I thought I had this. My issue now is that the Average Weight needs
to be a running total. Tare Weight is entered periodically by date, for
example as of August 1st. The Average Weight results will be used to
calculate Net Tons by harvest dates, for example August 1st through August
5th. A crop can be harvested over a several day period, and the Average
Weight results would remain the same. Does that make sense? Any ideas on how
to address the issue are appreciated.

Sue

 

[HotRodSue]

Ken,

You absolutely have it straight. Your interpretation is spot on. Yes, to
your questions in both your first and second paragraph. With your experience
you can probably appreciate the time I've spent trying to resolve this. It
can get so overwhelming. To answer your question about the column and table;
the gross weight is recorded as TotalWeight in tblTruckFieldCrops.

You must know how relieved I am to have a solution in process, your insights
are so very appreciated.

Kind regards,

Sue

Sue:

Let's see if I've got this straight. A crop is harvested over the 5 days
from 1st August to 5th August, so presumably a series of gross weights per
truck will be recorded over those 5 days. The same tare value is subtracted
from each gross weight value per truck over the 5 days to produce a series of
net weight values. Is that right?

Is, in the above scenario, the averaging of the tare values over the last
three dates the average of the tare on 1st August and on whatever were the
two tare values taken previous to that? If so the underlying basis for a
computation seems to be that the tare applied to a gross weight to give the
net weight is the average of that on the last three dates before the date the
gross weight is recorded.

If the above interpretation is correct then it should be possible to come up
with a query which applies the appropriate tare to each gross weight and
returns the net weight by, for each gross weight reading, computing the tare
to be subtracted in the way we've described in the earlier posts, but using
the three latest date rows from tblTruckTare where the dates are on or before
the date when the gross weight was taken. What you haven't yet told us,
though, is in what column of what table the gross weights are recorded, so
until we know that we can't come up with a suitable query.

Or have I got it all wrong? If so perhaps you can spell things out in terms
which even a simple Irish bog-trotter like me can understand.

Ken Sheridan
Stafford, England

Ken,

Well, I thought I had this. My issue now is that the Average Weight needs
to be a running total. Tare Weight is entered periodically by date, for
example as of August 1st. The Average Weight results will be used to
calculate Net Tons by harvest dates, for example August 1st through August
5th. A crop can be harvested over a several day period, and the Average
Weight results would remain the same. Does that make sense? Any ideas on how
to address the issue are appreciated.

Sue

Doh! Extra closing parenthesis got in there. Should have been:

[quoted text clipped - 21 lines]

ORDER BY TareDate DESC))
GROUP BY pkTruckID, txtTruckNo;

 

[kate]

Sue:

Good.

Its getting late here, so I'll get back to you tomorrow when I've had a
chance to come up with a query which brings in the tblTruckFieldCrops
table.

Ken Sheridan
Stafford, England

Ken,

You absolutely have it straight. Your interpretation is spot on. Yes, to
your questions in both your first and second paragraph. With your
experience
you can probably appreciate the time I've spent trying to resolve this.
It
can get so overwhelming. To answer your question about the column and
table;
the gross weight is recorded as TotalWeight in tblTruckFieldCrops.

You must know how relieved I am to have a solution in process, your
insights
are so very appreciated.

Kind regards,

Sue

Sue:

[quoted text clipped - 43 lines]

ORDER BY TareDate DESC))
GROUP BY pkTruckID, txtTruckNo;

 

[HotRodSue]

Ken,

Thank you so much for your time in helping resolve this. I'm glad you
mentioned that you assumed that the tblTruckFieldCrops table also contains
columns fkTruckID and HarvestDate. It has the fkTruckID column, however,
does not have HarvestDate. My apologies.

I did work in your code to reference the tblHarvestDates that contains the
column HarvestDate. I'm still having trouble with the query. Could it be
because the tblHarvestDates is not directly related to tblTruckFieldCrops?
tblHarvestDates is joined to tblHarvestDateFieldCrop, which is then joined to
tblTruckFieldCrops. Here's the table structure for the 3 tables mentioned:

tblHarvestDates (Table Name)
HarvestDate

tblHarvestDateFieldCrop (Table Name)
pkHarvestDateFieldCropID
fkHarvestDateID
fkFieldPlotCropID

tblTruckFieldCrops
pkTruckFieldCropsID
fkHarvestDateFieldCropID
fkTruckID
TotalWeight
TruckTime
PileNumber

Warm regards,

Sue

Sue:

I think this should do it:

SELECT pkTruckID, txtTruckNo, HarvestDate, TotalWeight,
(SELECT AVG(TareWeight)
FROM tblTruckTare As T1
WHERE T1.fkTruckID = tblTrucks.pkTruckID
AND TareDate IN
(SELECT TOP 3 TareDate
FROM tblTruckTare AS T2
WHERE T2.fkTruckID = T1.fkTruckID
AND T2.TareDate <= tblTruckFieldcrops.HarvestDate
ORDER BY TareDate DESC))
AS Tare, TotalWeight - Tare AS NetWeight
FROM tblTrucks INNER JOIN tblTruckFieldcrops
ON tblTrucks.pkTruckID = tblTruckFieldcrops.fkTruckID
ORDER BY txtTruckNo, HarvestDate;

I've assumed that the tblTruckFieldcrops table also contains columns
fkTruckID and HarvestDate. It might be a tad slow with if the tables are
large. I've only been able to test it with a small amount of dummy data of
course, but it seems to do the trick.

You could of course base 'totals' queries on the above query to aggregate the
data for each truck, or each harvest date, over a date range etc if you
wished.

Let me know how you get on with it.

Ken Sheridan
Stafford, England

Ken,

You absolutely have it straight. Your interpretation is spot on. Yes, to
your questions in both your first and second paragraph. With your experience
you can probably appreciate the time I've spent trying to resolve this. It
can get so overwhelming. To answer your question about the column and table;
the gross weight is recorded as TotalWeight in tblTruckFieldCrops.

You must know how relieved I am to have a solution in process, your insights
are so very appreciated.

Kind regards,

Sue

Sue:

[quoted text clipped - 43 lines]

ORDER BY TareDate DESC))
GROUP BY pkTruckID, txtTruckNo;

 

[HotRodSue]

Ken,

Thank you for the update, it works fantastic! Your help is very much
appreciated. I surprised myself when I figured the missing piece was the
table not directly related. When I received your response, you confirmed I
was at least on the right track. Feels good to be learning.

Your insight and explanations, along with hints and notes helped make this a
great learning experience. There is still so much for me to learn. Today I
spent time carefully looking at my DB to delete my various unsuccessful query
attempts.

As I move on to my next task in the DB, I wanted to take time to thank you
for helping me over this hurdle.

Warm Regards,

Sue

Sue:

You'll need to join the tables in the outer query and return the HarvestDate
column from the tblHarvestDates table. try this:

SELECT pkTruckID, txtTruckNo, HarvestDate, TotalWeight,
(SELECT AVG(TareWeight)
FROM tblTruckTare As T1
WHERE T1.fkTruckID = tblTrucks.pkTruckID
AND TareDate IN
(SELECT TOP 3 TareDate
FROM tblTruckTare AS T2
WHERE T2.fkTruckID = T1.fkTruckID
AND T2.TareDate <= tblHarvestDates.HarvestDate
ORDER BY TareDate DESC))
AS Tare, TotalWeight-Tare AS NetWeight
FROM tblTrucks INNER JOIN (tblHarvestDates
INNER JOIN (tblHarvestDateFieldCrop
INNER JOIN tblTruckFieldCrops
ON tblHarvestDateFieldCrop.pkHarvestDateFieldCropID
=tblTruckFieldCrops.fkHarvestDateFieldCropID)
ON tblHarvestDates.pkHarvestDateID
=tblHarvestDateFieldCrop.fkHarvestDateID)
ON tblTrucks.pkTruckID=tblTruckFieldCrops.fkTruckID
ORDER BY txtTruckNo, HarvestDate;

Ken Sheridan
Stafford, England

Ken,

Thank you so much for your time in helping resolve this. I'm glad you
mentioned that you assumed that the tblTruckFieldCrops table also contains
columns fkTruckID and HarvestDate. It has the fkTruckID column, however,
does not have HarvestDate. My apologies.

I did work in your code to reference the tblHarvestDates that contains the
column HarvestDate. I'm still having trouble with the query. Could it be
because the tblHarvestDates is not directly related to tblTruckFieldCrops?
tblHarvestDates is joined to tblHarvestDateFieldCrop, which is then joined to
tblTruckFieldCrops. Here's the table structure for the 3 tables mentioned:

tblHarvestDates (Table Name)
HarvestDate

tblHarvestDateFieldCrop (Table Name)
pkHarvestDateFieldCropID
fkHarvestDateID
fkFieldPlotCropID

tblTruckFieldCrops
pkTruckFieldCropsID
fkHarvestDateFieldCropID
fkTruckID
TotalWeight
TruckTime
PileNumber

Warm regards,

Sue

Sue:

[quoted text clipped - 49 lines]

ORDER BY TareDate DESC))
GROUP BY pkTruckID, txtTruckNo;