reference previous record in query

  • Thread starter Thread starter Joe Bohen
  • Start date Start date
J

Joe Bohen

I wish to reference the previous record in a query's
record set. I have seen this done using an alias of the
same record source joined on the key field, but have
forgotten the method used.

Any suggestions appreciated.

Joe
 
Here is an example of reference to the "previous" Record but it depends very
much on how you define "previous":

SELECT ( [Next].[Field1] + [Next].[Field2] ) / [Prev].[Field3]
AS Result
FROM [YourTable] AS Next, [YourTable] AS Prev
WHERE [Prev].[DateField] =
( SELECT Max([Sub].[DateField])
FROM [YourTable] AS Sub
WHERE [Sub].[DateField] < [Next].[DateField]
);
 
Thanks for your help I can see where your code is headed
but I fear I have not been clear with my intentions.
I am attempting to include an extra field in my query
which will list the invoice number of the previouse record
the query is sorted in invoice number order. Therefore if
the previouse record has the same invoice number it will
indicate that there is more than one detail line on the
invoice and I will be able to use the resulting extra
invoice field as a comparison in an if statement. I have
included the code for the query below.
Once again thanks for any help.

SELECT TRNLIST.INVNUM, TRNSTK.VAN, TRNSTK.SYSDATE,
TRNSTK.CUSTOMER, TRNSTK.INVDATE, TRNSTK.STCODE,
TRNSTK.PRDGRP, TRNLIST.TNETT, TRNSTK.DESCRIPN,
TRNSTK.QUANTITY, TRNSTK.COMPANY, TRNSTK.OLDADVNUM,
TRNSTK.DEPOT, TRNLIST.TSTART
FROM TRNSTK INNER JOIN TRNLIST ON (TRNSTK.OLDADVNUM =
TRNLIST.OLDADVNUM) AND (TRNSTK.INVNUM = TRNLIST.INVNUM)
WHERE (((TRNSTK.INVDATE)>=[forms]![frmdate_Depot_report]!
[txtstart] And (TRNSTK.INVDATE)<=[forms]!
[frmdate_Depot_report]![txtend]) AND ((TRNSTK.STCODE)
<>"fitterpay" And (TRNSTK.STCODE)<>"lump") AND
((TRNSTK.PRDGRP)<>"9b" And (TRNSTK.PRDGRP)<>"9a" And
(TRNSTK.PRDGRP) Not Like "9z"))
ORDER BY TRNLIST.INVNUM;
-----Original Message-----
Here is an example of reference to the "previous" Record but it depends very
much on how you define "previous":

SELECT ( [Next].[Field1] + [Next].[Field2] ) / [Prev]. [Field3]
AS Result
FROM [YourTable] AS Next, [YourTable] AS Prev
WHERE [Prev].[DateField] =
( SELECT Max([Sub].[DateField])
FROM [YourTable] AS Sub
WHERE [Sub].[DateField] < [Next].[DateField]
);

--
HTH
Van T. Dinh
MVP (Access)



Joe Bohen said:
I wish to reference the previous record in a query's
record set. I have seen this done using an alias of the
same record source joined on the key field, but have
forgotten the method used.

Any suggestions appreciated.

Joe
Click to expand...


.
Click to expand...
 
Sorry, can't write the SQL for you but if you want to get this, you will
need to use the self-join as per my example.

Alternatively, you may want to traverse the simple Recordset created from
the Query and compare value(s) from one row with values from the previous
row (saved in VBA variables).
 
Back
Top