Create a new query using the customer, orders, product and order detail tables t

Question

Create a new query using the customer, orders, product and order detail tables that indicates which customers have purchased this coffee during Feb and March. How would I put that into the query?

Explanation / Answer

Subquery basics 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. What is a subquery? The SELECT query statement 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: 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.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.