Query Tutorials Index:
Access Bottom Up Query / Detail-Master Queries Examples
We have dozens of downloadable Access databases for you to learn and examine.
We have expert enterprise-level database development capabilities for cloud/web-based databases through our partnership with IT Impact.
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
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:
Copyright 2000-2017 Blue Claw Database Design