Home   Fees/Services   Access Templates   Tutorials  Tutorial Downloads   Articles   Search   Contact  Privacy  Links
Parent Child Relationships
Detail-Master Query Example
Home > Tutorials > Query Tutorials > Child/Parent Query
 






Query Tutorials Index:
Too Complex Query
Aggregate Query
Not In Operator
Choose Function
Crosstab Query
Date Time Query
Delete Query
External Table Query
Report Filter Query
Group By Query
Having Query Clause
Histogram Query
Access Append Query
Master Detail Update
Order By Query
SQL Parameter Query
Predicate Query
Self Join Query
Scalar SubQuery
Master Detail Query
Select Query
Select Top
Union Query
SQL Update Query
Custom Query Function

 

MultiSelect Parameter Forms Query Tutorial


Access Bottom Up Query / Detail-Master Queries Examples



Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.

We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.


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







A Blue Claw Software Design Template:

Loan Process Management Access Database
 









A Blue Claw Database Design Article:

Microsoft Access On The Internet
 









Blue Claw Database Design Downloadable Tutorial:
Union Query (Advanced) Access Tutorial Download  








Microsoft Office:
MS Access 2000 Through Access 2016 & Office 365
 


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access Tutorial Home Page