Getting the max date after filtering out the deleted ones

[Guest]

Hello

i have a little problem. my tblServiceReports has 3 fields that i need to
manipulate.

the fields are ProductID, ServiceReportDate and SRDateDeleted

i also have a ServiceReportID field as the key for the table. each product
in my db can have one to many service reports. what i am trying to do is
create a query that pulls the max date for the service report so that i can
show the last time the product was serviced.

there is a catch, cause i know you think that what i just asked how to do in
the last paragraph was easy. i also need to filter out bad data. what? filter
BAD data, why not just delete it? because i am unsure if it is bad data, and
there are so many relationship affected that i am going to have to spend a
lot of time writing a sql query to remove it all properly from the db.

so, for now i hide the data. the field SRdatedeleted needs to be null in
order for it to be considered good data. so in my max records, anything that
doesnt have a null SRDateDeleted needs to be taken out first.

any clues?

 

[John Spencer]

SELECT ProductID, Max(ServiceReportDate) as LastService
FROM tblServiceReports
WHERE SRDateDeleted is Null
GROUP BY ProductId

In the Design view
-- Add your table
-- Add the three fields
-- Select View: Totals from the menu
-- Change Group By to Max under ServiceReportDate
-- Change Group By to WHERE under SRDateDeleted
-- Enter Is Null as criteria under SRDateDeleted


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