Access Query Table: Show value of previous row

  • Thread starter Thread starter Bryn Stevenson
  • Start date Start date
B

Bryn Stevenson

I have created QueryTable from MainTable showing data sorted by StartDateTime. Is there a formula or way of updating the added column QueryTable.PreviousEndDateTime with the value of QueryTable.EndDateTime from the previous row in the query for each record in the query?

The end result is to show the MainTable data in a form by start date/time order whilst alerting that the previous records end date/time field is different to the current records start date/time field. Records in MainTable may be in any order as it stores times on each job for different people.
 
One way is to add a calculated field. The first query is if all records are
to be treated together.
SELECT Column1, Column2, (SELECT COUNT(*)
FROM [YourTable-4] T1
WHERE T1.Column2 <= T.Column2) AS Rank
FROM [YourTable-4] AS T
ORDER BY Column1, Column2;

If you need grouping then use this query instead.
SELECT Q.Group, Q.Points, (SELECT COUNT(*) FROM Product Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.Points < Q.Points)+1 AS Rank
FROM Product AS Q
ORDER BY Q.Group, Q.Points;

Use the one that fits your situation. Then in another query design view
select the above query of choice twice. Access adds a sufix of '_1' to the
second instance of the query.
Join the two queries by clicking on the field names and draging to the
other. Then to see data from two consecutive records add the data field from
the '_1' query and the Rank field also. Use criteria on the '_1' Rank field
of [FirstQuery].[Rank]+1 or [FirstQuery].[Rank]-1 according to whether you
want to look forward or backwards.
 
Back
Top