Inner join on two tables - with record offset (1 Viewer)

[IanH]

Hi all, wonder if one of you can help out there.

I have a query with two tables (in fact two copies of the same table) that are linked with an inner join on two fields:
RecordID - an autonumber and
LocalID - a text person identifier.

I need to join the two fields so that the localIDs are the same, but offsetting the two tables so that the IDs are not equal, but the RecordIDs are offset by one, so that RecordID with a value of 1 from table one will map to RecordID with a value of 2 from table two etc throughout the two tables.

I can see the code for linking the two tables where the RecordID and LocalID are equal (as pasted below):
SELECT Table1.Code, Table1.Name, Table1.Consultant, Table2.DATE, Sum((1)) AS Occurrences
FROM Table1 INNER JOIN Table2 ON (Table1.RecordID = Table2.RecordID) AND (Table2.LOCALID = Table2.LOCALID)
WHERE ((([Table2].[Startdate]-[Table1].[Enddate]) Between 1 And 28) AND ((Table2.EPISODE)=1) AND ((Table2.StartReason) Like "2*"))
GROUP BY Table1.Code, Table1.Name, Table1.Consultant, Table2.DATE;

But, can't see how to easily offset them. Any thoughts appreciated

Regards

 

[Pat Hartman]

Try the following modification and see if it gets you what you want.

SELECT Table1.Code, Table1.Name, Table1.Consultant, Table2.DATE, Sum((1)) AS Occurrences
FROM Table1 INNER JOIN Table2 ON (Table1.RecordID + 1 = Table2.RecordID) AND (Table2.LOCALID = Table2.LOCALID)
WHERE ((([Table2].[Startdate]-[Table1].[Enddate]) Between 1 And 28) AND ((Table2.EPISODE)=1) AND ((Table2.StartReason) Like "2*"))
GROUP BY Table1.Code, Table1.Name, Table1.Consultant, Table2.DATE;

 

[IanH]

Thought I'd already tried that and failed and then realised I had offset the wrong way on the wrong table and was getting screwy results.

Thanks very much

Ian