Query Tutorials Index:
Access Bottom Up Query / Detail-Master Queries Examples
Since 2000 we have developed nearly 500 databases for clients throughout the world. See a sample of them at our recent clients page.
We also offer Microsoft SQL Server / Azure / Cloud database development through our programmer partnership with Jay McCormick and his team.
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-2018 Blue Claw Database Design
Development in Microsoft Access, Microsoft SQL/Server and Azure
See our SQL/Server Development and Access Migration Tutorials