K
Kevin Labore
Hi
I have successfully created a query/report based on Month/Dept/Year and
shows a comparison
however I have run into a problem when I attempt to do Dept/Month/Year
query/report to show comparison. The Month/Dept/Year uses a crosstab
query(Month as row, Dept as Row, Year as Col, deptsales as value)
I then do a report with grouping (Month, Dept) from that query (2 cols one
with 2005 and one with 2004)
since the report knows both the sales for the dept as well as sales for all
depts for the month it can determine the dept %
(ie. dept 1 $25, dept2 $50, dept3 $12.50. dept4 $12.50) would have (dept1
25%. dept2 $50%, dept3 12.5%...)and total sales(for that month) would be
$100.
In then shows increases TY vs LY in cols of the reports for each (Sales +/-.
% of Sales increase, dept% change)
For the Month/Dept/Year report I can show the sales ok, but when I need to
get the dept% like in
my Dept/Month report I have not be able to get the data Crosstab'ed
correctly in order to
have the Month total sales for all dept's so I can figure the % of sales for
that dept vs all dept. I can generate a crosstab query (Month row, Year row,
Dept as Col, Dept sales as Value) to get the month sales, but then I have
been able to generate the report I want from that query
Basically the report should be each page should be grouped for each
deptartment for each department a crosstab report with Month/Year
So the report grouping would be Dept,.Month ---(it uses the same query as in
above)
The problem lies in being to determine the total Monthly sales in order
figure the dept% with I do it
I can compare the dept sales TY vs LY but not the dept%
I guess I am approaching this wrong because I am sure the solution is
relatively simple but I being confused by the data array.
I know in queries that there can be up to 3 rows and only one col and one
value so maybe that is what is confusing me.
I though of making a table from query and then having a crosstab query on
the table that was generated but I was unsure how to get the data I want.
Can someone point me in the right direction?
thanks for any assistance you can offer
Kevin
ps. if you would like a sample report page of either report let me know and
I will email it to you
I have successfully created a query/report based on Month/Dept/Year and
shows a comparison
however I have run into a problem when I attempt to do Dept/Month/Year
query/report to show comparison. The Month/Dept/Year uses a crosstab
query(Month as row, Dept as Row, Year as Col, deptsales as value)
I then do a report with grouping (Month, Dept) from that query (2 cols one
with 2005 and one with 2004)
since the report knows both the sales for the dept as well as sales for all
depts for the month it can determine the dept %
(ie. dept 1 $25, dept2 $50, dept3 $12.50. dept4 $12.50) would have (dept1
25%. dept2 $50%, dept3 12.5%...)and total sales(for that month) would be
$100.
In then shows increases TY vs LY in cols of the reports for each (Sales +/-.
% of Sales increase, dept% change)
For the Month/Dept/Year report I can show the sales ok, but when I need to
get the dept% like in
my Dept/Month report I have not be able to get the data Crosstab'ed
correctly in order to
have the Month total sales for all dept's so I can figure the % of sales for
that dept vs all dept. I can generate a crosstab query (Month row, Year row,
Dept as Col, Dept sales as Value) to get the month sales, but then I have
been able to generate the report I want from that query
Basically the report should be each page should be grouped for each
deptartment for each department a crosstab report with Month/Year
So the report grouping would be Dept,.Month ---(it uses the same query as in
above)
The problem lies in being to determine the total Monthly sales in order
figure the dept% with I do it
I can compare the dept sales TY vs LY but not the dept%
I guess I am approaching this wrong because I am sure the solution is
relatively simple but I being confused by the data array.
I know in queries that there can be up to 3 rows and only one col and one
value so maybe that is what is confusing me.
I though of making a table from query and then having a crosstab query on
the table that was generated but I was unsure how to get the data I want.
Can someone point me in the right direction?
thanks for any assistance you can offer
Kevin
ps. if you would like a sample report page of either report let me know and
I will email it to you