using value from previous record
- Thread starter dennisr
- Start date
SteveSchapel
Registered User.
- Local time
- Tomorrow, 08:45
- Joined
- Apr 29, 2010
- Messages
- 242
Dennis,
You can use a subquery, or perhaps a domain aggregate function may also be applicable (depending on the details of what you want). You need to be cautions with both these approaches in the case of large sets of records, as it can get pretty clunky.
If you want more explicit help, let us know the ins and outs of what you want. Also need to define (in data terms) what you mean exactly by "previous record".
You can use a subquery, or perhaps a domain aggregate function may also be applicable (depending on the details of what you want). You need to be cautions with both these approaches in the case of large sets of records, as it can get pretty clunky.
If you want more explicit help, let us know the ins and outs of what you want. Also need to define (in data terms) what you mean exactly by "previous record".
Steve, thanks for replying. What I need in my database datasheet is basically a running balance for a selected recordset. The records are by date order and each one (row) shows product added or used for the day and an table field for manual adjustment. I need the day's current product balance. I know you can have reports give you running balances but I need the running balance in a query or form datasheet view so the user can make live adjustments. How would you make a form or query that would work like a simple excel worksheet where the previous balance +/- the row's changes = the new balance? Can DLookup be used with a record offset?
DoCmd.GoToRecord , , acNext
Dim rsClone As Recordset
Dim intValue As Integer
Set rsClone = Me.RecordsetClone
With rsClone
.Bookmark = Me.Bookmark
.MovePrevious
intValue = rsClone.Fields("D")
End With
Me.D = Me.A + Me.B + Me.C + intValue
rsClone.Close
Microsoft Access Tips for Serious Users
Provided by Allen Browne, March 2007. Updated February 2009.
Discovering subqueries is one of those "Eureka!" moments. A new landscape opens in front of you, and you can do really useful things such as:
This example shows basic SQL syntax.
It returns 3 fields from 1 table, applies criteria, and sorts the results:
SELECT CompanyID, Company, City
FROM Table1
WHERE (City = "Springfield")
ORDER BY Company;
The clauses must be in the right order. Line endings and brackets are optional.
A subquery is a SELECT query statement inside another query.
As you drag fields and type expressions in query design, Access writes a sentence describing what you asked for. The statement is in SQL (see'quell) - Structured Query Language - the most common relational database language, also used by MySQL, SQL Server, Oracle, DB2, FoxPro, dBase, and others.
If SQL is a foreign language, you can mock up a query like the subquery you need, switch it to SQL View, copy, and paste into SQL View in your main query. There will be some tidying up to do, but that's the simplest way to create a subquery.
Subquery examples
The best way to grasp subqueries is to look at examples of how to use them.
Identifying what is NOT there
A sales rep. wants to hound customers who have not placed any orders in the last 90 days:
SELECT Customers.ID, Customers.Company
FROM Customers
WHERE NOT EXISTS
(SELECT Orders.OrderID
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
AND Orders.OrderDate > Date() - 90) ;
The main query selects two fields (ID and Company) from the Customers table. It is limited by the WHERE clause, which contains the subquery.
The subquery (everything inside the brackets) selects Order ID from the Orders table, limited by two criteria: it has to be the same customer as the one being considered in the main query, and the Order Date has to be in the last 90 days.
When the main query runs, Access examines each record in the Customers table. To decide whether to include the customer, it runs the subquery. The subquery finds any orders for that customer in the period. If it finds any, the customer is excluded by the NOT EXISTS.
Points to note:
Periodically, they read the meter at your house, and send a bill for the number of units used since the previous reading. The previous reading is a different record in the same table. How can they query that?
A subquery can read another record in the same table, like this:
SELECT MeterReading.ID,
MeterReading.ReadDate,
MeterReading.MeterValue,
(SELECT TOP 1 Dupe.MeterValue
FROM MeterReading AS Dupe
WHERE Dupe.AddressID = MeterReading.AddressID
AND Dupe.ReadDate < MeterReading.ReadDate
ORDER BY Dupe.ReadDate DESC, Dupe.ID) AS PriorValue
FROM MeterReading;
The main query here contains 4 fields: the primary key, the reading date, the meter value at that date, and a fourth field that is the value returned from the subquery.
The subquery returns just one meter reading (TOP 1.) The WHERE clause limits it to the same address, and a previous date. The ORDER BY clause sorts by descending date, so the most recent record will be the first one.
Points to note:
You want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query:
SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
(SELECT TOP 3 OrderID
FROM Orders AS Dupe
WHERE Dupe.CustomerID = Orders.CustomerID
ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;
Points to note:
A Totals query easily gives you a total for the current month, but to get a year-to-date total or a total from the same month last year means another calculation from the same table but for a different period. A subquery is ideal for this purpose.
SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS MonthAmount,
(SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD
FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID = OD.OrderID
WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1)
AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]),
Month([Orders].[OrderDate]) + 1, 1)) AS YTDAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);
Points to note:
The Unmatched Query Wizard (first dialog when you create a new query) can help you identify records in one table that have no records in another. But if you try to delete the unmatched records, Access may respond with, Could not delete from specified tables.
An alternative approach is to use a subquery to identify the records in the related table that have no match in the main table. This example deletes any records in tblInvoice that have no matching record in the tblInvoiceDetail table:
DELETE FROM tblInvoice
WHERE NOT EXISTS
(SELECT InvoiceID
FROM tblInvoiceDetail
WHERE tblInvoiceDetail.InvoiceID = tblInvoice.InvoiceID);
Delete duplicate records
This example uses a subquery to de-duplicate a table. "Duplicate" is defined as records that have the same values in Surname and FirstName. We keep the one that has the lowest primary key value (field ID.)
DELETE FROM Table1
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe
WHERE (Dupe.Surname = Table1.Surname)
AND (Dupe.FirstName = Table1.FirstName));
Nulls don't match each other, so if you want to treat pairs of Nulls as duplicates, use this approach:
DELETE FROM Table1
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe
WHERE ((Dupe.Surname = Table1.Surname)
OR (Dupe.Surname Is Null AND Table1.Surname Is Null))
AND ((Dupe.FirstName = Table1.FirstName)
OR (Dupe.FirstName Is Null AND Table1.FirstName Is Null)));
Aggregation: Counts and totals
Instead of creating a query into another query, you can summarize data with a subquery.
This example works with Northwind, to show how many distinct clients bought each product:
SELECT Products.ProductID, Products.ProductName, Count(Q.CustomerID) AS HowManyCustomers
FROM
(SELECT DISTINCT ProductID, CustomerID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) AS Q
INNER JOIN Products ON Q.ProductID = Products.ProductID
GROUP BY Products.ProductID, Products.ProductName;
Points to note:
Since subqueries can look up tables that are not in the main query, they are very useful for filtering forms and reports.
A Filter or WhereCondition is just a WHERE clause. A WHERE clause can contain a subquery. So, you can use a subquery to filter a form or report. You now have a way to filter a form or report on fields in tables that are not even in the RecordSource!
In our first example, the main query used only the Customers table, and the subquery filtered it to those who had no orders in the last 90 days. You could filter the Customers form in exactly the same way:
'Create a subquery as a filter string.
strWhere = "NOT EXISTS (SELECT Orders.OrderID FROM Orders " & _
"WHERE (Orders.CustomerID = Customers.CustomerID) AND (Orders.OrderDate > Date() - 90))"
'Apply the string as the filter of the form that has only the Customers table.
Forms!Customers.Filter = strWhere
Forms!Cusomters.FilterOn = True
'Or, use the string to filter a report that has only the Customers table.
DoCmd.OpenReport "Customers", acViewPreview, , strWhere
This technique opens the door for writing incredibly powerful searches. Add subqueries to the basic techniques explained in the article, and you can offer a search where the user can select criteria based on any related table in the whole database.
The screenshot below is to whet your appetite for how you can use subqueries. The form is unbound, with each tab collecting criteria that will be applied against related tables. The final RESULTS tab offers to launch several reports which don't even have those tables. It does this by dynamically generating a huge WhereCondition string that consists of several subqueries. The reports are filtered by the subqueries in the string.
Inserting the value from the previous record in Access
Applies to
Microsoft Access 97 and 2000
In Access, you can insert the value from the same field in the previous record when you create a new record. For example, you can copy the supplier name from the previous record into the Supplier field of a new record. You can insert the value manually by using the CTRL+APOSTROPHE (') key combination. You can also insert the value automatically by using a DLookup function in an expression.
Using the Keyboard
Use the keyboard if the number of records you want to fill is relatively small.
To insert the value from the same field in the previous record by using the keyboard
1. Open a form in Form or Datasheet view, a query in Datasheet view, or a report in Print Preview and scroll to a new record.
2. Click the field in which you want to insert the value.
3. Press CTRL+APOSTROPHE (').
Using the DLookup Function in an Expression
Use the DLookup function if you want to fill a large number of records.
Note This technique assumes that you have a table with an ID field that has a Number data type, and the ID values are not missing any number in sequential order.
To insert the value from the same field in the previous record on a form
1. Open the form in Design view.
2. Right-click the control where you want to insert the value, and then type the following text in the ControlSource property box for that control, substituting your own values for FieldName, TableName, and FormName:
=DLookup("[FieldName]","TableName","[ID]=Forms![FormName]![ID]-1")
To insert the value from the same field in the previous record in a report
1. Open the report in Design view.
2. Right-click the control where you want to enter the value, and then type the following text in the ControlSource property box for that control, substituting your own values for FieldName, TableName, and ReportName:
=DLookup("[FieldName]","TableName","[ID]=Reports![ReportName]![ID]-1")
To insert the value from the same field in the previous record in a query
1. Open the query in Design view.
2. In the Field row of the query design grid, type the following text, substituting your own values for FieldName and TableName:
Expr1: DLookup("[FieldName]","TableName","[ID]=" & [ID]-1)
Dim rsClone As Recordset
Dim intValue As Integer
Set rsClone = Me.RecordsetClone
With rsClone
.Bookmark = Me.Bookmark
.MovePrevious
intValue = rsClone.Fields("D")
End With
Me.D = Me.A + Me.B + Me.C + intValue
rsClone.Close
Microsoft Access Tips for Serious Users
Provided by Allen Browne, March 2007. Updated February 2009.
Discovering subqueries is one of those "Eureka!" moments. A new landscape opens in front of you, and you can do really useful things such as:
- Read a value from the previous or next record in a table.
- Select just the TOP (or most recent) 5 scores per client.
- Choose the people who have not paid/ordered/enrolled in a period.
- Express a value as a percentage of the total.
- Avoid inflated totals where a record is repeated (due to multiple related records.)
- Filter or calculate values from other tables that are not even in the query.
The SELECT query statement
It returns 3 fields from 1 table, applies criteria, and sorts the results:
SELECT CompanyID, Company, City
FROM Table1
WHERE (City = "Springfield")
ORDER BY Company;
The clauses must be in the right order. Line endings and brackets are optional.
A subquery is a SELECT query statement inside another query.
As you drag fields and type expressions in query design, Access writes a sentence describing what you asked for. The statement is in SQL (see'quell) - Structured Query Language - the most common relational database language, also used by MySQL, SQL Server, Oracle, DB2, FoxPro, dBase, and others.
If SQL is a foreign language, you can mock up a query like the subquery you need, switch it to SQL View, copy, and paste into SQL View in your main query. There will be some tidying up to do, but that's the simplest way to create a subquery.
Subquery examples
The best way to grasp subqueries is to look at examples of how to use them.
Identifying what is NOT there
A sales rep. wants to hound customers who have not placed any orders in the last 90 days:
SELECT Customers.ID, Customers.Company
FROM Customers
WHERE NOT EXISTS
(SELECT Orders.OrderID
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID
AND Orders.OrderDate > Date() - 90) ;
The main query selects two fields (ID and Company) from the Customers table. It is limited by the WHERE clause, which contains the subquery.
The subquery (everything inside the brackets) selects Order ID from the Orders table, limited by two criteria: it has to be the same customer as the one being considered in the main query, and the Order Date has to be in the last 90 days.
When the main query runs, Access examines each record in the Customers table. To decide whether to include the customer, it runs the subquery. The subquery finds any orders for that customer in the period. If it finds any, the customer is excluded by the NOT EXISTS.
Points to note:
- The subquery goes in brackets, without a semicolon of its own.
- The Orders table is not even in the main query. Subqueries are ideal for querying about data in other tables.
- The subquery does not have the Customers table in its FROM clause, yet it can refer to values in the main query.
- Subqueries are useful for answering questions about what data exists or does not exist in a related table.
Periodically, they read the meter at your house, and send a bill for the number of units used since the previous reading. The previous reading is a different record in the same table. How can they query that?
A subquery can read another record in the same table, like this:
SELECT MeterReading.ID,
MeterReading.ReadDate,
MeterReading.MeterValue,
(SELECT TOP 1 Dupe.MeterValue
FROM MeterReading AS Dupe
WHERE Dupe.AddressID = MeterReading.AddressID
AND Dupe.ReadDate < MeterReading.ReadDate
ORDER BY Dupe.ReadDate DESC, Dupe.ID) AS PriorValue
FROM MeterReading;
The main query here contains 4 fields: the primary key, the reading date, the meter value at that date, and a fourth field that is the value returned from the subquery.
The subquery returns just one meter reading (TOP 1.) The WHERE clause limits it to the same address, and a previous date. The ORDER BY clause sorts by descending date, so the most recent record will be the first one.
Points to note:
- Since there are two copies of the same table, you must alias one of them. The example uses Dupe for the duplicate table, but any name will do.
- If the main query displays the result, the subquery must return a single value only. You get this error if it returns multiple values:
At most one record can be returned by this subquery. - Even though we asked for TOP 1, Access will return multiple records if there is a tie, e.g. if there were two meter readings on the same date. Include the primary key in the ORDER BY clause to ensure it can decide which one to return if there are equal values.
- The main query will be read-only (not editable.) That is always the case when the subquery shows a value in the main query (i.e. when the subquery is in the SELECT clause of the main query.)
You want the three most recent orders for each client. Use a subquery to select the 3 top orders per client, and use it to limit which orders are selected in the main query:
SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
(SELECT TOP 3 OrderID
FROM Orders AS Dupe
WHERE Dupe.CustomerID = Orders.CustomerID
ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC)
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;
Points to note:
- Since we have two copies of the same table, we need the alias.
- Like EXISTS in the first example above, there is no problem with the subquery returning multiple records. The main query does not have to show any value from the subquery.
- Adding the primary key field to the ORDER BY clause differentiates between tied values.
A Totals query easily gives you a total for the current month, but to get a year-to-date total or a total from the same month last year means another calculation from the same table but for a different period. A subquery is ideal for this purpose.
SELECT Year([Orders].[OrderDate]) AS TheYear,
Month([Orders].[OrderDate]) AS TheMonth,
Sum([Order Details].[Quantity]*[Order Details].[UnitPrice]) AS MonthAmount,
(SELECT Sum(OD.Quantity * OD.UnitPrice) AS YTD
FROM Orders AS A INNER JOIN [Order Details] AS OD ON A.OrderID = OD.OrderID
WHERE A.OrderDate >= DateSerial(Year([Orders].[OrderDate]),1,1)
AND A.OrderDate < DateSerial(Year([Orders].[OrderDate]),
Month([Orders].[OrderDate]) + 1, 1)) AS YTDAmount
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Year([Orders].[OrderDate]), Month([Orders].[OrderDate]);
Points to note:
- The subquery uses the same tables, so aliases them as A (for Orders) and OD (for Order Details.)
- The date criteria are designed so you can easily modify them for financial years rather than calendar years.
- Even with several thousand records in Order Details, the query runs instantaneously.
The Unmatched Query Wizard (first dialog when you create a new query) can help you identify records in one table that have no records in another. But if you try to delete the unmatched records, Access may respond with, Could not delete from specified tables.
An alternative approach is to use a subquery to identify the records in the related table that have no match in the main table. This example deletes any records in tblInvoice that have no matching record in the tblInvoiceDetail table:
DELETE FROM tblInvoice
WHERE NOT EXISTS
(SELECT InvoiceID
FROM tblInvoiceDetail
WHERE tblInvoiceDetail.InvoiceID = tblInvoice.InvoiceID);
Delete duplicate records
This example uses a subquery to de-duplicate a table. "Duplicate" is defined as records that have the same values in Surname and FirstName. We keep the one that has the lowest primary key value (field ID.)
DELETE FROM Table1
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe
WHERE (Dupe.Surname = Table1.Surname)
AND (Dupe.FirstName = Table1.FirstName));
Nulls don't match each other, so if you want to treat pairs of Nulls as duplicates, use this approach:
DELETE FROM Table1
WHERE ID <> (SELECT Min(ID) AS MinOfID FROM Table1 AS Dupe
WHERE ((Dupe.Surname = Table1.Surname)
OR (Dupe.Surname Is Null AND Table1.Surname Is Null))
AND ((Dupe.FirstName = Table1.FirstName)
OR (Dupe.FirstName Is Null AND Table1.FirstName Is Null)));
Aggregation: Counts and totals
Instead of creating a query into another query, you can summarize data with a subquery.
This example works with Northwind, to show how many distinct clients bought each product:
SELECT Products.ProductID, Products.ProductName, Count(Q.CustomerID) AS HowManyCustomers
FROM
(SELECT DISTINCT ProductID, CustomerID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) AS Q
INNER JOIN Products ON Q.ProductID = Products.ProductID
GROUP BY Products.ProductID, Products.ProductName;
Points to note:
- The subquery is in the FROM clause, where it easily replaces another saved query.
- The subquery in the FROM clause can return multiple fields.
- The entire subquery is aliased (as Q in this example), so the main query can refer to (and aggregate) its fields.
- Requires Access 2000 or later.
Since subqueries can look up tables that are not in the main query, they are very useful for filtering forms and reports.
A Filter or WhereCondition is just a WHERE clause. A WHERE clause can contain a subquery. So, you can use a subquery to filter a form or report. You now have a way to filter a form or report on fields in tables that are not even in the RecordSource!
In our first example, the main query used only the Customers table, and the subquery filtered it to those who had no orders in the last 90 days. You could filter the Customers form in exactly the same way:
'Create a subquery as a filter string.
strWhere = "NOT EXISTS (SELECT Orders.OrderID FROM Orders " & _
"WHERE (Orders.CustomerID = Customers.CustomerID) AND (Orders.OrderDate > Date() - 90))"
'Apply the string as the filter of the form that has only the Customers table.
Forms!Customers.Filter = strWhere
Forms!Cusomters.FilterOn = True
'Or, use the string to filter a report that has only the Customers table.
DoCmd.OpenReport "Customers", acViewPreview, , strWhere
This technique opens the door for writing incredibly powerful searches. Add subqueries to the basic techniques explained in the article, and you can offer a search where the user can select criteria based on any related table in the whole database.
The screenshot below is to whet your appetite for how you can use subqueries. The form is unbound, with each tab collecting criteria that will be applied against related tables. The final RESULTS tab offers to launch several reports which don't even have those tables. It does this by dynamically generating a huge WhereCondition string that consists of several subqueries. The reports are filtered by the subqueries in the string.
Inserting the value from the previous record in Access
Applies to
Microsoft Access 97 and 2000
In Access, you can insert the value from the same field in the previous record when you create a new record. For example, you can copy the supplier name from the previous record into the Supplier field of a new record. You can insert the value manually by using the CTRL+APOSTROPHE (') key combination. You can also insert the value automatically by using a DLookup function in an expression.
Using the Keyboard
Use the keyboard if the number of records you want to fill is relatively small.
To insert the value from the same field in the previous record by using the keyboard
1. Open a form in Form or Datasheet view, a query in Datasheet view, or a report in Print Preview and scroll to a new record.
2. Click the field in which you want to insert the value.
3. Press CTRL+APOSTROPHE (').
Using the DLookup Function in an Expression
Use the DLookup function if you want to fill a large number of records.
Note This technique assumes that you have a table with an ID field that has a Number data type, and the ID values are not missing any number in sequential order.
To insert the value from the same field in the previous record on a form
1. Open the form in Design view.
2. Right-click the control where you want to insert the value, and then type the following text in the ControlSource property box for that control, substituting your own values for FieldName, TableName, and FormName:
=DLookup("[FieldName]","TableName","[ID]=Forms![FormName]![ID]-1")
To insert the value from the same field in the previous record in a report
1. Open the report in Design view.
2. Right-click the control where you want to enter the value, and then type the following text in the ControlSource property box for that control, substituting your own values for FieldName, TableName, and ReportName:
=DLookup("[FieldName]","TableName","[ID]=Reports![ReportName]![ID]-1")
To insert the value from the same field in the previous record in a query
1. Open the query in Design view.
2. In the Field row of the query design grid, type the following text, substituting your own values for FieldName and TableName:
Expr1: DLookup("[FieldName]","TableName","[ID]=" & [ID]-1)
