Access Bottom Up Query / Detail-Master Queries Examples
Select parent records based on the values in a child table – there are times when you need to retrieve master records based on criteria in a detail record. We will assume that you have two tables in a master detail relationship.The master is M_Employees and the detail is M_Attendance. These are linked by the Employee_ID field. Our goal is to find the name and number of the employees that have missed more than 5 days of work.
Select M_Employees.Name, M_Employees.Emp_Number
Where M_Employees.Employee_ID in
(Select Employee_ID from M_Attendance
Group By Employee_ID
Having Count(M_Attendance.Day_Missed) >= 5);
Hopefully you can see how to parameterize this detail-master query to select a variable number of missed days. Or, you could use the Choose command to select the count of a different field.
Another bottom up query example:
In this example with have two tables:
1) Customer Orders
2) Customer Order Items
We want to display all customer order records where at least one of the items in the order has not been shipped (shipped_date is null).
Here is the query which supplies the form with data:
SELECT M_Orders.Order_ID, M_Orders.Order_No, M_Orders.Order_Date, M_Orders.Customer FROM M_Orders WHERE (((M_Orders.Order_ID) In (select order_id from M_Order_items where isnull(date_shipped)=true)));
Below is a snapshot of the form displaying the data:
MS Access 2000 Through Access 2016 & Office 365
Development in Microsoft Access, Microsoft SQL/Server and Azure
Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016