Solved

MS access, SQL question

Posted on 2016-10-28
7
24 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 49

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 34

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 34

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now