By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,436 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Converting Query Record Source to Columns in a report

P: 9
For a report, I am using a query as my record source. The query produces 5 columns by 3272 records.

The data in columns 1 & 2 (“Objective” and “Rating”) repeats every 409 records The data for “initial” and “Date” are unique for the individual. Currently there are 8 individuals (409 * 8 = 3272)


. . . . .. . . . .Objective . . . Rating . . Initial . . . .. Date . . . . . Name
[rec0001]. . . . .abc. . . . . . .a. . . . . . jg. . . . . .1/12/07 . . . . .Bob


[rec0409]. . . . .xyz. . . . . . .f. . . . . . mc. . . . . .6/30/08 . . . . . Bob
[rec0410]. . . . .abc. . . . . . .b. . . . . . dt. . . . . .4/15/08 . . . . . Jim



[rec3272]. . . . .xyz. . . . . . .c. . . . . . do. . . . . .7/15/08 . . . . . Steve



I would like the report to display these records as

Heading. . . . . . . . . . . . . . . . . . . . . Bob. . . . . . . . . . Jim. . . . . . . . . . . . . . . Steve
. . . . . . . . . . Obj . . Rating. . . . Initial . . Date. . . . Initial . . Date. . . -> . . .Initial . . Date
. . . . . . . . . . ------ . ---------. . . ------- . -------- . . . . ------- --------- . . . . . . . ------- . ---------
[rec0001] . . . abc . . . a . . . . . . jg . . 1/12/07 . . . . dt . . 4/15/08 . . . . . . . df . . 1/21/06


[rec0409] . . . xyz . . . f . . . . . . mc . . 6/30/08 . . . . br . . 3/31/07 . . . . . . . se . . 9/23/08

When using the original query, I get a 72 Page report rather than the desired 11 page report. I am uncertain whether to do this as another query, a recordset in the detail_Print(), an array, or what. Any suggestions on how to limit the first three columns to 409, while place the remaining, non-repeating data in additional columns? Not even sure which would be the best approach to this in order to research it. Any assistance/direction is appreciated in advance.
Jun 19 '08 #1
Share this Question
Share on Google+
1 Reply


100+
P: 167
Hi!

It looks like you could use crosstab query for this.
Try to make a CQ that has Name as Column.
That way you will get one column for every Name (john; steve;...8th person)

After you that try to make a new Select query combining the data from CQ with the query containing the rest of the data.

Also you can refer to help in Access to understand more about Crosstab queries...

Best,
H.
Jun 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.