asked on

Access 2000 - Query question

Dear Experts,

I have a problem which seems easy but can not find the solution, basically have such table in the attached database

Item Location Labour hours
10101 West 10
10102 East 30
10102 West 20
10103 North 40

So having three columns, it contains the labour hours for items on certain locations.

Could you advise how a query could give that location which has the highest Labour? So the max of labour, but the with the belonging location. So based on the example this would be the result

10101 West 10
10102 East 30
10103 North 40

thanks,

Query.zip

Rey Obrero (Capricorn1)

The result you posted is not correct.

Try this

Select item, location, max(labor hours)
From table
Group by item,location

csehz

ASKER

Do you mean like in the attached code?

This result is not good for me as brings four records instead of three. Somehow the target should be at item 10102 showing one record and one location the East. Because that has the highest labour

SELECT Labour.Item, Labour.Location, Max(Labour.[Labour hours]) AS [MaxOfLabour hours]
FROM Labour
GROUP BY Labour.Item, Labour.Location;

Open in new window

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick Matthews

csehz,

BTW, it is a really, really bad idea to put spaces in column names.

Patrick

SOLUTION

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Thanks the solutions, really learnt again something. For me Capricorn's solution is the easiest to understand and apply on the live database, as it is without join.

Patrick, yes I know that not too good like this, but in the work from outer reports typically like this comings field names.