difference of 2 records in a table per group (1 Viewer)

Hi,

I have a table with following fields / data:

Year ; district ; type ; value
2000 ; 00001 ; A ; 10000
2000 ; 00001 ; B ; 5000

2000 ; 00002 ; A ; 20000
2000 ; 00002 ; B ; 10000

2001 ; 00001 ; A ; 15000
2001 ; 00001 ; B ; 10000

2001 ; 00002 ; A ; 25000
2001 ; 00002 ; B ; 20000


What I need is a query that makes for each group "Year / district" the difference between A and B and define that as C

So the result of the query would be:

Year ; district ; type ; value
2000 ; 00001 ; C ; 5000
2000 ; 00002 ; C ; 10000
2001 ; 00001 ; C ; 5000
2001 ; 00002 ; C ; 5000

I can do this by doing a transpose first of the original table which gives me a column A en B so I can easily calculate the difference per record.

I would like to do it without a transpose because that's a little cumbersome.
Instead, I would like to do it directly in SQL, however I can't directly see how to do it. Can someone please direct me in the right direction?

bring your table through twice into a query, join them on year and district, then filter the first one on type=A and the second type=B. Something like


Note year, type and value are all reserved words, using them as field names can cause errors, often with misleading error descriptions

Thank you CJ
I've tried the code, but I think there something missing and I can't see what.
I would expect less records than the orginal table, and it isn't the case.

Also, I suppose I have to add a where clause somewhere with type = A and type = B or something like that to exclude other possibilities (for example if type = F)

I've attached my test-file. Should you have a little moment, I would appreciate it a lot if you could have an eye on it.

copy this code, it should work

Thank you, when I see it like this, it is a quiet simple solution.