MS access, SQL question

How can I write this query without having to list all the fields in the work_order table?

remove the Select *

SELECT sum(work_order_item.quantity)    from work_order
left join work_order_item ON work_order_item.local_work_order_id = work_order.local_id
where work_order.local_id = 36

Select allOpen in new window

?
or select yourfield1, youfield2, sum(work_order_item.quantity)    from work_order
...
with a group by of your fields

SELECT yourfield1, youfield2, sum(work_order_item.quantity)    from work_order
left join work_order_item ON work_order_item.local_work_order_id = work_order.local_id
where work_order.local_id = 36
group by yourfield1, youfield2

Select allOpen in new window

So, if I have say 20 columns to pull back from the main table, and then 2 column from a JOINed table that I want to SUM, I have to list all the 20 columns and GROUP BY all of them?

Also what I really want to do is have a grand total cost.   I believe I know how to do this using SQL server, but not Access SQL.    I have a main orders table and a related orders item, which has  a quantity and cost.  I want a grand total for the order.  Trying to do it all in in SELECT statement, where I pull into from the  main orders table, and then calculate the total cost.  In SQL server, I think a Sub-query select would work.

ah, this works.  I just need to list all columns in the orders table.   There is around 30 columns. No way around this, correct?  

SELECT  work_order.id,  work_order.service_order_id,  work_order.equipment_id,  work_order.description, sum(work_order_item.quantity * work_order_item.unit)

FROM  work_order

inner join work_order_item ON work_order_item.local_work_order_id = work_order.local_id

where work_order.id = 75567

group by work_order.id,  work_order.service_order_id,  work_order.equipment_id,  work_order.description

It is always better to select specific columns than to use Select * anyway but when you are aggregating, EVERY column in the Select clause must be accounted for.  If you are not summing, counting, etc, then you must specifically group by.

Thanks!