Getting Latest Date from One Table Relative to Date in Another Table

[rjgriffin46]

In our Kids Feeding database, there are the following tables:

1. Site Changes. Fields:
   
   1. Site Number
   2. Meal Quantity
   3. Effective Date (for the meal quantity)
   
   
2. Delivery. Fields:
   
   1. Delivery Date
   2. Serving Start Date (the 1st date the meals delivered on the Delivery Date will be served)
   3. Number of Serving Days (how many days worth of meals are delivered)

In the situation to be addressed, 5 days worth of meals are delivered once a week to each site. The delivery is done on Thursday and the meals are for the following Monday through Friday.
There can be multiple site change records for any given site. An example is that one site requires 125 meals Monday through Thursday and 120 on Friday. As it stands now, and subject to future enhancement, a Site Change record must be entered for each meal count change. So for example for the week of Monday, 10/7 to Friday, 11/October there would be the following site change records:

Site Number	Meal Quantity	Effective Date
1	125	10/7/19
1	120	10/11/19

I have a query that generates a record for each serving day for a delivery, so in this example, for a Thursday 10/3/19 delivery, there would be the following 5 records in the query recordset:

Serving Date

10/07/19

10/08/19

10/09/10

10/10/19

10/11/19

I next need to generate another query that has the latest meal quantity for each of the 5 serving dates for each site. In this case the resulting recordset should be:

Site Number	Meal Quantity	Serving Date
1	125	10/07/19
1	125	10/08/19
1	125	10/09/19
1	125	10/10/19
1	120	10/11/19

After some thought and a very brief search I'm thinking that a DMAX expression might work for this. This is a pretty small database with only a couple of users so performance shouldn't be an issue. Although I don't think a factor, this is the database about which I'd previously posted is deployed an has been running with a single read only front end via Citrix without any apparent problem for several months.

As always, I'm appreciative of everyone's ideas and guidance, especially if you can point to some examples where something like this has been done.



Thanks

[CJ_London]

you would need to use DMax and Dlookup to achieve what you want, better to use a subquery, or special join

if data volumes are small, use a subquery

SELECT SiteNumber, ServingDate, (SELECT TOP 1 MealQuantity FROM tblChanges WHERE EffectiveDate<=ServingDate ORDER BY EffectiveDate Desc) AS Meals
FROM QryServingDates

[rjgriffin46]

Thank you Ajax. This looks quite promising.