Filter by latest date! 3

[netrusher]

Below is the code I have for a query. This query shows
Processes, Operations, etc. Each Operation can have
duplicate entry's due to Multiple Effective Dates
(Effective_). I would only like to see the records
that match the latest Effective_ (date). If Operation
START-UP is listed three times due to three effective
dates 2007-01-04, 2007-01-08 & 2007-01-17 I would only
like to the the one associated with the 2007-01-17. Is
there a way to accomplish this in my query? I had
something like this earlier in the year and the query had
joins. This query has no joins and I do not know how to
modify the earlier posting to make it work for this Query.

SELECT [4-5TPROC].PROCESS, [4-5TPROC].OPERATION, [4-5TPROC].OPERATION_, [4-5TPROC].EFFECTIVE_
FROM [4-5TPROC]
ORDER BY [4-5TPROC].PROCESS, [4-5TPROC].OPERATION;

 

[SkipVought]

Hi,

"I would only like to see the records
that match the latest Effective_ (date). "

SELECT [4-5TPROC].PROCESS, [4-5TPROC].OPERATION, [4-5TPROC].OPERATION_, [4-5TPROC].EFFECTIVE_
FROM [4-5TPROC][b]
where CLng([4-5TPROC].EFFECTIVE)_ = Date()[/b]
ORDER BY [4-5TPROC].PROCESS, [4-5TPROC].OPERATION;

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[netrusher]

What is CLng? and I think Date() equals today's date. The query may or may not have Effective dates equaling today's date. This query has no effective dates equaling today's date and that is why I need the latest date no matter when it is. With your code above I am getting a Syntax error
CLng([4-5TPROC].EFFECTIVE)_ = Date()

 

[PHV]

The latest date for each WHAT ? process ? operation ? both ?

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

 

[netrusher]

Operations as my original posting shows.

Thanks for your help!

 

[SkipVought]

Sorry, the UNDERSCORE was not in the correct place...

where CLng([4-5TPROC].EFFECTIVE_) = Date()

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[netrusher]

Skip,

Now I am getting a Data Type Mismatch.

SELECT [4-5TPROC].PROCESS, [4-5TPROC].OPERATION, [4-5TPROC].OPERATION_, [4-5TPROC].EFFECTIVE_
FROM [4-5TPROC]
where CLng([4-5TPROC].EFFECTIVE_) = Date()
ORDER BY [4-5TPROC].PROCESS, [4-5TPROC].OPERATION;

 

[SkipVought]

is the Data Type for [4-5TPROC].EFFECTIVE_ DATE?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[traingamer]

Why not simply:

SELECT [4-5TPROC].PROCESS, [4-5TPROC].OPERATION, [4-5TPROC].OPERATION_, [4-5TPROC].EFFECTIVE_
FROM [4-5TPROC]
where [4-5TPROC].EFFECTIVE_ = [b](Select max(tbl1.EFFECTIVE_) from [4-5TPROC] tbl1)[/b]
ORDER BY [4-5TPROC].PROCESS, [4-5TPROC].OPERATION;

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[netrusher]

It just says:

Data Type Mismatch in criteria expression

 

[netrusher]

Greg thanks,

That just gave the all of the latest dates and not just the latest date for each operation.

 

[SkipVought]

SELECT [4-5TPROC].PROCESS, [4-5TPROC].OPERATION, [4-5TPROC].OPERATION_, [4-5TPROC].EFFECTIVE_
FROM [4-5TPROC]
where SELECT [4-5TPROC].PROCESS&[4-5TPROC].OPERATION&[4-5TPROC].EFFECTIVE_ = 
(
Select [4-5TPROC].PROCESS&[4-5TPROC].OPERATION&max(tbl1.EFFECTIVE_) 
from [4-5TPROC] tbl1
group by [4-5TPROC].PROCESS, [4-5TPROC].OPERATION
)
ORDER BY [4-5TPROC].PROCESS, [4-5TPROC].OPERATION;

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[traingamer]

Sounds like you need a query getting the latest date for each operation. (not tested)

Select operation, Max(Effective_) As [b]MaxEff[/b]
FROM [4-5TPROC]
order by operation;

Save that query as qryOperDate.
Then use an inner join to match up:

SELECT [4-5TPROC].PROCESS, [4-5TPROC].OPERATION, [4-5TPROC].OPERATION_, [4-5TPROC].EFFECTIVE_
FROM [4-5TPROC] [b]inner join qryOperDate on (([4-5TPROC].OPERATION_ = qryOperDate.Operation) AND ([4-5TPROC].Effective_ = qryOperDate.MaxEff))[/b]
ORDER BY [4-5TPROC].PROCESS, [4-5TPROC].OPERATION;

Or something along those lines, if I understand you correctly.


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

You may try this:
SELECT A.PROCESS, A.OPERATION, A.OPERATION_, A.EFFECTIVE_
FROM [4-5TPROC] AS A
WHERE A.EFFECTIVE_=(SELECT Max(EFFECTIVE_) FROM [4-5TPROC] WHERE OPERATION=A.OPERATION)
ORDER BY 1, 2;

Or this:
SELECT A.PROCESS, A.OPERATION, A.OPERATION_, A.EFFECTIVE_
FROM [4-5TPROC] AS A INNER JOIN (
SELECT OPERATION, Max(EFFECTIVE_) AS LastDate FROM [4-5TPROC] GROUP BY OPERATION
) AS B ON A.OPERATION = B.OPERATION AND A.EFFECTIVE_ = B.LastDate
ORDER BY 1, 2;

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

 

[netrusher]

Thank you everyone for your help.

PH, they work great!