Solved

MS access, SQL question

Posted on 2016-10-28
7
43 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
  • 4
  • 2
7 Comments
 
LVL 50

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 35

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 35

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

792 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