Fiscal Period Definition

[Guest]

Hello,

I have the following crosstab query, and am trying to make a few changes.

1) In the PIVOT line, I want to define the quarters as my fiscal quarters
(ie: 1/2/05 - 4-2-05 for Q1) rather than the standard quarters access uses
(1/1/05-3/3105).

2) I would like to make the transform line variable based on a form
parameter. IE: enter "Amount" on the form you get
TRANSFORM Sum(Sales.Amount) AS SumOfAmount.

Enter "Volume" on the form and you get
TRANSFORM Sum(Sales.Volume) AS SumOfVolume

Any help would be greatly appreciated! (SQL below) Thanks.

***************************
TRANSFORM Sum(Sales.Amount) AS SumOfAmount
SELECT Customers.CustName
FROM (Sales INNER JOIN Customers ON Sales.CustNo = Customers.CustNo) INNER
JOIN Products ON Sales.ProductCode = Products.ProductCode
WHERE (((Sales.Date) Between [Forms]![SalesbySalesman]![StartDate] And
[Forms]![SalesbySalesman]![EndDate]) AND
((Customers.SalesName)=[Forms]![SalesbySalesman]![SalesPerson]))
GROUP BY Customers.CustName, Customers.SalesName
PIVOT "Q" & DatePart("q",[Date],1,0) In ("Q1","Q2","Q3","Q4");

 

[David Lloyd]

Ari:

For the first issue, one approach is to build a field that calculates the
correct quarter based on the [Date] value and then pivot on this field
rather than using the DatePart function. It would be a little lengthy but
the field would start like the following:

IIF(Month([Date])<=4 AND Day([Date])<=2 AND Month([Date])>=1 AND
Day([Date])>=2,1,IIF(...[Continue on to define Q2 and Q3, Q4 will be what is
left over]

Regarding the second issue, one approach would be to dynamically build the
crosstab query based on the information entered on your form. A second
approach would be to have a separate crosstab query for each possible
Transform field on your form.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello,

I have the following crosstab query, and am trying to make a few changes.

1) In the PIVOT line, I want to define the quarters as my fiscal quarters
(ie: 1/2/05 - 4-2-05 for Q1) rather than the standard quarters access uses
(1/1/05-3/3105).

2) I would like to make the transform line variable based on a form
parameter. IE: enter "Amount" on the form you get
TRANSFORM Sum(Sales.Amount) AS SumOfAmount.

Enter "Volume" on the form and you get
TRANSFORM Sum(Sales.Volume) AS SumOfVolume

Any help would be greatly appreciated! (SQL below) Thanks.

***************************
TRANSFORM Sum(Sales.Amount) AS SumOfAmount
SELECT Customers.CustName
FROM (Sales INNER JOIN Customers ON Sales.CustNo = Customers.CustNo) INNER
JOIN Products ON Sales.ProductCode = Products.ProductCode
WHERE (((Sales.Date) Between [Forms]![SalesbySalesman]![StartDate] And
[Forms]![SalesbySalesman]![EndDate]) AND
((Customers.SalesName)=[Forms]![SalesbySalesman]![SalesPerson]))
GROUP BY Customers.CustName, Customers.SalesName
PIVOT "Q" & DatePart("q",[Date],1,0) In ("Q1","Q2","Q3","Q4");

 

[David Lloyd]

Ari:

Regarding issue number one, the sample I gave you does not work in all
cases. Below is a more robust example, which appears to work, however, you
may have to adjust it for end/beginning of year issues depending on the date
values in your table. You may want to consider creating a function to call
in your query rather than defining a field using this lengthy expression.

IIf(Month(dt) = 1 And Day(dt >= 2) = 2 Or Month(dt) = 3, 1, _
IIf(Month(dt) = 5 Or Month(dt) = 6, 2, IIf(Month(dt) = 8 Or
Month(dt) = 9, 3, _
IIf(Month(dt) = 11 Or Month(dt) = 12, 4, IIf(Month(dt) = 1 And
Day(dt) >= 2, 1, _
IIf(Month(dt) = 4 And Day(dt) <= 2, 1, IIf(Month(dt) = 4 And Day(dt)

2, 2, _

IIf(Month(dt) = 7 And Day(dt) <= 2, 2, IIf(Month(dt) = 7 And Day(dt)

2, 3, _

IIf(Month(dt) = 10 And Day(dt) <= 2, 3, 4))))))))))

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello,

I have the following crosstab query, and am trying to make a few changes.

1) In the PIVOT line, I want to define the quarters as my fiscal quarters
(ie: 1/2/05 - 4-2-05 for Q1) rather than the standard quarters access uses
(1/1/05-3/3105).

2) I would like to make the transform line variable based on a form
parameter. IE: enter "Amount" on the form you get
TRANSFORM Sum(Sales.Amount) AS SumOfAmount.

Enter "Volume" on the form and you get
TRANSFORM Sum(Sales.Volume) AS SumOfVolume

Any help would be greatly appreciated! (SQL below) Thanks.

***************************
TRANSFORM Sum(Sales.Amount) AS SumOfAmount
SELECT Customers.CustName
FROM (Sales INNER JOIN Customers ON Sales.CustNo = Customers.CustNo) INNER
JOIN Products ON Sales.ProductCode = Products.ProductCode
WHERE (((Sales.Date) Between [Forms]![SalesbySalesman]![StartDate] And
[Forms]![SalesbySalesman]![EndDate]) AND
((Customers.SalesName)=[Forms]![SalesbySalesman]![SalesPerson]))
GROUP BY Customers.CustName, Customers.SalesName
PIVOT "Q" & DatePart("q",[Date],1,0) In ("Q1","Q2","Q3","Q4");

 

[David Lloyd]

Ari:

A slightly more refined version:

IIf(Month(dt) = 2 Or Month(dt) = 3, 1, _
IIf(Month(dt) = 5 Or Month(dt) = 6, 2, IIf(Month(dt) = 8 Or
Month(dt) = 9, 3, _
IIf(Month(dt) = 1 And Day(dt) >= 2, 1, _
IIf(Month(dt) = 4 And Day(dt) <= 2, 1, IIf(Month(dt) = 4 And Day(dt)

2, 2, _

IIf(Month(dt) = 7 And Day(dt) <= 2, 2, IIf(Month(dt) = 7 And Day(dt)

2, 3, _

IIf(Month(dt) = 10 And Day(dt) <= 2, 3, 4)))))))))

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello,

I have the following crosstab query, and am trying to make a few changes.

1) In the PIVOT line, I want to define the quarters as my fiscal quarters
(ie: 1/2/05 - 4-2-05 for Q1) rather than the standard quarters access uses
(1/1/05-3/3105).

2) I would like to make the transform line variable based on a form
parameter. IE: enter "Amount" on the form you get
TRANSFORM Sum(Sales.Amount) AS SumOfAmount.

Enter "Volume" on the form and you get
TRANSFORM Sum(Sales.Volume) AS SumOfVolume

Any help would be greatly appreciated! (SQL below) Thanks.

***************************
TRANSFORM Sum(Sales.Amount) AS SumOfAmount
SELECT Customers.CustName
FROM (Sales INNER JOIN Customers ON Sales.CustNo = Customers.CustNo) INNER
JOIN Products ON Sales.ProductCode = Products.ProductCode
WHERE (((Sales.Date) Between [Forms]![SalesbySalesman]![StartDate] And
[Forms]![SalesbySalesman]![EndDate]) AND
((Customers.SalesName)=[Forms]![SalesbySalesman]![SalesPerson]))
GROUP BY Customers.CustName, Customers.SalesName
PIVOT "Q" & DatePart("q",[Date],1,0) In ("Q1","Q2","Q3","Q4");

 

[Guest]

Thanks David! I will give this a shot. I really appreciate your help.

Another option I was thinking was to try and build a seperate table that
shows all possible dates and the fiscal period that date would fall in. Then
somehow pass that information through to the PIVOT line. That might let me
do fiscal months over a number of years without building a gigantic
expression. Do you think that is a path worth walking down?

Thanks again for all your help!

Ari

Ari:

A slightly more refined version:

IIf(Month(dt) = 2 Or Month(dt) = 3, 1, _
IIf(Month(dt) = 5 Or Month(dt) = 6, 2, IIf(Month(dt) = 8 Or
Month(dt) = 9, 3, _
IIf(Month(dt) = 1 And Day(dt) >= 2, 1, _
IIf(Month(dt) = 4 And Day(dt) <= 2, 1, IIf(Month(dt) = 4 And Day(dt)

2, 2, _

IIf(Month(dt) = 7 And Day(dt) <= 2, 2, IIf(Month(dt) = 7 And Day(dt)

2, 3, _

IIf(Month(dt) = 10 And Day(dt) <= 2, 3, 4)))))))))

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hello,

I have the following crosstab query, and am trying to make a few changes.

1) In the PIVOT line, I want to define the quarters as my fiscal quarters
(ie: 1/2/05 - 4-2-05 for Q1) rather than the standard quarters access uses
(1/1/05-3/3105).

2) I would like to make the transform line variable based on a form
parameter. IE: enter "Amount" on the form you get
TRANSFORM Sum(Sales.Amount) AS SumOfAmount.

Enter "Volume" on the form and you get
TRANSFORM Sum(Sales.Volume) AS SumOfVolume

Any help would be greatly appreciated! (SQL below) Thanks.

***************************
TRANSFORM Sum(Sales.Amount) AS SumOfAmount
SELECT Customers.CustName
FROM (Sales INNER JOIN Customers ON Sales.CustNo = Customers.CustNo) INNER
JOIN Products ON Sales.ProductCode = Products.ProductCode
WHERE (((Sales.Date) Between [Forms]![SalesbySalesman]![StartDate] And
[Forms]![SalesbySalesman]![EndDate]) AND
((Customers.SalesName)=[Forms]![SalesbySalesman]![SalesPerson]))
GROUP BY Customers.CustName, Customers.SalesName
PIVOT "Q" & DatePart("q",[Date],1,0) In ("Q1","Q2","Q3","Q4");