Version 2.0           Last Updated 3 Dec 2018                 Approx 0.5 MB


This example was written in response to several questions at various Access forums.
For example, Special Calculation Query by AccessForums.net member ArvinFx

This was a fairly typical example where a user wanted to calculate the difference between values in the current record with those in the previous record.
For example for calculating energy consumption between meter readings.

A common solution to this type of problem is to use subqueries.
Allen Browne has an article using that approach - see Get the value in another record section on his subqueries page.

This article demonstrates a different approach and is based on the data in the forum post.
The data in Table1 shows the visit dates for 2 employees


The requirement was to count the number of days between each employee visit (as shown in Query1)


The method used here is to create a query and add the table to the query twice with a self-join.
The second copy will be shown as e.g. Table1_1
Link the two by employeeID and add the employeeID field to the query
Now add other fields & criteria as shown below:


The filter criteria provides a link between the current and previous records for each employee.

The query SQL is:

SELECT Table1.employee_ID, [Table1].[Visit_date]-[Table1_1].[Visit_Date] AS DaysBetweenVisit
FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.employee_ID = Table1_1.employee_ID
WHERE (((Table1.ID)=[Table1_1].[ID]+1));

This setup works perfectly for this simple scenario.
However, it will fail if all the records for each employee are not added in sequence

In this second example, the order has been deliberately jumbled (Table2)



NOTE:
A solution is also possible using the same method but utilising the Serialize function to create a ‘rank order’ query.
Click this link for more details about the Serialize function and all required code.

Create a new query (Query2) and sort the data by employeeID & visit date.
Add a rank order field then use that as the reference field in place of the ID field:


The ranking query SQL is:

SELECT Serialize("Query2","EmpVisitDate",[EmpVisitDate]) AS Rank, Table2.employee_ID, Table2.Visit_date,    
      [employee_ID] & CLng([Visit_date]) AS EmpVisitDate
FROM Table2
ORDER BY Table2.employee_ID, Table2.Visit_date;

NOTE:
I first converted the date to a long integer and combined it with the employeeID to obtain a rank order based on both the employeeID and the visit date,

Two copies of this query are combined with a self-join in a very similar way to that used previously (Query3)


This final query SQL is:

SELECT Query2.employee_ID, [Query2].[Visit_date]-[Query2_1].[Visit_Date] AS DaysBetweenVisit
FROM Query2 INNER JOIN Query2 AS Query2_1 ON Query2.employee_ID = Query2_1.employee_ID
WHERE (((Query2.Rank)=[Query2_1].[Rank]+1));

This type of solution will work for many examples of this type.
It runs faster than a subquery & is more transferable than creating a user defined function for the task.

Click to download the example database used for this article:       GetPrevRecord v2          Approx 0.5 MB (zipped)



Colin Riddington           Mendip Data Systems                 Last Updated 3 Dec 2018

Return to Code Samples Page

Return to Top