link to previous record query

  • Thread starter Thread starter david epsom dot com dot au
  • Start date Start date
D

david epsom dot com dot au

I need a query to return linked newdate, oldate from a table of date
records.
Table like this:

1 2000-3-1
1 2000-4-1
2 2000-3-1
2 2000-4-1

Result like this:
1 2000-3-1 [null]
1 2000-4-1 2000-3-1
2 2000-3-1 [null]
2 2000-4-1 2000-3-1

I'm thinking, something like:

select mytable.mydate as newdate, oldate
from mytable join (select max mydate as oldate
from mytable as q where q.mydate < mytable.mydate)

Has anyone got a worked example of the syntax?
 
Try this:

SELECT M.MyDate, OldDate
FROM MyTable AS M
INNER JOIN (SELECT
Max(O.MyDate AS OldDate)
FROM MyTable AS O
WHERE O.MyDate < M.MyDate);
 
Just saw that I have a typo -- try this instead:

SELECT M.MyDate, OldDate
FROM MyTable AS M
INNER JOIN (SELECT
Max(O.MyDate) AS OldDate
FROM MyTable AS O
WHERE O.MyDate < M.MyDate);


--
Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Try this:

SELECT M.MyDate, OldDate
FROM MyTable AS M
INNER JOIN (SELECT
Max(O.MyDate AS OldDate)
FROM MyTable AS O
WHERE O.MyDate < M.MyDate);


--
Ken Snell
<MS ACCESS MVP>


david epsom dot com dot au said:
I need a query to return linked newdate, oldate from a table of date
records.
Table like this:

1 2000-3-1
1 2000-4-1
2 2000-3-1
2 2000-4-1

Result like this:
1 2000-3-1 [null]
1 2000-4-1 2000-3-1
2 2000-3-1 [null]
2 2000-4-1 2000-3-1

I'm thinking, something like:

select mytable.mydate as newdate, oldate
from mytable join (select max mydate as oldate
from mytable as q where q.mydate < mytable.mydate)

Has anyone got a worked example of the syntax?
Click to expand...
Click to expand...
 
had another shot: wound up with this :)

SELECT audit.*,
(select max(datChange) FROM tblDP_Audit where (tbldp_audit.datchange <
audit.datchange) and (tblDP_audit.idxRecord = audit.idxRecord)) AS OldDate

FROM tblDP_Audit AS audit;

Couldn't use /correlated/ subquery in from clause.

(david)

Ken Snell said:
Just saw that I have a typo -- try this instead:

SELECT M.MyDate, OldDate
FROM MyTable AS M
INNER JOIN (SELECT
Max(O.MyDate) AS OldDate
FROM MyTable AS O
WHERE O.MyDate < M.MyDate);


--
Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Try this:

SELECT M.MyDate, OldDate
FROM MyTable AS M
INNER JOIN (SELECT
Max(O.MyDate AS OldDate)
FROM MyTable AS O
WHERE O.MyDate < M.MyDate);


--
Ken Snell
<MS ACCESS MVP>


david epsom dot com dot au said:
I need a query to return linked newdate, oldate from a table of date
records.
Table like this:

1 2000-3-1
1 2000-4-1
2 2000-3-1
2 2000-4-1

Result like this:
1 2000-3-1 [null]
1 2000-4-1 2000-3-1
2 2000-3-1 [null]
2 2000-4-1 2000-3-1

I'm thinking, something like:

select mytable.mydate as newdate, oldate
from mytable join (select max mydate as oldate
from mytable as q where q.mydate < mytable.mydate)

Has anyone got a worked example of the syntax?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top