if there were two meter readings on the same date. You get this error if it returns multiple values:Īt most one record can be returned by this subquery.Įven though we asked for TOP 1, Access will return multiple records if there is a tie, e.g. If the main query displays the result, the subquery must return a single value only.
The example uses Dupe for the duplicate table, but any name will do. Since there are two copies of the same table, you must alias one of them. The ORDER BY clause sorts by descending date, so the most recent record will be the first one. The subquery returns just one meter reading (TOP 1.) The WHERE clause limits it to the same address, and a previous date. 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. ORDER BY Dupe.ReadDate DESC, Dupe.ID) AS PriorValue WHERE Dupe.AddressID = MeterReading.AddressIDĪND Dupe.ReadDate < MeterReading.ReadDate How can they query that?Ī subquery can read another record in the same table, like this: The previous reading is a different record in the same table. Periodically, they read the meter at your house, and send a bill for the number of units used since the previous reading. Subqueries are useful for answering questions about what data exists or does not exist in a related table. The subquery does not have the Customers table in its FROM clause, yet it can refer to values in the main query. Subqueries are ideal for querying about data in other tables. The Orders table is not even in the main query. The subquery goes in brackets, without a semicolon of its own. If it finds any, the customer is excluded by the NOT EXISTS. The subquery finds any orders for that customer in the period. To decide whether to include the customer, it runs the subquery. When the main query runs, Access examines each record in the Customers table. 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. It is limited by the WHERE clause, which contains the subquery. The main query selects two fields (ID and Company) from the Customers table. WHERE Orders.CustomerID = Customers.CustomerID wants to hound customers who have not placed any orders in the last 90 days: Identifying what is NOT thereĪ sales rep.
WHAT IS MS ACCESS HOW TO
The best way to grasp subqueries is to look at examples of how to use them. There will be some tidying up to do, but that's the simplest way to create a subquery. 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. 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. Line endings and brackets are optional.Ī subquery is a SELECT query statement inside another query.Īs you drag fields and type expressions in query design, Access writes a sentence describing what you asked for. It returns 3 fields from 1 table, applies criteria, and sorts the results: