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
From M_Employees
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: