In a query, show master records even if a child record doesn't exi

[mcduff22]

I have a master table of records that include clients and record creation
dates. I also have a subtable for documenting yearly review dates, comments
etc. for the record built in the master table - as the subtable will track
all review dates forever.

My query which requires both tables, will only pull master table records
that have a child record already created. I need the query to pull records
up for review based on the date created or last review date (whichever is
most recent). My first problem is that the record may not have been reviewed
yet (thus subtable record created) and not being pulled into the query. The
second problem is that I need the query to pull the master record based on
time span of creation date or last review date (whichever most recent).

 

[Jeff Boyce]

Open the query in design view.

Right-click on the line connecting parent to child table. Select the option
to show all parent records and any related child records.

Save the query and re-run.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer MVP]

You need two queries
QueryOne: returns must recent review date

SELECT Child.ClientID, Max(ReviewDate) as LastReview
FROM Child
GROUP BY ClientID

Now you can use that in a subsequent query

SELECT *
FROM Master LEFT JOIN QueryOne
ON Master.ClientID = QueryOne.ClientID
WHERE (QUeryONe.LastReview is Null and Master.CreateDate = #2009-01-01#)
OR QueryONe.LastReview = #2009-01-01#


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County