Solved

Access Crosstab Query: based on sales totals within TWO date ranges

Posted on 2016-10-23
8
51 Views
Last Modified: 2016-10-24
I'm looking for a way to create an Access crosstab query reporting sales totals by 'Brand', for two different date ranges.:

- For Distributor: "DistID" (column, not visible)
- Sales Totals: "Sales" (column)
- TWO different date ranges: "depDate" for Period 1 and Period 2 (column):
  - Period1 = Between [forms]![frmRPT_YTDDepl_SF]![BDT1] And [forms]![frmRPT_YTDDepl_SF]![EDT1]
  - Period2 = Between [forms]![frmRPT_YTDDepl_SF]![BDT2] And [forms]![frmRPT_YTDDepl_SF]![EDT2]
- Brands = "DprodBrand" (rows)

Currently, I have TWO separate crosstab queries for each period, working properly. -> CODE BELOW.

I am looking for a way to create ONE query displaying Brand's sales totals for each date range, in two separate columns or one crosstab query.

Period1:

  TRANSFORM Sum(tblDepletions_DETAIL.detQuan) AS Sales
    SELECT tblProducts_DEPL.DprodBrand
    FROM tblDepletions INNER JOIN (tblProducts_DEPL INNER JOIN     tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID
    WHERE (((tblDepletions.depDate) Between [forms]![frmRPT_YTDDepl_SF]![BDT1]     And [forms]![frmRPT_YTDDepl_SF]![EDT1]) AND ((tblDepletions.depDistID)=132))
    GROUP BY tblProducts_DEPL.DprodBrand
    ORDER BY tblProducts_DEPL.DprodBrand
    PIVOT Format([depDate],"yy")-(Format(Date(),"yy"))+2 In (1,2);

Open in new window


Period2:

 TRANSFORM Sum(tblDepletions_DETAIL.detQuan) AS Sales
    SELECT tblProducts_DEPL.DprodBrand
    FROM tblDepletions INNER JOIN (tblProducts_DEPL INNER JOIN tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID
    WHERE (((tblDepletions.depDate) Between [forms]![frmRPT_YTDDepl_SF]![BDT2] And [forms]![frmRPT_YTDDepl_SF]![EDT2]) AND ((tblDepletions.depDistID)=132))
    GROUP BY tblProducts_DEPL.DprodBrand
    ORDER BY tblProducts_DEPL.DprodBrand
    PIVOT Format([depDate],"yy")-(Format(Date(),"yy"))+2 In (1,2);

Open in new window

Many Thanks!!! ~~  Jacob
0
Comment
Question by:Chi Is Current
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41856394
try creating a UNION query

Select A.*
From
(
  TRANSFORM Sum(tblDepletions_DETAIL.detQuan) AS Sales
    SELECT tblProducts_DEPL.DprodBrand
    FROM tblDepletions INNER JOIN (tblProducts_DEPL INNER JOIN     tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID
    WHERE (((tblDepletions.depDate) Between [forms]![frmRPT_YTDDepl_SF]![BDT1]     And [forms]![frmRPT_YTDDepl_SF]![EDT1]) AND ((tblDepletions.depDistID)=132))
    GROUP BY tblProducts_DEPL.DprodBrand
    ORDER BY tblProducts_DEPL.DprodBrand
    PIVOT Format([depDate],"yy")-(Format(Date(),"yy"))+2 In (1,2)
UNION ALL
 TRANSFORM Sum(tblDepletions_DETAIL.detQuan) AS Sales
    SELECT tblProducts_DEPL.DprodBrand
    FROM tblDepletions INNER JOIN (tblProducts_DEPL INNER JOIN tblDepletions_DETAIL ON tblProducts_DEPL.DprodZSKU = tblDepletions_DETAIL.detZSKU) ON tblDepletions.depID = tblDepletions_DETAIL.detDeplID
    WHERE (((tblDepletions.depDate) Between [forms]![frmRPT_YTDDepl_SF]![BDT2] And [forms]![frmRPT_YTDDepl_SF]![EDT2]) AND ((tblDepletions.depDistID)=132))
    GROUP BY tblProducts_DEPL.DprodBrand
    ORDER BY tblProducts_DEPL.DprodBrand
    PIVOT Format([depDate],"yy")-(Format(Date(),"yy"))+2 In (1,2)
) As A
0
 
LVL 2

Author Comment

by:Chi Is Current
ID: 41856426
Thank you, Rey, for your reply.

When I create the Union query from the code you provide, the query does not save, I get an error msg.: "Syntax error in FROM clause."  --  Both queries run independently.
???

I am hoping to find a way of creating one crosstab query with sales figures by brands within date range #1 in one column and sales figures by brands within date range #2 in the other column, ideally avoiding a union query, if possible.

Jacob
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41856428
can you upload a copy of the db?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 2

Author Comment

by:Chi Is Current
ID: 41856434
Thank you, Rey ~

It has a bunch of client data in it.  I'll have to clear it out and make a demo version.  I need to leave now - so, either later tonight or tomorrow.

Best ~ Jacob
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41856557
Save the two queries leaving out the ORDER BY.
Then create a new query:

SELECT *
FROM Q1
UNION ALL
SELECT *
FROM Q2
ORDER BY DprodBrand

Open in new window

/gustav
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 41856775
I'm not sure you need a CrossTab for this.

Select DProdBrand,
SUM(IIF([DepDate] BETWEEN [Forms]![frmRpt_YTDDepl_SF]![BDT1]
AND [[forms]![frmRPT_YTDDepl_SF]![EDT1], [Sales], 0) as Period1,
SUM(IIF([DepDate] Between [forms]![frmRPT_YTDDepl_SF]![BDT2]
AND [forms]![frmRPT_YTDDepl_SF]![EDT2], [Sales], 0) as Period2,
SUM([Sales]) as [Sales Total]
FROM yourTable
GROUP BY DProdBrand
1
 
LVL 2

Author Comment

by:Chi Is Current
ID: 41857541
Dale ~ Thank you for your new eyes here!  I agree, a crosstab query is not needed!
With a little  massaging (expected - see '[[forms]!...') your code is GREAT!
Even better!!!

Many Thanks ~ Jacob
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41857678
sorry about the typo; glad I could help.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now