reports

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin - 10 Feb 2005 08:13 GMT

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

I query the table to return Date as month, year and week number
The results are grouped by Area, Month and 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
OP Num Reason1 reason 2 reason 3 reason
4
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 reason
4
10 100
10 49
20 100
20 20
can anyone please tell me how to resolve this

thanks in advance

kevin

Reply to this Message

Squirrel - 10 Feb 2005 11:13 GMT

Hi Kevin,

I put together a dataset as you described. OpNum is numeric, and Reason is
text and will hold
data such as "Reason1", "Reason2", "Reason3" etc. tblCrossTab with only the
above two fields.

Then I built two queries:

This is "qryCrossTab"
SELECT tblCrossTab.OpNum, tblCrossTab.Reason, Count(*) AS cnt
FROM tblCrossTab
GROUP BY tblCrossTab.OpNum, tblCrossTab.Reason;

This is "qryCrossTab_CrossTab"
TRANSFORM sum(qryCrossTab.cnt) AS SumOfcnt
SELECT qryCrossTab.OpNum, sum(qryCrossTab.cnt) AS [Total Of cnt]
FROM qryCrossTab
GROUP BY qryCrossTab.OpNum
PIVOT qryCrossTab.Reason;

and returns this data:

qryCrossTab_Crosstab OpNum Total Of cnt Reason1 Reason2
10 40 21 19
20 37 22 15

HTH -Linda

> Hi
> I have a table with several fields i.e
[quoted text clipped - 24 lines]
>
> kevin

Reply to this Message

Kevin - 11 Feb 2005 18:50 GMT

thanks for reply that solved my problem
however if i wanted to and component price to the query it gives me
the same results as before ie duplicate op numbers, i think this is
because the component prices are grouped(new at this)could you tell me
how i can get the coponent price into the query and then onto the
report and stillreturn just on occurence of op number

thanks

kevin

> Hi Kevin,
>
[quoted text clipped - 53 lines]
> >
> > kevin

Reply to this Message