Data Labels with Values in crosstab query or report

[omondragon]

Is there a way to include the data label as part of data. For example I have the following data set:

Quarter Product Price Qty
Q1 Product A $5 500
Q2 Product B $10 1000
Q3 Product C $5 1500
Q4 Product D $20 500

I would like to see a table that has Price as a Row Heading, Quarter as a Column Heading and both the Product and Qty in the detail (so that they are grouped into as few rows as possible at the row headings). For example:

Price Q1 Q2 Q3 Q4
5 500 units of Product A 1500 units of Product C
10 1000 units of Product B
20 500 units of product D

Does anybody have any idea of how i can accomplish this within Microsoft Access (any which way, be it query, report or a combination of)?

Any help would be appreciated!!!

(Sorry if my tables above are not that great but i don't know how to input this in.)

 

[dhookom]

Create a crosstab with this SQL view:

TRANSFORM First([Qty] & " Units of " & [Product]) AS Expr1
SELECT tblDataSet.Price
FROM tblDataSet
GROUP BY tblDataSet.Price
PIVOT tblDataSet.Quarter;

This will work with your sample data but might not work with actual records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GirlBlonde]

hi

a solution to this is not to do a crosstab if you are doing exact colums all the time. just write them into your query headings yourself. see below SQL.

SELECT example.price, IIf([quarter]="Q1",[quantity] & " " & [product]) AS ['Q1], IIf([quarter]="Q2",[quantity] & " " & [product]) AS ['Q2]
FROM example
GROUP BY example.price, IIf([quarter]="Q1",[quantity] & " " & [product]), IIf([quarter]="Q2",[quantity] & " " & [product]);

hope this helps

Energy & persistance conquer all things!

 

[dhookom]

GirlBlonde,
I believe your solution will create two lines in the result for the Price = 5.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GirlBlonde]

hi
there are no duplicates from my queries i only gave you Q1 & Q2 examples only thats why proberly why thinks price 5 is duping. below is the outcome of query using your exact table properties.

price 'Q1 'Q2 'Q3 'Q4
5 1500 of Product C
5 500 of Product A
10 1000 of Product B
20 500 of Product D

i have added a 'of' in the middle as i saw you wanted it in there. Below is the complete SQL

SELECT example.price, IIf([quarter]="Q1",[quantity] & " of " & [product]) AS ['Q1], IIf([quarter]="Q2",[quantity] & " of " & [product]) AS ['Q2], IIf([quarter]="Q3",[quantity] & " of " & [product]) AS ['Q3], IIf([quarter]="Q4",[quantity] & " of " & [product]) AS ['Q4]
FROM example
GROUP BY example.price, IIf([quarter]="Q1",[quantity] & " of " & [product]), IIf([quarter]="Q2",[quantity] & " of " & [product]), IIf([quarter]="Q3",[quantity] & " of " & [product]), IIf([quarter]="Q4",[quantity] & " of " & [product]);


Energy & persistance conquer all things!

 

[dhookom]

GirlBlonde,
If you look at the results of your query, you will see two rows in the output with [blue]5[/blue] under the price column. The requested output has only one such row.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GirlBlonde]

hi dhookom
please just disregard that then thats my input. good pick up.

Energy & persistance conquer all things!