Home  Fees/Services  Access Templates  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Query Examples
MS Access SQL Support & Training
Detail-Master Query Example

SQL Parent Child Relationships Queries Microsoft Access 2007 Query Help
Professional Microsoft Access Programmers

Access Template Databases:


MS Query Tutorials:

Aggregate Functions
Choose Function
More Choose Functions
Crosstab Query Tutorial
Crosstab Query Advanced
DateTime Query
Delete Query Tutorial
External Link Query
Access Report Filter Query
Group By Clause
Access Having Clause
Histogram Query Tutorial
Insert Into Query
Master/Detail Updates
Order By Dynamic
Access Parameter Query
Predicate Query Example
Self Join Query Tutorial
Scalar Query Example
Child/Parent Table Query
Select Statement Tutorial
Select Top 1 Query
Union Query Example
Access Update Query

Multi-Select Parameter Forms Query Tutorial


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:

bottom up queries


Contact Information

Microsoft Access Developer

Microsoft Access Query 2007 2003 2000 Access Query Tutorial