reports

[Kevin]

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

[Squirrel]

Re: reports

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 "qryCrossTa b"
SELECT tblCrossTab.OpN um, tblCrossTab.Rea son, Count(*) AS cnt
FROM tblCrossTab
GROUP BY tblCrossTab.OpN um, tblCrossTab.Rea son;

This is "qryCrossTab_Cr ossTab"
TRANSFORM sum(qryCrossTab .cnt) AS SumOfcnt
SELECT qryCrossTab.OpN um, sum(qryCrossTab .cnt) AS [Total Of cnt]
FROM qryCrossTab
GROUP BY qryCrossTab.OpN um
PIVOT qryCrossTab.Rea son;

and returns this data:

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


HTH -Linda



"Kevin" <kevcar40@btint ernet.com> wrote in message
news:9f8487f2.0 502100013.43864 dcf@posting.goo gle.com...[color=blue]
> 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[/color]

[Kevin]

Re: reports

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
"Squirrel" <wiseowl@covad. net> wrote in message news:<8c524$420 b4261$44a4c085$ 6152@msgid.mega newsservers.com >...[color=blue]
> 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 "qryCrossTa b"
> SELECT tblCrossTab.OpN um, tblCrossTab.Rea son, Count(*) AS cnt
> FROM tblCrossTab
> GROUP BY tblCrossTab.OpN um, tblCrossTab.Rea son;
>
> This is "qryCrossTab_Cr ossTab"
> TRANSFORM sum(qryCrossTab .cnt) AS SumOfcnt
> SELECT qryCrossTab.OpN um, sum(qryCrossTab .cnt) AS [Total Of cnt]
> FROM qryCrossTab
> GROUP BY qryCrossTab.OpN um
> PIVOT qryCrossTab.Rea son;
>
> and returns this data:
>
> qryCrossTab_Cro sstab OpNum Total Of cnt Reason1 Reason2
> 10 40 21 19
> 20 37 22 15
>
>
> HTH -Linda
>
>
>
> "Kevin" <kevcar40@btint ernet.com> wrote in message
> news:9f8487f2.0 502100013.43864 dcf@posting.goo gle.com...[color=green]
> > 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[/color][/color]