Putting multiple values into one field

[Jorgandr]

If I were to make a report that includes an intersectioin table in the query it would contain multiple records for a particular Field in the primary table. This would then make my reports show one line per record, when I'd rather have all the intersection field data in one field.
Example below:

Report shows

CompanyName GuarantorName
----------- -------------
ABC Company John Doe
ABC Company Jane Doe
XYZ Company John Smith

I'd rather it look like this

Company Name Guarantor Name
------------ --------------
ABC Company John Doe, Jane Doe
XYZ Company John Smith

Is there any way to do this? I would think that this would be a common need/issue? Thanks.

 

[GShen]

You could group the company name and have it look like this

ABC Company
John Doe
Jane Doe
xxxxx xxx
XYZ Company
John Smith
Bob "

etc. etc.


Remember when... everything worked and there was a reason for it?

 

[Jorgandr]

Thank you for the information, however if had a report that had 12 fields and then the guarantor name, it looks ugly to group the records and only have the guarantor name on each separate line. It would look much nicer if they were all in one text field separated by commas.

 

[Spenney]

You could have a report with a record source of just the company name (and identifier if you have one), and then a sub-report with a record source of company name and guarantor name. You could then format the sub-report to display its records in multiple columns. It would keep the number of rows to a minimum, but the variable nature of a names length may not make it to your taste.

It's all in the eye of the beholder I suppose, but personally I think it would be neater using the group by or hide duplicates of the company name method.

 

[snyperx3]

Solution:

1) On your form set up the grouping for company name, with a footer only.

2) In the details section place the field GuarantorName.

3) In the footer for the company, place the field CompanyName and an unbound text box named "txtNames"

4) VBA Code for the report. paste the following in your vba section

Option Compare Database
Public gNames As String

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    gNames = gNames & "," & GuarantorName
    Cancel = True
End Sub

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
    txtNames.Value = gNames
    gNames = ""
End Sub

Run the report

the details wont display because it is being canceled.

-Pete

 

[dhookom]

Another option is the generic concatenate function in faq701-4233.

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]