Unique Date Quries

[m.pietrzy]

I have a table named INVENTORY. Each day, the current tank inventory is entered. I want to compare two unique dates vs. a range of dates. From that comparison, I can add raw material receipts and shipped goods and determine actual production.



For example: If on 01-May-2019 a tank contains 100 (we'll call this "x") and on 07-May-19 contains 300 (we'll call this "y"). In between 01-May and 07-May, I received 1000 (we'll call this "z"), the amount processed is 800. ((x+z)-y)=processed amount.

Where I'm getting wrapped around the axle is trying to query the specific dates vs. "between" dates. The only way I can seem to get close is by using a "start" query, a "finish" query, a "received" query, and a "shipped" query. From there I would build a main report with four sub-reports and from there perform the calculations in the report.

Is this a good approach... or is there a better (I'm sure there is but my eyes are crossing at this point).

[Micron]

Not really sure what you mean by "trying to query the specific dates vs. "between" dates." Sounds like you want a value on each of two dates (one before and one after) plus a series/set of records that come in between. Maybe look at http://allenbrowne.com/subquery-01.html

If all you want is a value on two given dates and you know those dates, then you can retrieve those easily enough. Perhaps then you want to show all the records that come in between, or maybe you just want to total the units processed during that period? Kinda fuzzy. Depending on where you're doing what, maybe all you need is a DLookup on each of the 2 dates with either records or aggregate functions on what comes in between.