[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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