• Status: Solved
• Priority: Medium
• Security: Public
• Views: 281

# Need help with an access query

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
Gerhardpet
• 2
• 2
• 2
1 Solution

Commented:
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

Commented:
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

Author Commented:
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

Commented:
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

Author Commented:
My mistake. Thank you for your help on this
0

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

:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.