How to detect differencess/similarities between Rows in Tables

[billelev]

I have a table that stores date/value pairs. The dates are successive end-of-month values.

For each month, I need to know if
(1) a value is not present, but was in the previous month (e.g. a "sell")
(2) a value is present, but was not in the previous month (e.g. a "buy")
(3) a value was present for both months (e.g. a "keep" of that value)

e.g.

12/31/2014 A
12/31/2014 B
01/31/2015 A
01/31/2015 C

For date 1/31/2015

(1) B is not present, but was previously (e.g. a "sell")
(2) C is present, but wasn't previously (e.g. a "buy")
(3) A is present in this and the previous month (e.g. a "keep")

I have been able to achieve this, but I believe in a reasonably inefficient way. My method was:

(1) Create a query with date/value pairs, but date shifted forward by a month (using a VBA function): qryD2
(2) Perform separate inner/outer joins on the original table and qryD2, joining on the date fields.

e.g. qryD2

SELECT NextMonth([tblValues.dt]) AS dtNext, tblValues.Value
FROM tblValues;

e.g. to return the "sells"

SELECT qryD2.dtNext AS dt, qryD2.Value, 'S' as Type
FROM tblValuesRIGHT JOIN qryD2 ON (tblValues.dt = qryD2.dtNext) AND (tblValues.Value= qryD2.Value)
WHERE (((tblValues.Value) Is Null)) OR (((qryD2.Value) Is Null));

Is there a more efficient way to do this, or is this pretty much it?

[Rabbit]

That's the correct approach.