I have two tables, tblWorkOrder and tblSubWorkOrder. There is a one to many relationship between them. I want to pull the work order ID from the tblWorkOrder table and the Acres field from the tblSubWorkOrder table. Here's my query:

SELECT tblWorkOrder.pkWorkOrderID, tblSubWorkOrder.Acres
FROM tblWorkOrder INNER JOIN tblSubWorkOrder ON tblWorkOrder.pkWorkOrderID = tblSubWorkOrder.WorkOrderID


What I get is this:

WORKORDERID___ACRES
1_______________55
1_______________120
1_______________87
2_______________400
3_______________40
3_______________80
3_______________200
ETC.


I get multiple records per work order because different subworkorders under the same work order can have different acreages associated with them.

What I want is just one record returned for each work order ID. Preferably, the one record I would want to keep for each work order is the one that has the highest acreage. Thus, this is what I'm after:


WORKORDERID___ACRES
1______________120
2______________400
3______________200
ETC.



How do I do this in SQL?


Thanks,

Duluter

Have you tryied a totaling query grouping on the [WORKORDERID] and MAX of [ACRES]

HiTechCoach:
No, I have not tried that. Though I'm not really sure what that would look like (I'm sorry, I'm not very skilled at SQL). Could you demonstrate?


Duluter

You can use the query designer to do it all by selecting the options. No need to know how to write the SQL manually.

create a new query in design mode.

Add your table and the two fields.

On the menu go to View > Totals

The grid will change to add a new "Total:" row. Under the Acres change the "Group By" to "Max".

Worked perfectly. Thank you, HiTechCoach.


Duluter

I thought max just returned the highest number of the records. Like it would return 120. I thought that sum was the key word that would return the sum of the values. No?

I thought max just returned the highest number of the records. Like it would return 120. I thought that sum was the key word that would return the sum of the values. No?

You are correct about the MAX. The MAX is what the OP wanted. They wanted the 120.

You are right, Thanks.