Query Tutorials Index:
Access Bottom Up Query / Detail-Master Queries Examples
Are you just starting out on your quest for a small business database? Need some advice or someone to bounce ideas off of? We are happy to chat with you to discuss options to your situation.
Are you looking for an MS Access template database? We have many for sale in our Access Template and Examples Software section.
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:
MS Access 2000 Through Access 2016 & Office 365
Copyright 2000-2017 Blue Claw Database Design
Development in Microsoft Access, Microsoft SQL/Server and Azure
See our SQL/Server Development and Access Migration Tutorials