Simple Query question 1

[ptrifile]

I have a simple query that has 4 fields, they are as follows:

Number
type
maxofdate
numberofdays - which is a calculated field to show how many days has passed since the record was last updated.

I have a continuous form that uses this query and all works fine except when the "type" changes from one update to the next.

There are 2 "types"...an A type and a B type. Lets say i have the following in my table:

104 A 1/20/2011 5
110 A 1/20/2011 5
104 B 1/25/2011 0
110 A 1/25/2011 0


Using the "MaxOfDate" i would expect the output on the form to be:

110 A 1/25/2011
104 B 1/25/2011

But instead it seems because the type is B the result is

104 A 1/20/2011
110 A 1/20/2011
104 B 1/25/2011

Regardless of what the "type" is i want to only show the date last updated.

I hope this made sense, can anyone help?

Paul

 

[PHV]

SELECT A.Number, A.type, A.Date
FROM yourTable A INNER JOIN (
SELECT [Number], MAX([Date]) AS MaxOfDate FROM yourTable GROUP BY [Number]
) M ON A.Number = M.Number AND A.Date = M.MaxOfDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ptrifile]

PHV, thank you, This worked great but i forgot to add in the output that i also need the number of days since the last update....before your post i was using todays date minus the max of the updated date.....how can i add this in to your solution?

thanks again for the help!

Paul

 

[PHV]

SELECT A.Number, A.type, A.Date[!], Date()-A.Date AS numberofdays[/!]
FROM yourTable A INNER JOIN (
SELECT [Number], MAX([Date]) AS MaxOfDate FROM yourTable GROUP BY [Number]
) M ON A.Number = M.Number AND A.Date = M.MaxOfDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886