Extract max value from 3 adjacent columns 2

[KUZZ]

Hi,

I need to extract max date value from 3 adjacent colums: returndate, scrapdate, reworkdate. Mind that these 3 values are in individual fields of the same record. I know how to do this in Excell. Is there a way to extract it in Access?


Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[HarleyQuinn]

Yep, try something like

SELECT YourIDField, Max(returndate) AS MaxReturndate, Max(scrapdate) AS MaxScrapdate, Max(reworkdate) AS MaxReworkdate
FROM YourTable
GROUP BY YourIDField

I've stuck an ID field in as I assumed you have one, worst comes to worst it will still work without one if you've not got one, just take it out of the select and remove the group by clause.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[KUZZ]

This is great, but how do I now extract a single value that is the Max of the Maxes

Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[lespaul]

first create a query that normalizes your data:

SELECT OtherFieldsYouNeed, returnDate As "TheDate", "returnDate" as DateType FROM TableName
UNION
SELECT OtherFieldsYouNeed, scrapDate, "scrapDate" FROM TableName
UNION
SELECT OtherFieldsYouNeed, reworkDate, "reworkDate" FROM TableName

then:

SELECT OtherFieldsYouNeed, DateType, Max(TheDate) FROM qryNormal
GROUP BY OtherFieldsYouNeed, DateType

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[HarleyQuinn]

Go on then, something using IIF's perhaps?

SELECT YourIDField, Max(IIf([returndate]>[scrapdate],IIf([returndate]>[reworkdate],[returndate],IIf([scrapdate]>[reworkdate],[scrapdate],[reworkdate])))) AS MaxOfDates
FROM YourTable
GROUP BY YourIDField

Is that a bit more like what you want? Again, you can take off the ID field if you don't have one.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[KUZZ]

HarleyQuinn and LesPaul thanks for the input. I like both ideas, however I went with Leslie's

Best regards,

Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."