Query Most Recent

[hotrodsue]

I have weights that are recorded by date. What I need to query is the most recent 3 by date, then sum the most recent 3, and then average the most recent 3.

Can this be accomplished with running totals? For instance, the average weight as of August 1st, and the average weight as of August 5th will be required.

Any suggestions are appreciated.

 

[jjturner]

hotrodsue,

How are you recording your data? Is it only 1 weight recorded per day, or possibly multiple weights recorded per day? I only mention that since it will complicate your intention of obtaining the "most recent 3 by date".

Here is an article which may help you conceptualize what you're attempting to do.

The basic idea is that you'll be using correlated Subqueries to pull your averages and running totals.

HTH,
John

 

[hotrodsue]

John,

The data is recorded by TruckID and usually has one TareWeight per TruckID per given day. Multiple weights can be entered in one day (due to multiple vehicles). For each TruckID I need to average the 3 most recent TareWeight entries.

For reference, 2 of my tables:

TruckTareID (Primary Key)
TruckID
TareDate
TareWeight

TruckID (Primary Key)
TruckNumber

I'll take a closer look at "How to Rank Records within a Query". Thank you for your post and link. Hopefully I answered your question. Any other thoughts you have are surely welcome.

Thanks,

Sue

 

[jjturner]

Hi Sue,

I should've mentioned, the aggregate functions you want to use against the recordset will require "nested" subqueries, so your entire SQL would be something like this:

SELECT DISTINCT main.TruckID, main.TareDate,
(SELECT Count(*) As RecentRecords FROM
    (SELECT TOP 3 sub1.TareWeight
     FROM tblTruckTare sub1
     WHERE sub1.TruckID = main.TruckID AND sub1.TareDate <= main.TareDate
     ORDER BY sub1.TareDate DESC)),
(SELECT Sum(sub2.TareWeight) As RecentWeightsTotal FROM
    (SELECT TOP 3 sub2.TareWeight
     FROM tblTruckTare sub2
     WHERE sub2.TruckID = main.TruckID AND sub2.TareDate <= main.TareDate
     ORDER BY sub2.TareDate DESC)),
(SELECT Avg(sub3.TareWeight) As RecentWeightsAvg FROM
    (SELECT TOP 3 sub3.TareWeight
     FROM tblTruckTare sub3
     WHERE sub3.TruckID = main.TruckID AND sub3.TareDate <= main.TareDate
     ORDER BY sub3.TareDate DESC))
FROM tblTruckTare main;

I also added a "Counts" column to show you how many records were being calculated in the Sum and Avg - which won't necessarily be 3 every time...

HTH,
John

 

[hotrodsue]

John,

Thank you posting an example. The record count is a great idea.

I'm sorry, 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 to tblTrucks)
TareDate (Data Type Date/Time)
TareWeight (Data Type Number)

When reviewing your code, would the word "main" need to be replaced with the table name? It seems the TruckID needs to be replaced with pkTruckID and/or fkTruckID. Is that correct.

Thanks,

Sue

 

[ByteMyzer]

This query can be simplified with one subquery:

SELECT T1.TruckID, Avg(T1.TareWeight) AS AvgTareWeight
FROM tblTruckTare AS T1
WHERE T1.TareDate IN (
    SELECT TOP 3 T2.TareDate
    FROM tblTruckTare AS T2
    WHERE T2.TruckID = T1.TruckID
    ORDER BY T2.TareDate DESC)
) GROUP BY T1.TruckID;

 

[jjturner]

@ ByteMyzer - I neglected to consider using the WHERE clause for the subquery, so my hats off to you for the simplification

As you have it, my only question regarding that would be whether this gives Sue a 'running' Average, or only a single Avg per TruckID? I suppose the point is moot if you simply included TareDate in the main SELECT clause and modified the subquery WHERE clause accordingly...

@ Sue - my use of 'main' is simply as an Alias for the true table name, "tblTruckTare", so it's rather the opposite of what you suggest - "main" is replacing "tblTruckTare", but as you see in both ByteMyzer's and my solution, the reference to "tblTruckTare" is still there, it's just being "aliased" by a subsequent identifier (I didn't explicitly use "AS" to indicate this, but the effect is the same as if I had).

And yes, TruckID should be replaced by fkTruckID if that is the actual name of your field.

Let us know how it works out for you.

Cheers,
John

 

[hotrodsue]

@John, thank you for answering my questions. You explanation about the alias helped me. Guess, I need to get this working first, then I'll know how to address your question:

As you have it, my only question regarding that would be whether this gives Sue a 'running' Average, or only a single Avg per TruckID? I suppose the point is moot if you simply included TareDate in the main SELECT clause and modified the subquery WHERE clause accordingly...

@ByteMyzer, thanks for your input as well.

I've pasted the code from ByteMyzer. I'm getting an error that there is an extra parentheses in the query expression. I've tried removing a parentheses and moving it, with no luck. Below I've pasted the only change I've made to the code - placing fk in front of TruckID.

Should all references to the TruckID in fact be the foreign key from tblTruckTare, fkTruckID? In looking it over it doesn't seem like there is a reference to the tblTrucks which holds the primary key pkTruckID. So it seems I would not place pkTruckID anywhere in the code. Is that right?

SELECT T1.[COLOR=red]fk[/COLOR]TruckID, Avg(T1.TareWeight) AS AvgTareWeight
FROM tblTruckTare AS T1
WHERE T1.TareDate IN (
    SELECT TOP 3 T2.TareDate
    FROM tblTruckTare AS T2
    WHERE T2.[COLOR=red]fk[/COLOR]TruckID = T1.[COLOR=red]fk[/COLOR]TruckID
    ORDER BY T2.TareDate DESC)
) GROUP BY T1.[COLOR=red]fk[/COLOR]TruckID;

Thanks so much for the help.

 

[ByteMyzer]

Sue,

You're the one who posted the field names; I was simply using the names that you posted. The answers you receive in this forum are only as good as the information that you provide.

 

[hotrodsue]

ByteMyzer, I appreciate your help and I certainly learned a valuable lesson. When posting a question, it is important for me to list exact information on my issue. You provided a simplified solution, however, due to my error in posting I'm still unable to get your solution to work.

At this time, I'll continue to work on placing the exact names in your example. Apparently, I'm still doing something wrong. The questions I have are:

1) I'm getting an error message stating there is an extra paranthesis in query expression. Can you see where I would need to remove a paranthesis in your example?

2) Should all references to the TruckID in fact be the foreign key from tblTruckTare, fkTruckID?

If you're willing to take another look, here is the exact info on 2 of my tables:

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

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

Thanks so much for the help.

 

[ByteMyzer]

The extra parenthesis is by bad. Here is how the structure should look:

SELECT T1.fkTruckID, Avg(T1.TareWeight) AS AvgTareWeight
FROM tblTruckTare AS T1
WHERE T1.TareDate IN (
    SELECT TOP 3 T2.TareDate
    FROM tblTruckTare AS T2
    WHERE T2.fkTruckID = T1.fkTruckID
    ORDER BY T2.TareDate DESC
) GROUP BY T1.fkTruckID;

 

[hotrodsue]

Thanks for posting, that did it!

 

[hotrodsue]

John,

Well, I thought I had this. My issue remains that I need the running total. In reviewing this thread, I've tried to apply your recommendations. It's probably simple, I'm just stuck.

In working with your solution, I replaced instances of TruckID with the actual field name fkTruckID. When I click "Run", I'm prompted for parameter values for:

main.fkTruckID
main.TareDate

Any ideas of what I'm missing?

Thanks,

Sue

@ ByteMyzer - I neglected to consider using the WHERE clause for the subquery, so my hats off to you for the simplification

As you have it, my only question regarding that would be whether this gives Sue a 'running' Average, or only a single Avg per TruckID? I suppose the point is moot if you simply included TareDate in the main SELECT clause and modified the subquery WHERE clause accordingly...

@ Sue - my use of 'main' is simply as an Alias for the true table name, "tblTruckTare", so it's rather the opposite of what you suggest - "main" is replacing "tblTruckTare", but as you see in both ByteMyzer's and my solution, the reference to "tblTruckTare" is still there, it's just being "aliased" by a subsequent identifier (I didn't explicitly use "AS" to indicate this, but the effect is the same as if I had).

And yes, TruckID should be replaced by fkTruckID if that is the actual name of your field.

Let us know how it works out for you.

Cheers,
John

 

[jjturner]

Hi Sue,

I suspect my main query references in the subqueries are getting disrupted by the Aggregate functions. Well there's always more than 1 way to skin a cat, so my other (and more efficient) approach would be to integrate ByteMyzer's solution into a self-join:

SELECT T1.fkTruckID, T1.TareDate, Avg(T2.TareWeight) AS RunningAvg_TWeight,
Sum(T2.TareWeight) AS RunningTotal_TWeight, Count(T2.TareWeight) AS RecentRecords
FROM tblTruckTare T1 INNER JOIN tblTruckTare T2 ON T1.fkTruckID = T2.fkTruckID
WHERE T2.TareDate IN
   (SELECT TOP 3 Tsub.TareDate
    FROM tblTruckTare AS Tsub
    WHERE Tsub.fkTruckID = T[COLOR="Red"]1[/COLOR].fkTruckID AND Tsub.TareDate <= T1.TareDate
    ORDER BY TSub.TareDate DESC)
GROUP BY T1.fkTruckID, T1.TareDate;

I think this should get you the running avg's, totals, and counts.

Let me know how it works out...

Cheers,
John

 

[hotrodsue]

Hi John,

Your solution works very well, with running avg’s, totals, and counts. The results are exactly as they should be, thanks! “There’s more than 1 way to skin a cat.” That gave me a smile, so nice to have different ideas shared to resolve an issue.

My next calculation will use the RunningAvg_TWeight results from above query, qryTruckTare. It seems I need to tie 2 different queries together. The concept of using aliases is new to me, so I wonder if that would be a more efficient means.

The basic idea is this. Trucks haul about 6 loads per day; each load per truck is weighed. All loads per truck are totaled. The RunningAvg_TWeight multiplied by the number of loads is subtracted from the Sum Of TotalWeight to determine Net Weight in pounds. The pounds are then converted to Net Tons.

Now, qryTruckFieldCrops is based on 5 tables and calculates the Sum of Total Weight AND the Count of Truck Loads for each Truck. The results from the qryTruckTareAvg and results from qryTruckFieldCrops can determine Net Pounds. Since I wasn’t sure how to tie the 2 queries together, I created another query which includes this code:

Net Pounds: [Sum Of TotalWeight]-([Count Of tblTruckFieldCrops]*[qryTruckTareAvg]![AvgOfTareWeight])

That isn’t working quite right. The more I move forward, the more it seems I’m complicating things. Can I calculate Net Pounds without creating another query? Truck load weights are entered into TotalWeight of this table:

tblTruckFieldCrops (Table Name)
pkTruckFieldCropID
fkHarvestDateFieldCropID
fkTruckID
TotalWeight
TruckTime
PileNumber

Any thoughts on how to best accomplish my series of calculations?

Kind regards,

Sue

 

[jjturner]

Hi Sue,

Apologies for not responding sooner. I've had a few read-through's of your latest issue, and I'm afraid I still can't make heads or tails of it.

You're introducing a lot of different elements (tables, queries, fields, calculated fields, etc.,) and there's no real way for me to make logical connections with all that you have going on.

So, if I may; I'd like to ask if you would please try to specifically explain:

• the exact attributes of the result you need (Net Pounds per Truck? per Load?)
• the exact problem you're having at the moment
• SQL for your 2 component queries and for the query in question (if that is how you're presently going about it)

And please be very strict about the names of all your objects as it seems your post may be referring to the same thing with slightly different name variations.

HTH,
John

 

[hotrodsue]

John,





No problem, I’m happy to have your help.

• the exact attribute needed: Net Pounds per txtTruckNo by HarvestDate. Attached is a screen shot (NetPounds) that shows the math used to determine Net Pounds harvested by one txtTruckNo for one HarvestDate.
• the exact problem I’m having at the moment: I can't figure out how to use results from 2 separate queries (qryTruckTareAvg and qryTruckFieldCrops) to determine Net Pounds.
• SQL for the 2 component queries and for the query in question is pasted below.

The first component query is from a post in this thread, which I have named: qryTruckTareAvg

[FONT=Verdana][FONT=Verdana]SELECT T1.fkTruckID, T1.TareDate, Avg(T2.TareWeight) AS RunningAvg_TWeight, Sum(T2.TareWeight) AS RunningTotal_TWeight, Count(T2.TareWeight) AS RecentRecords[/FONT]
[FONT=Verdana]FROM tblTruckTare AS T1 INNER JOIN tblTruckTare AS T2 ON T1.fkTruckID = T2.fkTruckID[/FONT]
[FONT=Verdana]WHERE (((T2.TareDate) In (SELECT TOP 3 Tsub.TareDate     FROM tblTruckTare AS Tsub     WHERE Tsub.fkTruckID = T2.fkTruckID AND Tsub.TareDate <= T1.TareDate     ORDER BY TSub.TareDate DESC)))[/FONT]
[FONT=Verdana]GROUP BY T1.fkTruckID, T1.TareDate;[/FONT][/FONT]

The second component query is named qryTruckFieldCrops, used to determine the Sum Of TotalWeight for each txtTruckNo by HarvestDate. Also counting the number of loads in the expression: Count(*) AS [Count Of tblTruckFieldCrops] While qryTruckFieldCrops appears to be working, I wonder if I should use aliases rather than the actual tables. Attached is a screen shot of design view of qryTruckFieldCrops.

SELECT DISTINCTROW tblFieldPlotCrop.pkFieldPlotCropID, tblHarvestDateFieldCrop.pkHarvestDateFieldCropID, tblHarvestDates.HarvestDate, tblTrucks.txtTruckNo, Sum(tblTruckFieldCrops.TotalWeight) AS [Sum Of TotalWeight], Count(*) AS [Count Of tblTruckFieldCrops]
FROM tblTrucks INNER JOIN (tblHarvestDates INNER JOIN ((tblFieldPlotCrop INNER JOIN tblHarvestDateFieldCrop ON tblFieldPlotCrop.pkFieldPlotCropID = tblHarvestDateFieldCrop.fkFieldPlotCropID) INNER JOIN tblTruckFieldCrops ON tblHarvestDateFieldCrop.pkHarvestDateFieldCropID = tblTruckFieldCrops.fkHarvestDateFieldCropID) ON tblHarvestDates.pkHarvestDateID = tblHarvestDateFieldCrop.fkHarvestDateID) ON tblTrucks.pkTruckID = tblTruckFieldCrops.fkTruckID
GROUP BY tblFieldPlotCrop.pkFieldPlotCropID, tblHarvestDateFieldCrop.pkHarvestDateFieldCropID, tblHarvestDates.HarvestDate, tblTrucks.txtTruckNo;

The query in question that is not working is qryNetPounds. I used qryTruckFieldCrops and added qryTruckTareAvg in my effort to use results from these 2 queries to determine NetPounds.

SELECT DISTINCTROW tblFieldPlotCrop.pkFieldPlotCropID, tblHarvestDateFieldCrop.pkHarvestDateFieldCropID, tblHarvestDates.HarvestDate, tblTrucks.txtTruckNo, Sum(tblTruckFieldCrops.TotalWeight) AS [Sum Of TotalWeight], Count(*) AS [Count Of tblTruckFieldCrops], qryTruckTareAvg.RunningAvg_TWeight, tblTruckFieldCrops.fkTruckID
FROM (tblTrucks INNER JOIN qryTruckTareAvg ON tblTrucks.pkTruckID = qryTruckTareAvg.fkTruckID) INNER JOIN (tblHarvestDates INNER JOIN ((tblFieldPlotCrop INNER JOIN tblHarvestDateFieldCrop ON tblFieldPlotCrop.pkFieldPlotCropID = tblHarvestDateFieldCrop.fkFieldPlotCropID) INNER JOIN tblTruckFieldCrops ON tblHarvestDateFieldCrop.pkHarvestDateFieldCropID = tblTruckFieldCrops.fkHarvestDateFieldCropID) ON tblHarvestDates.pkHarvestDateID = tblHarvestDateFieldCrop.fkHarvestDateID) ON tblTrucks.pkTruckID = tblTruckFieldCrops.fkTruckID
GROUP BY tblFieldPlotCrop.pkFieldPlotCropID, tblHarvestDateFieldCrop.pkHarvestDateFieldCropID, tblHarvestDates.HarvestDate, tblTrucks.txtTruckNo, qryTruckTareAvg.RunningAvg_TWeight, tblTruckFieldCrops.fkTruckID;

Finally, I attached a screen shot of my relationships. Thank you for your help. Please let me know if you need more clarification or information. It’s pretty mind boggling to me.

Kind regards,

Sue

 

[jjturner]

Hi Sue,

To begin with, let me say you've done an excellent job of full-disclosure!

Secondly, regarding qryTruckTareAvg I finally realized after I thought about it that it would in fact give you the same results with either table reference in the subquery, so thanks for confirming that with concrete evidence.

Well, this latest issue is actually pretty simple, as it turns out (famous last words ). We should be able to take care of it in 2 steps:

• Add fkTruckID as a column in your qryTruckFieldCrops query
• qryNetPounds will be like this:

SELECT HarvestDate, txtTruckNo,
([Sum of TotalWeight] - [Count of tblTruckFieldCrops]*[RunningAvg_TWeight])
   AS NetPounds
FROM qryTruckFieldCrops qTFC INNER JOIN qryTruckTareAvg qTTA ON
   qTFC.fkTruckID = qTTA.fkTruckID AND
   qTFC.HarvestDate = qTTA.TareDate;

HTH,
John

 

[Sako]

I am wondering whether it is possible to add to this code the requirement that
it has to select exactly 3 numbers to do the average. Using select top 3 might result in selecting only 2 numbers in case a truck would not have 3 weights..
This would result in a faulty average,


any help on this?

 

[jjturner]

Hi Sako,

Regarding qryTruckTareAvg, you're right - that's why I added in the Count column so it would show any instances out of the norm. If you need a strict requirement to average exactly 3 records, I would think you could add a condition in the HAVING clause to only show records with counts = 3.

HTH,
John