Report: Detail Section

[notoca]

Hi

I hope I can explain this to make sense. I've designed a report that gets it's data via a query based upon 2 tables. All the fiedls except 1 have been put in the group header section. These fields all come from 1 table. The remaining field (which comes from the 2nd table) is in the detail section of the report. This field can have a multiple values to the key field in the group header area. That's why I had to design it this way. Anyway the report works. However, I would like to tidy up the presentation of the field in the detail section. If there are, for example, 5 values for this field, then they are listed 1 beneath the other i.e. they take up 5 lines. What I'd like to do is to list the values on one line reading from left to right with a space between each value and wrapping to a second line if there are a lot of values in this field.

Is this possible? If so, how do I do it simply.

Thanks

 

[Joe4]

You may want to investigate using a CrossTab query (see Access Help) to get all your values in one record. Then you can do another query which puts all the values into one field.

From there, you can put the field on the report, and use the Can Grow property to allow it to expand to two rows if necessary.

 

[notoca]

Hi

They are already part of the same record. It's just dispalying left to right that I need to do.

 

[Joe4]

do another query which puts all the values into one field. 

From there, you can put the field on the report, and use the Can Grow property to allow it to expand to two rows if necessary

Did you try what I suggested above? If that will not work for you, please explain why. A concrete example would also be helpful.

 

[notoca]

Sorry I'm not sure I understand what you're telling me. I think I should clarify what I am doing. For simplicity I have a table with two fields called fieldkey and fieldname1. Fieldname1 can have multiple values for each key.

Sample data

Fieldkey Fieldname1
1 Asset
1 Equipment
1 Portal
1 Grant

2 Computing
2 Printer

3 Asset
3 Computer

When I produce the report it looks like this:

1
---
Asset
Equipment
Portal
Grant

2
--
Computing
Printer


3
----
Asset
Computer

How I would like it to look is:

1
---
Asset Equipment Portal Grant

2
---
Computing Printer

3
----
Asset Computer

It might even be better to put in a comma as well to separate the words.

The environment will like this as it will reduce the amount of paper required to print the reports.

I did have a quick look in help for cross queriews but didn't understand it. I got the impression that cross queries were for numeric and statistical data.

 

[Joe4]

If you add another field called EntryNum, then you can use a CrossTab query. Basically, this is just the number of the entry for that particular Fieldkey. Your table would look like:

FieldKey	EntryNum	FieldName
1	1	Asset
1	2	Equipment
1	3	Portal
1	4	Grant
2	1	Computing
2	2	Printer
3	1	Asset
3	2	Computer

Then you could do a CrossTab query (use the CrossTab Query Wizard). Select your FieldKey as the Row Headings and EntryNum as your Column Headings and then the First Function for your FieldName. Be sure to uncheck the box that says "Yes, include row sums", then finish.

The resulting CrossTab query should look like:

FieldKey	1	2	3	4
1	Asset	Equipment	Portal	Grant
2	Computing	Printer		
3	Asset	Computer

Now, simply do a query on this CrossTab query. Select the FieldKey as your first query record, and create an expression to combine the other four fields, like:

MyList: Table1_Crosstab![1] & IIf(Table1_Crosstab![2],", " & Table1_Crosstab![2]) & IIf(Table1_Crosstab![3],", " & Table1_Crosstab![3]) & IIf(Table1_Crosstab![4],", " & Table1_Crosstab![4])

The results of this query should give you your desired results:

FieldKey	MyList
1	Asset, Equipment, Portal, Grant
2	Computing, Printer
3	Asset, Computer

 

[notoca]

Thanks Jmiskey. I'll give that a go