Query Most Recent

[hotrodsue]

Hi John,

Glad to hear the full-disclosure helped. Thanks for letting me know we're "good to go" on the qryTruckTareAvg subquery results. I typed a post earlier today and must have hit "preview" instead of "submit".

Your solution looks so much more efficient than the attempts I've made. On qryNetPounds I'm sorry that I did not mention that the tare date is not always the same as the harvest date. For each harvest date for each truck we use the most recent tare weight. Does that make sense?

In looking at the following statement:

qTFC.HarvestDate = qTTA.TareDate

As is, the query does not return all the results it should. When I remove the above statement, too many results are returned. I think (look out ) that the pkFieldPlotCropID may pull the results I need. However, it is only in qryTruckFieldCrops and NOT in qryTruckTareAvg.

I'm going to keep working on this. Any ideas on what to do are surely appreciated.

Thanks,

Sue

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:

1. Add fkTruckID as a column in your qryTruckFieldCrops query
2. 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

 

[jjturner]

Hi Sue,

Hmmm... so we need a way to associate the Tare with the Field Crop for any particular truck.

Can you explain in just words (forget about the tables and fields for now) how a particular Tare average is picked in real life to apply to a particular Field Crop for the truck?

That might help us figure out where we need to go here.

Cheers,
John

 

[hotrodsue]

Hi John,

An Excel spreadsheet was given to me to show how a harvest is currently recorded. Attached is a copy for reference. As you can imagine this current method is not the most efficient way to keep the harvest records.

In this example we have 8 different trucks hauling up to 6 loads on 1 harvest date. Typically a field is harvested over the course of several days.

Tare weight is usually taken sometime within the course of the several day harvest “event”. Truck drivers turn in a sheet that has truck load weight and truck tare weight. However, there are instances when a tare weight is not taken during a harvest event.

Tare weight is applied to a particular Field Crop for the truck by physically pulling a previous harvest spreadsheet. Then the previous tare weight and any current tare weights are manually entered into a current spreadsheet.

Does this help? If not, please let me know.

Cheers,

Sue

 

[jjturner]

Hi Sue,

By the sound of it, I'd say you need "the most recent" tare weight available for a given truck and harvest date (correct me if I'm wrong) - provided the associated tare weight date is less than or equal to (<=) the harvest date.

Based on that, this would be my attempt to "marry up" the necessary values:

SELECT t1.HarvestDate, t1.txtTruckNo,
    (t1.STWgt - t1.Loads * t1.ATWgt) AS NetPounds,
    (t1.STWgt - t1.Loads * t1.ATWgt) / 2000 AS NetTons
FROM
    (SELECT HarvestDate, txtTruckNo, [Sum of TotalWeight] AS STWgt,
     [Count of tblTruckFieldCrops] AS Loads,
        (SELECT TOP 1 RunningAvg_TWeight
         FROM qryTruckTareAvg qTTA
         WHERE qTFC.fkTruckID = qTTA.fkTruckID
            AND qTFC.HarvestDate >= qTTA.TareDate
         ORDER BY qTTA.TareDate DESC) AS ATWgt
     FROM qryTruckFieldCrops qTFC) t1;

Hopefully that gets you the results you're looking for.

Cheers,
John

 

[hotrodsue]

John,

That did give me the results I'm looking for. I doubled checked the results against my manual calculations. It's all correct!

Since I'm relatively new to db design, I've mostly worked from a visual perspective in design view of Access. To familiarize myself with code I review SQL and work in that some too.

When I study the solutions you suggest, I see that you can look at a scenario and logically determine how to write the code. Once I study what you've done it makes sense. My goal is to learn how to approach a solution using SQL.

No doubt the more hands on experience I have, the more I'll learn. There are so many resources with books, DVD's, and this forum. Do you have a favorite or suggested resource for learning SQL?

Thanks for all your help,

Sue

 

[jjturner]

Hi Sue,

You're welcome, I'm glad that worked out for you

As for a favorite/suggested resource, these forums aren't bad for picking up tips and methods (as you've probably noticed even in this thread). For actual syntax reference and occasional examples, the library at the MSDN site is good. Also, I've recently found DevGuru to be helpful with SQL usage examples and specifications.

HTH,
John

 

[hotrodsue]

John,

It's amazing to see how my project is coming along. Many thanks to you and folks like you at the forum that are happy to share what you know. You can bet I'll be at this forum when I do have questions. It's nice to know there's a place to troubleshoot and toss ideas around.

The links look fantastic. Thank you so much for posting them. No doubt they'll be of help to others that may take a peek at this thread.



Kind regards,

Sue