Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Get Value in Previous Record  

Version 2.0   03/12/2018

Click any image to view a larger version ...

Screenshots

Code Samples for Businesses, Schools & Personal Use

Return to Example Databases Page

This example was written in response to a couple of 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.

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:

GetPrevRecord-Table1 GetPrevRecord-Query1Design GetPrevRecord-QueryResult GetPrevRecord-Table2

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)

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 and code for the Serialize function

 

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:

GetPrevRecord-Query2Design GetPrevRecord-Query3Design

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: to obtain a rank order based on both the employeeID and the visit date, I first converted the date to a long integer and combined it with the employeeID

 

2 copies of this query are combined 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)