Max Date Query w/ Nulls

[stephen.belyea]

I've got a situation where I need to run a query that pulls data from
two different (but related) tables, and then lists the most recent
date for each entry in the second table.

The two tables and fields are

tblProperty
tblProperty.PropSiteName
tblProperty.PropStreet

tblInspection
tblInspection.InspDate

Each Property can have multiple inspection dates.

My goal is to have a report that provides the PropSiteName, PropStreet
and InspDate, with the information sorted by the InspDate that also
shows the null records (properties that have never been inspected).

Is there a way to do this with criteria and multiple queries? Or would
I have to use SQL (which I have no experience with)?

Thanks in advance!

((I've browsed the newsgroup history and read similar threads to this
one, but I am unable to understand how to create the SQL queries
mentioned in them or how to modify them to fit mine, a situation where
I have 2 different tables))

 

[Jaybird]

If I understand you correctly, you simply want the results of the query with
the latest Inspection Date, right? To do this, open your query in design
mode, select the "sigma" (Totals) button on the toolbar. This will cause an
extra row to appear on the design grid called "Total". The default setting
for this is "Group By", but if you go to the field that you want to show the
maximum values for and select "max" from the drop down menu on the "Total"
row and then run your query, you should only get the maximum values for that
field.

 

[John Spencer]

--Open a new query
--Add your two tables
-- select the three fields you want to show
-- double click on the line joining the two tables and select the option
that says show all tblProperty and only matching tblInspection
-- Select View: Totals from the menu bar
-- Change Group by to Max under the Inspdate field

Run the query. If this doesn't work, switch to the sql view and post the
sql of your query, so we can make more educated guesses.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

I've got a situation where I need to run a query that pulls data from
two different (but related) tables, and then lists the most recent
date for each entry in the second table.

The two tables and fields are

tblProperty
tblProperty.PropSiteName
tblProperty.PropStreet

tblInspection
tblInspection.InspDate

You'll need an Outer Join to a query selecting the max date. I presume that
tblInspection has some field (PropSiteName?) which links the two tables; if so
create and save a query

qryInspectionLatest
SELECT tblInspection.PropSiteName, Max(tblInspection.InspDate) AS
LatestInspDate
FROM tblInspection
GROUP BY PropSiteName;

Then join *this* query to tblProperty:

SELECT tblProperty.PropSiteName, tblProperty.PropStreet,
qryInspectionLatest.LatestInspDate
FROM tblProperty
LEFT JOIN qryInspectionLatest
ON qryInspectionLatest.PropSiteName = tblProperty.PropSiteName;

John W. Vinson [MVP]