Cross Tab

[solsen]

Hi,

What I am looking to do is to track the average amount of units at multiple
accounts in a cross tab query. I have the column headings set as the fiscal
period (200701) as well column in the query identifing when the account first
purchased the produced (became "active") What I am looking to do is return a
zero value for all time points after the first order point (using the NZ
function) when the account has not placed an order (but is active) while
leaving the time points before that blank.

Thanks in advance

 

[KARL DEWEY]

We can not see your database!
You need to post the table & field structure along with sample data and
example of what the crosstab query output would look like.

 

[solsen via AccessMonster.com]

Karl,

Here is the SQL:
TRANSFORM IIf([FISCAL_PER]>=[MinOfFISCAL_PER],nz([SumOfINVOICE_QU],0)) AS
Quantity
SELECT Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER, Trial.MATERIAL_N, Trial.
SumOfNET_REVENU, Trial.SALES_TER2, Trial.SALES_REGI, Trial.SALES_REP2
FROM Trial
GROUP BY Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER, Trial.MATERIAL_N, Trial.
SumOfNET_REVENU, Trial.SumOfINVOICE_QU, Trial.SALES_TER2, Trial.SALES_REGI,
Trial.SALES_REP2
PIVOT Trial.FISCAL_PER;

what I am trying to show would look like the following

Customer_N Min of Fiscal Period 200706 200707 200708 200709 200710 200711
200712
1234 200707 3 0
4 2 0 0
4321 200710
10 0 10

where anything that is before the minimum fiscal period would be blank. If
there were no records (200708 for customer 1234) it would return a zero (NZ
function).

We can not see your database!
You need to post the table & field structure along with sample data and
example of what the crosstab query output would look like.

Hi,

[quoted text clipped - 7 lines]

Thanks in advance

 

[KARL DEWEY]

What is the number you wish to display under each FISCAL_PER?

Why are you selecting all these fields?
Trial.MATERIAL_N, Trial.SumOfNET_REVENU, Trial.SALES_TER2, Trial.SALES_REGI,
Trial.SALES_REP2

--
KARL DEWEY
Build a little - Test a little

Karl,

Here is the SQL:
TRANSFORM IIf([FISCAL_PER]>=[MinOfFISCAL_PER],nz([SumOfINVOICE_QU],0)) AS
Quantity
SELECT Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER, Trial.MATERIAL_N, Trial.
SumOfNET_REVENU, Trial.SALES_TER2, Trial.SALES_REGI, Trial.SALES_REP2
FROM Trial
GROUP BY Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER, Trial.MATERIAL_N, Trial.
SumOfNET_REVENU, Trial.SumOfINVOICE_QU, Trial.SALES_TER2, Trial.SALES_REGI,
Trial.SALES_REP2
PIVOT Trial.FISCAL_PER;

what I am trying to show would look like the following

Customer_N Min of Fiscal Period 200706 200707 200708 200709 200710 200711
200712
1234 200707 3 0
4 2 0 0
4321 200710
10 0 10

where anything that is before the minimum fiscal period would be blank. If
there were no records (200708 for customer 1234) it would return a zero (NZ
function).

We can not see your database!
You need to post the table & field structure along with sample data and
example of what the crosstab query output would look like.

Hi,

[quoted text clipped - 7 lines]

Thanks in advance

 

[solsen via AccessMonster.com]

What I am trying to display is the quantity ordered in each period. For
every period after the first order (min of invoice) if they did not place an
order (there is no record then) I want to show a zero. For the orders before
the first order I want it to be null.

Those fields are mapping information to sales reps, territories and regions
as well as the total revenue at the account (not necessary). Material_N
identifies the specific product I am looking at.

What is the number you wish to display under each FISCAL_PER?

Why are you selecting all these fields?
Trial.MATERIAL_N, Trial.SumOfNET_REVENU, Trial.SALES_TER2, Trial.SALES_REGI,
Trial.SALES_REP2

Karl,

[quoted text clipped - 30 lines]

 

[KARL DEWEY]

This data --------------
CUSTOMER_N MinOfFISCAL_PER SumOfINVOICE_QU SALES_REP2 FISCAL_PER
x 200707 4 200707
x 200707 2 200708
x 200707 1 200709
x 200707 6 200710
x 200707 3 200711
x 200707 4 200712
y 200708 1 200708
y 200708 6 200709
y 200708 4 200711
y 200708 3 200712
z 200706 5 200706
z 200706 6 200707
z 200706 7 200708
z 200706 7 200709
z 200706 3 200710
z 200706 6 200711
z 200706 5 200712
y 200708 0 200710
with this SQL ------------------------------
TRANSFORM Sum(IIf([FISCAL_PER]>=[MinOfFISCAL_PER] And [SumOfINVOICE_QU] Is
Null,0,[SumOfINVOICE_QU])) AS Quantity
SELECT Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER
FROM Trial
GROUP BY Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER
PIVOT Trial.FISCAL_PER;
results in this output --------------------------
CUSTOMER_N MinOfFISCAL_PER 200706 200707 200708 200709 200710 200711 200712
x 200707 4 2 1 6 3 4
y 200708 1 6 0 4 3
z 200706 5 6 7 7 3 6 5
--
KARL DEWEY
Build a little - Test a little

What I am trying to display is the quantity ordered in each period. For
every period after the first order (min of invoice) if they did not place an
order (there is no record then) I want to show a zero. For the orders before
the first order I want it to be null.

Those fields are mapping information to sales reps, territories and regions
as well as the total revenue at the account (not necessary). Material_N
identifies the specific product I am looking at.

What is the number you wish to display under each FISCAL_PER?

Why are you selecting all these fields?
Trial.MATERIAL_N, Trial.SumOfNET_REVENU, Trial.SALES_TER2, Trial.SALES_REGI,
Trial.SALES_REP2

Karl,

[quoted text clipped - 30 lines]

Thanks in advance

 

[solsen via AccessMonster.com]

Karl,

This is close however what I am looking to do is if there is no record in a
time period however the customer has ordered before that period I want to
return a zero (Take out the record for customer X in 200709 and it will
return a blank).

Thanks,
Scott

This data --------------
CUSTOMER_N MinOfFISCAL_PER SumOfINVOICE_QU SALES_REP2 FISCAL_PER
x 200707 4 200707
x 200707 2 200708
x 200707 1 200709
x 200707 6 200710
x 200707 3 200711
x 200707 4 200712
y 200708 1 200708
y 200708 6 200709
y 200708 4 200711
y 200708 3 200712
z 200706 5 200706
z 200706 6 200707
z 200706 7 200708
z 200706 7 200709
z 200706 3 200710
z 200706 6 200711
z 200706 5 200712
y 200708 0 200710
with this SQL ------------------------------
TRANSFORM Sum(IIf([FISCAL_PER]>=[MinOfFISCAL_PER] And [SumOfINVOICE_QU] Is
Null,0,[SumOfINVOICE_QU])) AS Quantity
SELECT Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER
FROM Trial
GROUP BY Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER
PIVOT Trial.FISCAL_PER;
results in this output --------------------------
CUSTOMER_N MinOfFISCAL_PER 200706 200707 200708 200709 200710 200711 200712
x 200707 4 2 1 6 3 4
y 200708 1 6 0 4 3
z 200706 5 6 7 7 3 6 5

What I am trying to display is the quantity ordered in each period. For
every period after the first order (min of invoice) if they did not place an

[quoted text clipped - 16 lines]

 

[KARL DEWEY]

Create a query that list all combinations of customer & .FISCAL_PER.
solsen_List ----
SELECT Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER, Trial_1.FISCAL_PER
FROM Trial, Trial AS Trial_1
GROUP BY Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER, Trial_1.FISCAL_PER;

Then this query -----
TRANSFORM
Sum(IIf([solsen_List].[FISCAL_PER]>=[solsen_List].[MinOfFISCAL_PER] And
[SumOfINVOICE_QU] Is Null,0,[SumOfINVOICE_QU])) AS Quantity
SELECT solsen_List.CUSTOMER_N, solsen_List.MinOfFISCAL_PER
FROM solsen_List LEFT JOIN Trial ON (solsen_List.FISCAL_PER =
Trial.FISCAL_PER) AND (solsen_List.CUSTOMER_N = Trial.CUSTOMER_N)
GROUP BY solsen_List.CUSTOMER_N, solsen_List.MinOfFISCAL_PER
PIVOT solsen_List.FISCAL_PER;

--
KARL DEWEY
Build a little - Test a little

Karl,

This is close however what I am looking to do is if there is no record in a
time period however the customer has ordered before that period I want to
return a zero (Take out the record for customer X in 200709 and it will
return a blank).

Thanks,
Scott

This data --------------
CUSTOMER_N MinOfFISCAL_PER SumOfINVOICE_QU SALES_REP2 FISCAL_PER
x 200707 4 200707
x 200707 2 200708
x 200707 1 200709
x 200707 6 200710
x 200707 3 200711
x 200707 4 200712
y 200708 1 200708
y 200708 6 200709
y 200708 4 200711
y 200708 3 200712
z 200706 5 200706
z 200706 6 200707
z 200706 7 200708
z 200706 7 200709
z 200706 3 200710
z 200706 6 200711
z 200706 5 200712
y 200708 0 200710
with this SQL ------------------------------
TRANSFORM Sum(IIf([FISCAL_PER]>=[MinOfFISCAL_PER] And [SumOfINVOICE_QU] Is
Null,0,[SumOfINVOICE_QU])) AS Quantity
SELECT Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER
FROM Trial
GROUP BY Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER
PIVOT Trial.FISCAL_PER;
results in this output --------------------------
CUSTOMER_N MinOfFISCAL_PER 200706 200707 200708 200709 200710 200711 200712
x 200707 4 2 1 6 3 4
y 200708 1 6 0 4 3
z 200706 5 6 7 7 3 6 5

What I am trying to display is the quantity ordered in each period. For
every period after the first order (min of invoice) if they did not place an

[quoted text clipped - 16 lines]

Thanks in advance

 

[solsen via AccessMonster.com]

I started to think that I would have to make a table first - thanks Karl.

Create a query that list all combinations of customer & .FISCAL_PER.
solsen_List ----
SELECT Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER, Trial_1.FISCAL_PER
FROM Trial, Trial AS Trial_1
GROUP BY Trial.CUSTOMER_N, Trial.MinOfFISCAL_PER, Trial_1.FISCAL_PER;

Then this query -----
TRANSFORM
Sum(IIf([solsen_List].[FISCAL_PER]>=[solsen_List].[MinOfFISCAL_PER] And
[SumOfINVOICE_QU] Is Null,0,[SumOfINVOICE_QU])) AS Quantity
SELECT solsen_List.CUSTOMER_N, solsen_List.MinOfFISCAL_PER
FROM solsen_List LEFT JOIN Trial ON (solsen_List.FISCAL_PER =
Trial.FISCAL_PER) AND (solsen_List.CUSTOMER_N = Trial.CUSTOMER_N)
GROUP BY solsen_List.CUSTOMER_N, solsen_List.MinOfFISCAL_PER
PIVOT solsen_List.FISCAL_PER;

Karl,

[quoted text clipped - 43 lines]