Cross Tab Query

[gm6243 via AccessMonster.com]

HI Friends,

I have a cross tab query as a source for my report. It calculates average
sales for mth1, mth2 & mth3 and calculates the % of mth0 ie mth0/avg of
mth1+mth2+mth3.

The property for the query has column headings mth1, mth2, mth3 & mth0.

i tried to add a column to the query to calculate the averages but when I run
the query it asks for parameter value mth0, mth1, mth2 & mth3.

I am not sure if I am doing something wrong. So I tried to add this filter to
the report on the report property

(nz([Mth0])-((nz([Mth1])+nz([Mth2])+nz([Mth3]))/3))/((nz([Mth1])+Nz([Mth2])
+Nz([Mth3]))/3) <-0.299

Now when I run the report it comes out with the error " that the function is
too complex and cannot be calculated". However when I take out the Nz, it
works fine, but eliminated all rows where the value of mth0 is blank.

can some one please assist. I will provide all information if I am not clear
above.

Thank you

Gmen

 

[Allen Browne]

In design view of your crosstab query, add the value field again, choosing
Average in the Total row, and Row Heading in the Crosstab row. This gives
you a new field showing the average of the values displayed across that row.

For more details, it's similar to the 2nd suggestion here:
Crosstab query techniques - Display row totals
at:
http://allenbrowne.com/ser-67.html#RowTotal

 

[gm6243 via AccessMonster.com]

Thanks for your response. The suggestion may not work in this case as I need
the average of only the 2nd, 3rd and 4th month. This average then has to be
the denominator for the 1st month to get a percentage. Using what you
suggested would give me an average of all the 4 months. What I am looking to
do is find out what is the % of sale for the current month as compared to the
average of the previous 3 months.

In design view of your crosstab query, add the value field again, choosing
Average in the Total row, and Row Heading in the Crosstab row. This gives
you a new field showing the average of the values displayed across that row.

For more details, it's similar to the 2nd suggestion here:
Crosstab query techniques - Display row totals
at:
http://allenbrowne.com/ser-67.html#RowTotal

HI Friends,

[quoted text clipped - 23 lines]

clear
above.

 

[gm6243 via AccessMonster.com]

Hi Allen,

Using the following expression in the report filter gives me somewhat what I
need but eliminates all records where the current month (mth0) rows which is
zero or null.

CCur(Nz([Mth0],0)-(((Nz([Mth1],0)+Nz([Mth2],0)+Nz([Mth3],0))/3)))<-10000 and
(([Mth0])-((([Mth1])+([Mth2])+([Mth3]))/3))/((([Mth1])+([Mth2])+([Mth3]))/3)
<-0.299

When I add Nz to the second portion of the expression i.e after "AND", I get
the error " This expression is typed incorrectly, or is too complex to be
evaluated. For example a numeric expression may......."

Please help.

In design view of your crosstab query, add the value field again, choosing
Average in the Total row, and Row Heading in the Crosstab row. This gives
you a new field showing the average of the values displayed across that row.

For more details, it's similar to the 2nd suggestion here:
Crosstab query techniques - Display row totals
at:
http://allenbrowne.com/ser-67.html#RowTotal

HI Friends,

[quoted text clipped - 23 lines]

clear
above.

 

[Allen Browne]

I don't understand your AND either.

Try a Field like this in your crosstab query:
[Mth0] / ((Nz([Mth1],0) + Nz([Mth2],0) + Nz(Mth3],0)) / 3)
Crosstab: Row Heading
Total: Expression

You *may* be able to add criteria to the HAVING clause, but no promises.

Alternatively, you may be able to create another query that uses your
crosstab as a source 'table.'

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

Using the following expression in the report filter gives me somewhat what
I
need but eliminates all records where the current month (mth0) rows which
is
zero or null.

CCur(Nz([Mth0],0)-(((Nz([Mth1],0)+Nz([Mth2],0)+Nz([Mth3],0))/3)))<-10000
and
(([Mth0])-((([Mth1])+([Mth2])+([Mth3]))/3))/((([Mth1])+([Mth2])+([Mth3]))/3)
<-0.299

When I add Nz to the second portion of the expression i.e after "AND", I
get
the error " This expression is typed incorrectly, or is too complex to be
evaluated. For example a numeric expression may......."

Please help.

In design view of your crosstab query, add the value field again, choosing
Average in the Total row, and Row Heading in the Crosstab row. This gives
you a new field showing the average of the values displayed across that
row.

For more details, it's similar to the 2nd suggestion here:
Crosstab query techniques - Display row totals
at:
http://allenbrowne.com/ser-67.html#RowTotal

HI Friends,

[quoted text clipped - 23 lines]

clear
above.

 

[gm6243 via AccessMonster.com]

the report has to filter records for 1st part which is sales lost more than
10000, and also the Percentage of sales lost should be more than 30%.

The below expression when added to the query ask for the parameter values of
Mth0, mth1, mth2 & mth3.

Did not work.

The query for the report is as under:

PARAMETERS [Forms]![Monthly Reports]![EndDate] DateTime, [forms]![Monthly
Reports]![repname] Text ( 255 ), [forms]![Monthly Reports]![manager] Text (
255 );
SELECT yield.Account, ClientMasterFile.[Account Name], ClientMasterFile.
repname, ClientMasterFile.manager, "Mth" & DateDiff("m",[ReportDate]-1,Forms!
[Monthly Reports]!EndDate), Sum(Nz([charges],0)) AS Charge
FROM ClientMasterFile INNER JOIN yield ON ClientMasterFile.acctNum = yield.
Account
WHERE (((yield.Transtype)="4010") AND ((ClientMasterFile.repname)=[forms]!
[Monthly Reports]![repname]) AND ((ClientMasterFile.manager)=[forms]![Monthly
Reports]![manager]))
GROUP BY yield.Account, ClientMasterFile.[Account Name], ClientMasterFile.
repname, ClientMasterFile.manager, "Mth" & DateDiff("m",[ReportDate]-1,Forms!
[Monthly Reports]!EndDate);

Thanks,

Gmen

I don't understand your AND either.

Try a Field like this in your crosstab query:
[Mth0] / ((Nz([Mth1],0) + Nz([Mth2],0) + Nz(Mth3],0)) / 3)
Crosstab: Row Heading
Total: Expression

You *may* be able to add criteria to the HAVING clause, but no promises.

Alternatively, you may be able to create another query that uses your
crosstab as a source 'table.'

Hi Allen,

[quoted text clipped - 31 lines]

 

[Allen Browne]

I'm not sure I follow all you're doing here; all we can do is suggest
techniques, but the implementation will be up to you.

If you can't get all this working in one query, perhaps another technique
would be to create a lower level query that filters the data you want. Then
use it as the source 'table' for your crosstab.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

the report has to filter records for 1st part which is sales lost more
than
10000, and also the Percentage of sales lost should be more than 30%.

The below expression when added to the query ask for the parameter values
of
Mth0, mth1, mth2 & mth3.

Did not work.

The query for the report is as under:

PARAMETERS [Forms]![Monthly Reports]![EndDate] DateTime, [forms]![Monthly
Reports]![repname] Text ( 255 ), [forms]![Monthly Reports]![manager] Text
(
255 );
SELECT yield.Account, ClientMasterFile.[Account Name], ClientMasterFile.
repname, ClientMasterFile.manager, "Mth" &
DateDiff("m",[ReportDate]-1,Forms!
[Monthly Reports]!EndDate), Sum(Nz([charges],0)) AS Charge
FROM ClientMasterFile INNER JOIN yield ON ClientMasterFile.acctNum =
yield.
Account
WHERE (((yield.Transtype)="4010") AND ((ClientMasterFile.repname)=[forms]!
[Monthly Reports]![repname]) AND
((ClientMasterFile.manager)=[forms]![Monthly
Reports]![manager]))
GROUP BY yield.Account, ClientMasterFile.[Account Name], ClientMasterFile.
repname, ClientMasterFile.manager, "Mth" &
DateDiff("m",[ReportDate]-1,Forms!
[Monthly Reports]!EndDate);

Thanks,

Gmen

I don't understand your AND either.

Try a Field like this in your crosstab query:
[Mth0] / ((Nz([Mth1],0) + Nz([Mth2],0) + Nz(Mth3],0)) / 3)
Crosstab: Row Heading
Total: Expression

You *may* be able to add criteria to the HAVING clause, but no promises.

Alternatively, you may be able to create another query that uses your
crosstab as a source 'table.'

 

[gm6243 via AccessMonster.com]

Hi Allen,

I will give you the situation here. May be I was not very clear earlier,

We have managers and each of the managers have a set of sales rep.

I am calulating the sales for each salesman for say month jan (mth1), Feb
(mth2), mar(mth3) & apr(mth0).
now adding Jan,Feb,Mar and dividing by 3 gives me the avg for the 3 mths. I
now want to divide April's sale by the avg to get a %. once I have this
information, on the report, I want to eliminate all the sales where the
variance in sales over the average sale is less than $10,000. i.e. I want to
shortlist only sales which are lost more than $10000. The second condition
which also needs to be checked is that this sales loss of $10000 should be
atleast 30% of the average sales.

I am able to get the calculation part. but when I bring this to the report
all the sales appear including sales lost of say 2000 or 3000.This also bring
sales % lost less than 30%.

I want to eliminate these sales from the report.
Hope I am clear with my explanation.
thanks for your patience.
Gmen

I'm not sure I follow all you're doing here; all we can do is suggest
techniques, but the implementation will be up to you.

If you can't get all this working in one query, perhaps another technique
would be to create a lower level query that filters the data you want. Then
use it as the source 'table' for your crosstab.

the report has to filter records for 1st part which is sales lost more
than

[quoted text clipped - 43 lines]

 

[Allen Browne]

Okay, I think cross-tabbing this will be the last step before the report.

You will need a couple of layers of queries, possibly a temp table to make
it efficient.

Start with a query that gives the monthly totals grouped the way you want.
Something like this:
Query1:
======
SELECT ManagerID,
SalesRepID,
DateSerial(Year([SaleDate]),Month([SaleDate]),1) AS TheMonth
Month(SaleDate) AS TheMonth,
Sum(Amount) AS SumOfAmount
FROM tblSales
GROUP BY ManagerID,
SalesRepID,
Year(SaleDate),
Month(SaleDate)

The next query will use that one to get the average of the 3 prior months,
e.g.:
Query2:
=======
SELECT Query1.*,
(SELECT Avg(Amount) AS AvgAmount
FROM Query1 AS Dupe
WHERE Dupe.TheMonth Between DateAdd("m", -3, Query1.TheMonth)
And DateAdd("m", -1, Query1.TheMonth)) AS Prior3MonthAvg
FROM Query1;

Now add the criteria to eliminate the records you want. If it gets too
complicated or inefficient, write the results to a temporary table.

You can then build the cross-tab on that to feed the report.

 

[gm6243 via AccessMonster.com]

Thanks Allen,

I will try this and advice. I am currently out of town will return end of the
week. Appreciate your help.

Gmen

Okay, I think cross-tabbing this will be the last step before the report.

You will need a couple of layers of queries, possibly a temp table to make
it efficient.

Start with a query that gives the monthly totals grouped the way you want.
Something like this:
Query1:
======
SELECT ManagerID,
SalesRepID,
DateSerial(Year([SaleDate]),Month([SaleDate]),1) AS TheMonth
Month(SaleDate) AS TheMonth,
Sum(Amount) AS SumOfAmount
FROM tblSales
GROUP BY ManagerID,
SalesRepID,
Year(SaleDate),
Month(SaleDate)

The next query will use that one to get the average of the 3 prior months,
e.g.:
Query2:
=======
SELECT Query1.*,
(SELECT Avg(Amount) AS AvgAmount
FROM Query1 AS Dupe
WHERE Dupe.TheMonth Between DateAdd("m", -3, Query1.TheMonth)
And DateAdd("m", -1, Query1.TheMonth)) AS Prior3MonthAvg
FROM Query1;

Now add the criteria to eliminate the records you want. If it gets too
complicated or inefficient, write the results to a temporary table.

You can then build the cross-tab on that to feed the report.

Hi Allen,

[quoted text clipped - 23 lines]

thanks for your patience.
Gmen