Stuck: Comparing values from the same field at Different dates

[MaxAndMin]

Hi, I've looked around on google and this forum for an answer to this but I'm not sure I've been looking for the right things.

I'm in the process of converting a lot of our excel sheet "databases" into Access DBs. This perticular one is related to our volume of fuel in inventory.

In order to calculate consumption, I want to do the Volume at Day 2, minus the Volume at Day 1.

I've tried a few things but I'm falling short on the syntax I should be using. Also tried VBA but I'm short on creativity today it seems.


Graphically, this is the result i'd want under the calculated "consumption" field
Date Volume Consumption
1 100
2 80 20
3 30 50


Thank You in Advance!

Max

[June7]

Use subquery.

Review Microsoft Access tips: Subquery basics
Specifically the example for 'Get the value in another record'

[meratigoerr]

Let's assume:
1. Your table is normalized.
2. For the sake of this example we'll call it Fuel. We'll also assume the only two fields we care about are a data field [FuelDate] and the amount of fuel [FuelVolume]
3. The only days that we currently care about are today and yesterday.
4. The only thing we're looking for is the difference between the fuel volume today and the fuel volume yesterday.

This can be done in one query. You could also modify it to look at days other than today, or even longer time spans by using references to textboxes
in your forms rather than the simple date() function.

SELECT Sum(IIf([FuelDate]=Date(),[FuelVolume]*-1,IIf([FuelDate]=(Date()-1),[FuelVolume],"0"))) AS FuelUsage
FROM fuel;

[MaxAndMin]

Thank you very much June7 and Meratigoerr.

Subqueries solves a lot of issues all across the board, and simplifies a lot of the cascading queries I had going. Definitly worth becoming a master at it.

As for the FuelUsage formula, works wonders. Very logical. I'll program the likes of it as a function.

The project I signed up for is a bit beyond my beginer-intermediate skill level, but I'm learning!