Solved

Need help with an access query

Posted on 2011-09-07
6
269 Views
Last Modified: 2012-05-12
I have attached an excel file with a sample data (sheet 1) and a sample query I need from the data (sheet 2).

The data has 13 periods with total QTY sold each period for each item from 5 different warehouses

Take a look at the attachment. Any ideas?
Sample.xlsx
0
Comment
Question by:Gerhardpet
  • 2
  • 2
  • 2
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36500487
SELECT ITEM,
    CASE WHEN WHSE = 'NDC' THEN SUM(TY_PER_SLS01 + TY_PER_SLS02 + TY_PER_SLS03 +
        TY_PER_SLS04 + TY_PER_SLS05 + TY_PER_SLS06 + TY_PER_SLS07 + TY_PER_SLS08 +
        TY_PER_SLS09 + TY_PER_SLS10 + TY_PER_SLS11 + TY_PER_SLS12 + TY_PER_SLS13) ELSE 0 AS NDC,
    CASE WHEN WHSE = '609' THEN SUM(TY_PER_SLS01 + TY_PER_SLS02 + TY_PER_SLS03 +
        TY_PER_SLS04 + TY_PER_SLS05 + TY_PER_SLS06 + TY_PER_SLS07 + TY_PER_SLS08 +
        TY_PER_SLS09 + TY_PER_SLS10 + TY_PER_SLS11 + TY_PER_SLS12 + TY_PER_SLS13) ELSE 0 AS [609],
    CASE WHEN WHSE = 'WPG' THEN SUM(TY_PER_SLS01 + TY_PER_SLS02 + TY_PER_SLS03 +
        TY_PER_SLS04 + TY_PER_SLS05 + TY_PER_SLS06 + TY_PER_SLS07 + TY_PER_SLS08 +
        TY_PER_SLS09 + TY_PER_SLS10 + TY_PER_SLS11 + TY_PER_SLS12 + TY_PER_SLS13) ELSE 0 AS WPG,
    CASE WHEN WHSE = 'DUF' THEN SUM(TY_PER_SLS01 + TY_PER_SLS02 + TY_PER_SLS03 +
        TY_PER_SLS04 + TY_PER_SLS05 + TY_PER_SLS06 + TY_PER_SLS07 + TY_PER_SLS08 +
        TY_PER_SLS09 + TY_PER_SLS10 + TY_PER_SLS11 + TY_PER_SLS12 + TY_PER_SLS13) ELSE 0 AS DUF,
    CASE WHEN WHSE = 'BIR' THEN SUM(TY_PER_SLS01 + TY_PER_SLS02 + TY_PER_SLS03 +
        TY_PER_SLS04 + TY_PER_SLS05 + TY_PER_SLS06 + TY_PER_SLS07 + TY_PER_SLS08 +
        TY_PER_SLS09 + TY_PER_SLS10 + TY_PER_SLS11 + TY_PER_SLS12 + TY_PER_SLS13) ELSE 0 AS BIR
FROM SomeTable
GROUP BY Item
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 36501130
Patrick's query could be OK in SQL Server but won't work in Access SQL because there is no Case command.

So use two SQL steps;

Save as Qry1:

SELECT WHSE,  ITEM,
(TY_PER_SLS01 + TY_PER_SLS02 + TY_PER_SLS03 + TY_PER_SLS04 + TY_PER_SLS05 + TY_PER_SLS06 + TY_PER_SLS07 + TY_PER_SLS08 + TY_PER_SLS09 + TY_PER_SLS10 + TY_PER_SLS11 + TY_PER_SLS12 + TY_PER_SLS13) as YTD,
 FROM SomeTable

Then use the crosstab query wizard to build a simple crosstab to give the resukts you want.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36503537
peter57r,
I followed your recommendation which works fine but I do not get the right numbers back in my cross-tab query

I'm attaching my database with sample data and as you will see I do not see the same result on the "test_Crosstab" query as on the test query

Item 1000 in Warehouse NDC I have 1863 YTD Sales and on the cross-tab query it shows 1

I'm I doing something wrong?

Access.mdb
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 77

Expert Comment

by:peter57r
ID: 36503730
You have specified a Count instead of a Sum for your crosstab summary.  You can change it in the design view of the crosstab.
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36504090
My mistake. Thank you for your help on this
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36507401
Even though the question title clearly indicated Access, I'd convinced myself it was SQL Server.

Head-desk!

:)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

808 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