Display Data in Report 1

[dulla]

Hello. I am trying to display data from 3 queries in a report which shows quarterly data. i am having trouble displaying the query that pulls the quarterly data per year. i would like it to be as:

Quarter 1 Quarter 2 Quarter 3 Quarter 4 Year Totals

in five columns and have the data be columnar going across all five tabs. the problem is that when i choose a column style report, columns are created but they are place vertically on the report as:

quarter 1
data
data
data...

quarter 2
data
data
data...

quarter 3
data
data
data...

quarter 4, etc.

how do i get the columns to display horizontally? can i change the size of font and fields to fit to make the difference? thanks.

ameen

 

[randysmid]

Hi,
You will want a crosstab query and report. I wrote an FAQ on this topic: faq703-2868
HTH,
Randy Smith
California Teachers Association

 

[dulla]

you see, i have almost 100 fields. 50 number fields and 50 percent fields. they are very tiny and would fit. the crosstab query wants this format

quarter field 1 field 2 etc.,...
1 data
2 data
3 data
4 data

i need:

quarter1 quarter2
field1name value,field2value value,field2value
field3name value,field4value value,field3value
filed5name
......
field50name value,field51value value,field51value


i would only have the fields for each individual quarter, but VALUES for all 4 quarters that i would want to be displayed. Half of my fields are percent values and i want them lined up beside their corresponding field values. alothough it is possible to fit, but access's defaults create the column on top of each other and not side by side. the crosstab query will not accomodate that many fields that i have. what to do?

ameen

 

[randysmid]

Hi,
You may need to create another query to appear before the crosstab query runs. Then set use that query to be the recordsource for the crosstab query. I wrote an FAQ on how to create a custom field, which in your case is the $$ for each quarter. Here is the FAQ: faq701-3273

Your custom field should look like this:
Quarter1SalesAmt: IIf(Month([InvoiceDate]=1 or [InvoiceDate]=2 or [InvoiceDate]=3, [SalesAmt], 0)

I haven't time to test it thoroughly, but this should get you real close. Unfortunately, there doesn't appear to be a Quarter function within Access queries. HTH,
Randy Smith
California Teachers Association