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
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
Last Comment
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
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;
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
csehz,
BTW, it is a really, really bad idea to put spaces in column names.
Patrick
BTW, it is a really, really bad idea to put spaces in column names.
Patrick
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
Patrick, yes I know that not too good like this, but in the work from outer reports typically like this comings field names.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Try this
Select item, location, max(labor hours)
From table
Group by item,location