Populating empty fields to get a daily snapshot of data

[MaxAndMin]

Alright, seems like I’m running out of wisdom once again. I’m working on a database that monitors the fuel inventory of multiple tanks. Main entries are the dips, entered only when liquid is added/taken out (not daily), which are then converted to Volume with a formula (calcvol below). My goal with this particular query is to return a daily snapshot of the total Inventory as of that date. With the help of June7 and Allen Browne, I was able to make a statement that returns the latest dip value for every tank without the need of cascading tons of queries and making silly relationships. It looks like this.

SELECT tblFuelDip2.fdDateTime, tblFuelDip2.fdTankNo, tblFuelDip2.fdReading, calcvol([fdReading],[stTankType]) AS Volume
FROM tblStorageTanks INNER JOIN tblFuelDip2 ON tblStorageTanks.stID = tblFuelDip2.fdTankNo
WHERE (((tblFuelDip2.[fdDateTime])=
(SELECT max([fdDateTime]) FROM tblFuelDip2 as x WHERE tblFuelDip2.fdTankNo = x.fdTankNo)));

This works great to know my total inventory at the current moment. Now the part where my brain stops to function is when trying to get this value AS OF each calendar date. So in other words, to run this for every fdDate while excluding any records where the fdDate is greater than the one we’re trying to get a result for.
If I simply do a where fdDateTime <= [insert random date], in the query above, it’ll exclude whatever tanks have been filled up after that date, but I won’t get a total snapshot of the volume as of that date, simply the ones that haven’t changed.

Example Current Result displayed as a crosstab (appologies for the dots...)
................2012-11-07...2012-11-08...2012-11-09...2012-11-010...2012-11-11
Tank 1................................................. .................................................. .....20000
Tank 2................................15000
Total................0.................15000...... ..........0........................0.............. ..20000

Example Desired Result displayed as a crosstab
................2012-11-07...2012-11-08...2012-11-09...2012-11-010...2012-11-11
Tank 1........50000.............50000.............40000 ..............30000...........20000
Tank 2........30000.............15000.............15000 ..............15000...........15000
Total...........80000.............65000........... ..55000 ..............45000...........35000

My backup plan for this is simply to have a single entry per tank everyday and sum up the volume at that date. Although that is very “excel” and will create tons of duplicate data, so I’m sure there’s a way to avoid that. I could also just convert all dip values to Volumes and deal with the rest in excel in a pivot table, which is what friends are recommending. Or perhaps i'm thinking about this a little bit too linear?

[meratigoerr]

You can get the value of any day by modifing the query you have already. Two options. If you know the specific date you a looking for has a reading, but it in a text box on your form. Change

(SELECT max([fdDateTime]) FROM tblFuelDip2 as x WHERE tblFuelDip2.fdTankNo = x.fdTankNo)));

to something along the lines of
(SELECT [fdDateTime] = Forms!YourForm!DateTextField FROM tblFuelDip2 as x WHERE tblFuelDip2.fdTankNo = x.fdTankNo))); (my sql syntax needs work I know, but hopefully you get the idea)
If you don't know if the date in question has a fuel reading, use fdDateTime <= Forms!YourForm!DateTextField. Set the dates in descending order and set the query to return only one result. (Give the max date value less than or equal to your entered date.)

[MaxAndMin]

Thanks again for your help meratigoerr. As I'm sure you figured it out, I'm going to use this query in order to use the other formula you've helped me out with. Decided to use subqueries to simplify the daily entry of dips. But it's only complicating the daily volume output O_o

I see the logic behind what you're proposing. The issue I'm having when trying to implement this is that my only date field in the dip-related tables is this [fdDateTime]. I cannot compare it to itself, access is going to go in an endless loop (unless i can??). I've tried applying this by making a table called 'tblDates' where the only entries are the dates of the year, and then using these for the comparison. Whichever way i do the join, it doesn't seem to like what i'm doing, as it simply returns empty fields under the tblDates.DatesField column.

[meratigoerr]

I see the logic behind what you're proposing. The issue I'm having when trying to implement this is that my only date field in the dip-related tables is this [fdDateTime]. I cannot compare it to itself, access is going to go in an endless loop (unless i can??). I've tried applying this by making a table called 'tblDates' where the only entries are the dates of the year, and then using these for the comparison. Whichever way i do the join, it doesn't seem to like what i'm doing, as it simply returns empty fields under the tblDates.DatesField column.

I'm lost, where are you trying to compare the date to itself? In the example I gave you, the date you're comparing it to is a user entered value that they're searching for. The confusion may be coming from my lack of sql knowledge. Basically you're using criteria to limit the search results to either the day the user wants, or the first entry before the date the user enters. Either way you're comparing [fdDateTime] to a user entered value, not itself

[MaxAndMin]

I'm gonna dumb down my DB a bit and apply your concepts. The way it's set up right now is a bit over my head & knowledge. Thanks for all the help. I'll get back you once i get it.