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
Solved

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

Posted on 2013-11-10
6
334 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

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…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…

829 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