Multiple Column for Curr/Prev Yr Qty/Value

M

meangene

Have a table showing period-to-date order cancellation data as follows:
CancReason Year CancQty CancValue
WrongItem 2007 10 150.00
WrongItem 2008 8 130.00
WrongColor 2007 5 75.00
WrongColor 2008 7 98.00

Can't use single crosstab. How can I get a report to display as follows
without subreports?
CancReason 2007CancQty 2007CancValue 2008CancQty 2007CancValue
WrongItem 10 150.00 8
130.00
WrongColor 5 75.00 7
98.00

Thanks!!
 
K

KARL DEWEY

You can have your cake and eat it too!
Substitute your table name for [meangene] ---

TRANSFORM Sum([CancQty]) & " " & Format(Sum([CancValue]),"Currency") AS Expr1
SELECT meangene.CancReason, Sum([CancQty]) & " " &
Format(Sum([CancValue]),"Currency") AS Totals
FROM meangene
GROUP BY meangene.CancReason
PIVOT [Year] & " Cancel QTY - Value";
 
M

meangene

Thanks Karl - I'll give it a shot!

KARL DEWEY said:
You can have your cake and eat it too!
Substitute your table name for [meangene] ---

TRANSFORM Sum([CancQty]) & " " & Format(Sum([CancValue]),"Currency") AS Expr1
SELECT meangene.CancReason, Sum([CancQty]) & " " &
Format(Sum([CancValue]),"Currency") AS Totals
FROM meangene
GROUP BY meangene.CancReason
PIVOT [Year] & " Cancel QTY - Value";

--
KARL DEWEY
Build a little - Test a little


meangene said:
Have a table showing period-to-date order cancellation data as follows:
CancReason Year CancQty CancValue
WrongItem 2007 10 150.00
WrongItem 2008 8 130.00
WrongColor 2007 5 75.00
WrongColor 2008 7 98.00

Can't use single crosstab. How can I get a report to display as follows
without subreports?
CancReason 2007CancQty 2007CancValue 2008CancQty 2007CancValue
WrongItem 10 150.00 8
130.00
WrongColor 5 75.00 7
98.00

Thanks!!
Click to expand...
Click to expand...
 
M

meangene

Karl - Where in report design does this go?

KARL DEWEY said:
You can have your cake and eat it too!
Substitute your table name for [meangene] ---

TRANSFORM Sum([CancQty]) & " " & Format(Sum([CancValue]),"Currency") AS Expr1
SELECT meangene.CancReason, Sum([CancQty]) & " " &
Format(Sum([CancValue]),"Currency") AS Totals
FROM meangene
GROUP BY meangene.CancReason
PIVOT [Year] & " Cancel QTY - Value";

--
KARL DEWEY
Build a little - Test a little


meangene said:
Have a table showing period-to-date order cancellation data as follows:
CancReason Year CancQty CancValue
WrongItem 2007 10 150.00
WrongItem 2008 8 130.00
WrongColor 2007 5 75.00
WrongColor 2008 7 98.00

Can't use single crosstab. How can I get a report to display as follows
without subreports?
CancReason 2007CancQty 2007CancValue 2008CancQty 2007CancValue
WrongItem 10 150.00 8
130.00
WrongColor 5 75.00 7
98.00

Thanks!!
Click to expand...
Click to expand...
 
M

meangene

Forget I asked the report design question (slaps forehead)!

meangene said:
Karl - Where in report design does this go?

KARL DEWEY said:
You can have your cake and eat it too!
Substitute your table name for [meangene] ---

TRANSFORM Sum([CancQty]) & " " & Format(Sum([CancValue]),"Currency") AS Expr1
SELECT meangene.CancReason, Sum([CancQty]) & " " &
Format(Sum([CancValue]),"Currency") AS Totals
FROM meangene
GROUP BY meangene.CancReason
PIVOT [Year] & " Cancel QTY - Value";

--
KARL DEWEY
Build a little - Test a little


meangene said:
Have a table showing period-to-date order cancellation data as follows:
CancReason Year CancQty CancValue
WrongItem 2007 10 150.00
WrongItem 2008 8 130.00
WrongColor 2007 5 75.00
WrongColor 2008 7 98.00

Can't use single crosstab. How can I get a report to display as follows
without subreports?
CancReason 2007CancQty 2007CancValue 2008CancQty 2007CancValue
WrongItem 10 150.00 8
130.00
WrongColor 5 75.00 7
98.00

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
M

meangene

Karl - This did great for creating a crosstab with concatenated fields but I
didn't make my first post clear (sorry). I really needed separate fields in
repective columns on a report that that would allow me to grand total each
column at end of report(.i.e., =Sum([CancQty]) ). They need to see 4 columns
(07 qty, 07 value, 08 qty, 08 value) with sums at end. The table that has the
values is setup per my first post. I can do this with two subreports
sidebyside but curious if cleaner way. Thanks again!

KARL DEWEY said:
You can have your cake and eat it too!
Substitute your table name for [meangene] ---

TRANSFORM Sum([CancQty]) & " " & Format(Sum([CancValue]),"Currency") AS Expr1
SELECT meangene.CancReason, Sum([CancQty]) & " " &
Format(Sum([CancValue]),"Currency") AS Totals
FROM meangene
GROUP BY meangene.CancReason
PIVOT [Year] & " Cancel QTY - Value";

--
KARL DEWEY
Build a little - Test a little


meangene said:
Have a table showing period-to-date order cancellation data as follows:
CancReason Year CancQty CancValue
WrongItem 2007 10 150.00
WrongItem 2008 8 130.00
WrongColor 2007 5 75.00
WrongColor 2008 7 98.00

Can't use single crosstab. How can I get a report to display as follows
without subreports?
CancReason 2007CancQty 2007CancValue 2008CancQty 2007CancValue
WrongItem 10 150.00 8
130.00
WrongColor 5 75.00 7
98.00

Thanks!!
Click to expand...
Click to expand...
 
K

KARL DEWEY

You can use three queries ---

TRANSFORM Sum(meangene.CancValue) AS SumOfCancValue
SELECT meangene.CancReason
FROM meangene
GROUP BY meangene.CancReason
PIVOT meangene.Year;


TRANSFORM Sum(meangene.CancQty) AS SumOfCancQty
SELECT meangene.CancReason
FROM meangene
GROUP BY meangene.CancReason
PIVOT meangene.Year;


SELECT meangene_Crosstab_QTY.CancReason,
Sum(nz([meangene_Crosstab_QTY].[2007],0)) AS [2007 Cancel QTY],
Sum(nz([meangene_Crosstab_VAL].[2007],0)) AS [2007 Canceled Value],
Sum(nz([meangene_Crosstab_QTY].[2008],0)) AS [2008 Canceled QTY],
Sum(nz([meangene_Crosstab_VAL].[2008],0)) AS [2008 Canceled Value]
FROM meangene_Crosstab_QTY INNER JOIN meangene_Crosstab_VAL ON
meangene_Crosstab_QTY.CancReason = meangene_Crosstab_VAL.CancReason
GROUP BY meangene_Crosstab_QTY.CancReason;

--
KARL DEWEY
Build a little - Test a little


meangene said:
Karl - This did great for creating a crosstab with concatenated fields but I
didn't make my first post clear (sorry). I really needed separate fields in
repective columns on a report that that would allow me to grand total each
column at end of report(.i.e., =Sum([CancQty]) ). They need to see 4 columns
(07 qty, 07 value, 08 qty, 08 value) with sums at end. The table that has the
values is setup per my first post. I can do this with two subreports
sidebyside but curious if cleaner way. Thanks again!

KARL DEWEY said:
You can have your cake and eat it too!
Substitute your table name for [meangene] ---

TRANSFORM Sum([CancQty]) & " " & Format(Sum([CancValue]),"Currency") AS Expr1
SELECT meangene.CancReason, Sum([CancQty]) & " " &
Format(Sum([CancValue]),"Currency") AS Totals
FROM meangene
GROUP BY meangene.CancReason
PIVOT [Year] & " Cancel QTY - Value";

--
KARL DEWEY
Build a little - Test a little


meangene said:
Have a table showing period-to-date order cancellation data as follows:
CancReason Year CancQty CancValue
WrongItem 2007 10 150.00
WrongItem 2008 8 130.00
WrongColor 2007 5 75.00
WrongColor 2008 7 98.00

Can't use single crosstab. How can I get a report to display as follows
without subreports?
CancReason 2007CancQty 2007CancValue 2008CancQty 2007CancValue
WrongItem 10 150.00 8
130.00
WrongColor 5 75.00 7
98.00

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
M

meangene

I see it now - thanks!

KARL DEWEY said:
You can use three queries ---

TRANSFORM Sum(meangene.CancValue) AS SumOfCancValue
SELECT meangene.CancReason
FROM meangene
GROUP BY meangene.CancReason
PIVOT meangene.Year;


TRANSFORM Sum(meangene.CancQty) AS SumOfCancQty
SELECT meangene.CancReason
FROM meangene
GROUP BY meangene.CancReason
PIVOT meangene.Year;


SELECT meangene_Crosstab_QTY.CancReason,
Sum(nz([meangene_Crosstab_QTY].[2007],0)) AS [2007 Cancel QTY],
Sum(nz([meangene_Crosstab_VAL].[2007],0)) AS [2007 Canceled Value],
Sum(nz([meangene_Crosstab_QTY].[2008],0)) AS [2008 Canceled QTY],
Sum(nz([meangene_Crosstab_VAL].[2008],0)) AS [2008 Canceled Value]
FROM meangene_Crosstab_QTY INNER JOIN meangene_Crosstab_VAL ON
meangene_Crosstab_QTY.CancReason = meangene_Crosstab_VAL.CancReason
GROUP BY meangene_Crosstab_QTY.CancReason;

--
KARL DEWEY
Build a little - Test a little


meangene said:
Karl - This did great for creating a crosstab with concatenated fields but I
didn't make my first post clear (sorry). I really needed separate fields in
repective columns on a report that that would allow me to grand total each
column at end of report(.i.e., =Sum([CancQty]) ). They need to see 4 columns
(07 qty, 07 value, 08 qty, 08 value) with sums at end. The table that has the
values is setup per my first post. I can do this with two subreports
sidebyside but curious if cleaner way. Thanks again!

KARL DEWEY said:
You can have your cake and eat it too!
Substitute your table name for [meangene] ---

TRANSFORM Sum([CancQty]) & " " & Format(Sum([CancValue]),"Currency") AS Expr1
SELECT meangene.CancReason, Sum([CancQty]) & " " &
Format(Sum([CancValue]),"Currency") AS Totals
FROM meangene
GROUP BY meangene.CancReason
PIVOT [Year] & " Cancel QTY - Value";

--
KARL DEWEY
Build a little - Test a little


:

Have a table showing period-to-date order cancellation data as follows:
CancReason Year CancQty CancValue
WrongItem 2007 10 150.00
WrongItem 2008 8 130.00
WrongColor 2007 5 75.00
WrongColor 2008 7 98.00

Can't use single crosstab. How can I get a report to display as follows
without subreports?
CancReason 2007CancQty 2007CancValue 2008CancQty 2007CancValue
WrongItem 10 150.00 8
130.00
WrongColor 5 75.00 7
98.00

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
E

Evi

If Year is really the name of your field, and not just included for clarity
in your post, you'll need to change it. It's a reserved word.
Evi
meangene said:
I see it now - thanks!

KARL DEWEY said:
You can use three queries ---

TRANSFORM Sum(meangene.CancValue) AS SumOfCancValue
SELECT meangene.CancReason
FROM meangene
GROUP BY meangene.CancReason
PIVOT meangene.Year;


TRANSFORM Sum(meangene.CancQty) AS SumOfCancQty
SELECT meangene.CancReason
FROM meangene
GROUP BY meangene.CancReason
PIVOT meangene.Year;


SELECT meangene_Crosstab_QTY.CancReason,
Sum(nz([meangene_Crosstab_QTY].[2007],0)) AS [2007 Cancel QTY],
Sum(nz([meangene_Crosstab_VAL].[2007],0)) AS [2007 Canceled Value],
Sum(nz([meangene_Crosstab_QTY].[2008],0)) AS [2008 Canceled QTY],
Sum(nz([meangene_Crosstab_VAL].[2008],0)) AS [2008 Canceled Value]
FROM meangene_Crosstab_QTY INNER JOIN meangene_Crosstab_VAL ON
meangene_Crosstab_QTY.CancReason = meangene_Crosstab_VAL.CancReason
GROUP BY meangene_Crosstab_QTY.CancReason;

--
KARL DEWEY
Build a little - Test a little


meangene said:
Karl - This did great for creating a crosstab with concatenated fields but I
didn't make my first post clear (sorry). I really needed separate fields in
repective columns on a report that that would allow me to grand total each
column at end of report(.i.e., =Sum([CancQty]) ). They need to see 4 columns
(07 qty, 07 value, 08 qty, 08 value) with sums at end. The table that has the
values is setup per my first post. I can do this with two subreports
sidebyside but curious if cleaner way. Thanks again!

:

You can have your cake and eat it too!
Substitute your table name for [meangene] ---

TRANSFORM Sum([CancQty]) & " " & Format(Sum([CancValue]),"Currency") AS Expr1
SELECT meangene.CancReason, Sum([CancQty]) & " " &
Format(Sum([CancValue]),"Currency") AS Totals
FROM meangene
GROUP BY meangene.CancReason
PIVOT [Year] & " Cancel QTY - Value";

--
KARL DEWEY
Build a little - Test a little


:

Have a table showing period-to-date order cancellation data as follows:
CancReason Year CancQty CancValue
WrongItem 2007 10 150.00
WrongItem 2008 8 130.00
WrongColor 2007 5 75.00
WrongColor 2008 7 98.00

Can't use single crosstab. How can I get a report to display as follows
without subreports?
CancReason 2007CancQty 2007CancValue 2008CancQty 2007CancValue
WrongItem 10 150.00 8
130.00
WrongColor 5 75.00 7
98.00

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report with Monthly rows, Yearly columns & 3 pieces of data per mo 2
Crosstab Query based on Ordinal Values 6
VB Code 4
Please Help with Query to Return a Balance Column 5
Finding a value in a cell matching up with a column/row 1
Sum row value across multiple columns 2
I need to help with Excel (beginner) -- year row and month column 2
AVERAGEIFS Multiple Criteria: use an Array? 7

Top