Newbie Question

[Andibevan]

Hi All,

I have used all the other MS applications to a reasonably advanced level but
have never needed to delve into Access very much. As such I would really
appreciate some guidance on how to set-up the following query.

I will simplify my situation for the purposes of my quesion - it relates to
a defect tracking database.
I wish to create a query that will produce a list of all open defects and
then count them by their severity (4 levels)

The severity is taken from table 1 - this is easy as table 1 has only 1
record for each defect so this is a simple query. The status is taken from
table 2 where each time the status of a defect changes a new entry is put
into table 2 with a time-stamp.

My query needs to return the most recent status from table 2 against each
individual defect from table 1 where the status is open.

Hope this makes sense - I would really appreciate either a solution or just
some pointers or URL's for a good guide to help me on my way.

Thanks

Andi

 

[John Spencer (MVP)]

A query and sub-queries might do it.

SELECT T0.Severity, Count(T0.Severity) as Countem
FROM Table1 as TO
WHERE T0.DefectID in (
SELECT T1.DefectID
FROM Table2 as T1
WHERE T1.StatusDate =
(SELECT Max(T2.StatusDate)
FROM Table2 as T2
WHERE T2.DefectID = T1.DefectID)
AND T1.Status = "OPEN")
GROUP BY T0.Severity

Easier method would be three nested queries.

QueryOne (saved as queryOne)
SELECT DefectId, Max(StatusDate) as LastDate
FROM Table2
GROUP BY DefectID

QueryTwo (Saved as queryTwo)
SELECT TableTwo.DefectID
FROM QueryOne INNER JOIN TableTwo
ON QueryOne.DefectID = TableTwo.DefectID
AND QueryOne.LastDate = TableTwo.StatusDate
WHERE Status = "Open"

QueryThree
SELECT Severity, Count(Severity) as Countem
FROM TableOne INNER JOIN QueryTwo