Rows into Column in a Report!

[cap.zadi]

Hey there,



I would like to ask a question to create a report when having the following data tables which are updated through a one main form and 2 sub forms.
table1
TrnNo pk-AN
TrnDate
Product
Machine

table2

RNO PK-AN
TrnNO FK
PrdDate
GoodQty
RejectQty


table3

RRNO PK-AN
RNO FK
CarNo
RRejectQty
Reason

* table3 is used to describe the reject details of Table2 like if

RejectQty = 10

so in table3 it will be

RRejectQty = 5
Reason = Crack

RRejectQty = 5
Reason = Bend


Now i am looking for a report where i can see the following sum by Prddate:

Prddate
product goodqty rejectqty Bend Crack
AA 10 10 5 5

How we can convert those reasons as column headings?


thanks

zee

[cap.zadi]

the file screen shots attached.

if we see the query results so it double count the other rows because of reject reasons.

how we can convert those reasons as column heading and there qty can be in row addressing to the concern record.

[June7]

Did you try a Crosstab query?

[cap.zadi]

yes but the sum is not correct for goodqty and rejectqty. plz advise bcz its very urgent to do.

[June7]

Why wasn't it correct? Show the SQL statement you tried, show what the sample output should look like, provide full dataset for testing (Excel or Access file).

[cap.zadi]

Hello Sir, how are you.

here is the attached sample database and i think now all the questions merge into this thread.

can you look at this thread and i am sure you can add some of your expertise.


https://www.accessforums.net/queries...ata-21056.html

thanks