In a Microsoft Access report, how can I display each record of a field as a column heading with the records of other fields in that columns records beneath it.
My query gives me data in the following format:
| ID | Item | Item Characteristic 1 | Item Characteristic 2 | Other Fields |
|:--:|:------:|:---------------------:|:---------------------:|--------------|
| 22 | Code 1 | Blue | 48 | … |
| 22 | Code 2 | Red | 50 | … |
| 22 | Code 3 | Green | 99 | … |
I'd like to have on my report to look something like this:
| Heading | Data True to All Records1 | More Data True to All Records2 | |
|:---------------------:|:-------------------------:|:------------------------------:|:------------:|
| ------------ | ------------ | ------------ | ------------ |
| Item | Code 1 | Code 2 | Code 3 |
| Item Characteristic 1 | Blue | Red | Green |
| Item Characteristic 2 | 48 | 50 | 99 |
| Other Fields | … | … | … |
| ------------ | ------------ | ------------ | ------------ |
| Footer | Data True to All Records3 | More Data True to All Records4 | |
Currently, I can only get data in the format:
| Heading | | |
|:-------:|:-----:|:--:|
| ---- | | |
| Code 1 | Blue | 48 |
| | | |
| Code 2 | Red | 50 |
| | | |
| Code 3 | Green | 99 |
| --- | | |
| Footer | | |
Where each record is resulting in a new 'row' in the report.
Can anyone help?
For posterity, I resolved this by setting out a table of unbound labels.
I gave each of these labels a control name of x-y, where x was the column number and y was the row number.
I then looped through each column and row and changed the caption of the label to the value from my RecordSet.
(Form("FormName").Controls.Item(x & "-" & y)).Caption = .Fields("FieldName")