Solved

MS access, SQL question

Posted on 2016-10-28
7
49 Views
Last Modified: 2016-11-01
I have this SQL:

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

It will only ever return one record.  How can I write this query without having to list all the fields in the work_order table?
0
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 41863883
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

Open 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

Open in new window

0
 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41864136
When you aggregate data, you cannot select columns that "unaggregate".  For example, if you want to count customers in a state, the query would be:
Select State, Count(*) AS CustCount
Group By State;

If you included CustName
Select CustName,, State, Count(*) AS CustCount
Group By CustName, State;

Your CustCount would always be 1 because no aggregation would take place.  That is what your Select * is doing.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41864423
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:HLRosenberger
ID: 41864445
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.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 41864450
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
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41864523
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.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 41868328
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question