[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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

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
Chi Is Current
Asked:
Chi Is Current
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
Chi Is CurrentAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chi Is CurrentAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Dale FyeCommented:
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
 
Chi Is CurrentAuthor Commented:
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
 
Dale FyeCommented:
sorry about the typo; glad I could help.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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