Query doing calculations against different records in same table

[thethack]

I have a single table with multiple rows of data for each entity (total of 8 records per entity - 4 for one fiscal year and 4 for the subsequent fiscal year). Here is the table structure:

EntityName (Text Field)
EntityConcentrator (Text Field)
Value (Number Field)
FiscalYear (Text Field)

There are 8 records for each entitiy in the EntityName field(4 for each of 2 fiscal years).
There are 4 different concentrators in the EntityConcentrator field for each fiscal year (Each fiscal year has the same four concentrators in separate records).
Value field is different for each record.
There are 2 different years in the FiscalYear field.

When the EntityName and the EntityConcentrator are the same in different records in the table (this condition will limit to no more than two records from the table), I want to subtract the Value field of the record with the older FiscalYear from the Value field of the record with the newer FiscalYear.

Thank you for your assistance.

 

[Brianwarnock]

Briefly as I am about to finish for today this will require 5 queries

1 and 2 will group on EntityName and EntityConcentrator and 1 will select max and 2 min in FiscalYear

3 and 4 will join these to the table on all 3 fields and select the first 2 fields and the value.

5 will join 3 and 4 on the first 2 fields and do the calculation on the value

Hope this helps

Brian