Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Need help with an access query

Posted on 2011-09-07
Medium Priority
276 Views
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
Question by:Gerhardpet
[X]
###### 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
• 2
• 2
• 2

LVL 93

Expert Comment

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

peter57r earned 2000 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

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

LVL 77

Expert Comment

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

ID: 36504090
My mistake. Thank you for your help on this
0

LVL 93

Expert Comment

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

:)
0

## Featured Post

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insiâ€¦
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a â€śType Mis-matchâ€ť error is about.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In thisâ€¦
###### Suggested Courses
Course of the Month6 days, 3 hours left to enroll