Solved

Simple SQL (why does it take me 2 queries when 1 should suffice?)

Posted on 2013-11-10
6
302 Views
Last Modified: 2013-11-10
See attached.

I want a query on the SalesInvoices table with the following characteristics;

1. Only include data from 2012
2. Use "group by" to produce the following format;

Joe     425
Tom   406


This should be fairly easy.  However, can it be done in a SINGLE query.  I find I need 2 queries.
TwoQueries.accdb
0
Comment
Question by:Patrick O'Dea
6 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 39637511
try this query

SELECT SalesInvoices.Salesman, SalesInvoices.Year, Sum(SalesInvoices.InvoiceValue) AS SumOfInvoiceValue
FROM SalesInvoices
GROUP BY SalesInvoices.Salesman, SalesInvoices.Year
HAVING (((SalesInvoices.Year)="2012"));
0
 
LVL 12

Accepted Solution

by:
duttcom earned 250 total points
ID: 39637515
Set up the query like this -
Single queryThis returns the results you are after.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39637523
if you want only two columns in a single query, use this query


Select A.Salesman,A.SumOfInvoiceValue
From
(
SELECT SalesInvoices.Salesman, SalesInvoices.Year, Sum(SalesInvoices.InvoiceValue) AS SumOfInvoiceValue
FROM SalesInvoices
GROUP BY SalesInvoices.Salesman, SalesInvoices.Year
HAVING (((SalesInvoices.Year)="2012"))
) As A
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Expert Comment

by:enfworks
ID: 39637524
SELECT SalesInvoices.Salesman, Sum(SalesInvoices.InvoiceValue) AS SumOfInvoiceValue
FROM SalesInvoices
WHERE (((SalesInvoices.year)='2012'))
GROUP BY SalesInvoices.Salesman;
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39637527
or this query


SELECT SalesInvoices.Salesman, Sum(SalesInvoices.InvoiceValue) AS SumOfInvoiceValue
FROM SalesInvoices
GROUP BY SalesInvoices.Salesman, SalesInvoices.Year
HAVING (((SalesInvoices.Year)="2012"));
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39637529
Thanks folks!
0

Featured Post

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).

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

758 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

22 Experts available now in Live!

Get 1:1 Help Now