Reports

[Kevin]

Hi
I have a table with several fields i.e
Area OP Num Date Reason Quantity, comp_price

I query the table to return Date as month, year and week number
the comp_price is the price of the component and can change for th
same component(ie different modle)
The results are grouped by Area, Month and comp_price quantity is
summed

I then create a crosstab query that pivots on the reason

What I want the report to do is show the op (once) and report the
reason as a quantity against it ie
com_price is not shown but used to calculate the cost of the quatity
(i have done this in the query and tried using the result onthe report
but get same result)
OP Num Reason1 reason 2 reason 3 reason4
10 100 49
20 100 20
However the report is not doing this, it is showing different results
ie
OP Num Reason1 reason 2 reason 3
reason4
10 100
10 49
20 100
20 20
can anyone please tell me how to resolve this

thanks in advance

kevin

 

[Duane Hookom]

Share the SQL of your crosstab.I expect you may be grouping by a field that
is not shown.

 

[Kevin]

below is the query with out the comp price
TRANSFORM Sum(DataTable_for_op_month.Quantity) AS SumOfQuantity
SELECT DataTable_for_op_month.opnumber, DataTable_for_op_month.Area,
DataTable_for_op_month.monthsSum(DataTable_for_op_month.Quantity) AS
[Total Of Quantity]
FROM DataTable_for_op_month
GROUP BY DataTable_for_op_month.opnumber, DataTable_for_op_month.Area,
DataTable_for_op_month.months
PIVOT DataTable_for_op_month.Reason;

 

[Duane Hookom]

What happens if you remove the Area and Month fields from the SELECT and
GROUP BY?

--
Duane Hookom
MS Access MVP

below is the query with out the comp price
TRANSFORM Sum(DataTable_for_op_month.Quantity) AS SumOfQuantity
SELECT DataTable_for_op_month.opnumber, DataTable_for_op_month.Area,
DataTable_for_op_month.monthsSum(DataTable_for_op_month.Quantity) AS
[Total Of Quantity]
FROM DataTable_for_op_month
GROUP BY DataTable_for_op_month.opnumber, DataTable_for_op_month.Area,
DataTable_for_op_month.months
PIVOT DataTable_for_op_month.Reason;

Share the SQL of your crosstab.I expect you may be grouping by a field
that
is not shown.