Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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
Asked:
Gerhardpet
  • 2
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
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
 
peter57rCommented:
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
 
GerhardpetAuthor 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
peter57rCommented:
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
 
GerhardpetAuthor Commented:
My mistake. Thank you for your help on this
0
 
Patrick MatthewsCommented:
Even though the question title clearly indicated Access, I'd convinced myself it was SQL Server.

Head-desk!

:)
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now