Combining text data from multiple records in a single report field

[motaman]

here is the scenario: I am trying to create an owners certificate where there may be 1,2,or more owners for a single item (investment property). I have a many-to-many-to-many relationship... a customer can own more than one property, either singely or in multiple buying units,
Property <<-->>BuyingUnit<<-->>Customer
I have a Cust table holding demographic info on individuals, a Property table holding info on individual properties, and a BuyingUnit table holding info on groups of customers that own a particular property.
When I create a query to isolate the owners of a property I get a row for every hit, if there are two owners, 2 records, if there are 6 owners, 6 records.
How can I create a single concatenated field of all the owner names? ideally this would happen at the query level so the report generation could happen without operator intervention.

 

[MoGryph]

Ugly little problem. I've been wondering about it myself for awhile now.
Lucky for you AND me, I just figured it out.

First of all, you don't need a special query for this. It can all be done in the design of the report. Just as long as you don't mind getting your hands dirty with a little Visual Basic for Applications (VBA). I have a HUGE listing here of how to do it, but it's really quite simple. I'm just going to be very descriptive for those people that don't know anything about VBA and property pages, etc...

I wrote 2 pieces of code, depending on how your tables and their Key fields (Primary Keys) are set up.
Incidentally, I don't believe that what you have is a Many-To-Many-To-Many relationship. What it's actually called, is just &quot;Many-To-Many&quot;. I suppose if you were to speak of the Physical relationship completely, it would be a &quot;Many-To-One-To-Many&quot;, as there is only ONE record in the &quot;Bridge&quot; or &quot;Junction&quot; table, for each Property<->Customer link (ie, one customer can't own One property more than once.) That's the premise behind this Bridgeing- The Bridge has a primary key that is made up of the 2 foreign keys from the other tables.
Anyhow, that's all syntactics, and I only mention it here because when talking about such things, it's sometimes easier and quicker if we all know the correct terminology. No disrespect meant.

Anyhow-
Here's what I got for ya (I break away, due to different ways that the tables can be designed, so watch out for that.)

Use the Report Wizard, to create a report, based on the BuyingUnit (bridge) table.
Use the fields as they are in that table (ie, don't switch in the drop down to the specified tables where the values came from). And select the rest of the fields you need.
After clicking Next, you will be shown, what I call the Grouping Property Page of the wizard (though it's not actually titled). Change the group if you need to, but- the Grouping level should be your Property, not your Customer (as it happend to have come up on mine).

Continue on, selecting all your wanted options, and Finish the wizard.

Here's where I stop and split. Two seperate methods for 2 different Table Design implementations.

------- TABLE DESIGN ONE ---- ---- Displayed Fields ARE the Primary (key) Fields -----
IF - the Primary Keys for the two tables, are the actual text values that you want displayed (I hope not, you wouldn't want the Customer's name to be it's Primary (Unique) key, but I dont' know how you set them up), then this is what you can do:

Go into Design Mode of the report.
Select the Customer field (it should be in the DETAIL section), and hit ALT-ENTER (short-cut to bring up the Properties). On the Format Tab, change the Visible Property to No (we need this field to pull data from, but we DON'T want to display it here!).
Now, Right-Click on any part of the form (not a control though). Select &quot;Sorting and Grouping&quot;. Change the &quot;Group Footer&quot; property to &quot;Yes&quot;.
Next- Create a new LABEL in the Group Footer section, to the approx. size that you think will require the length of all the names. Type a &quot;.&quot; in the label. It doesn't really matter what you type in it, because it will be replaced by our code, but if you type nothing, the label won't &quot;stick&quot; so-to-speak. ALT-ENTER to bring up Properties, and on the Other tab, give it a name of 'CustText'.
I'm done with adding the controls here. Now for some fun code....
Left-click somewhere in the Detail area (form, not control again). Again, ALT-ENTER.
On the EVENTS tab, click the down arrow next to the property box for &quot;On Format&quot;. Select &quot;[Event Procedure]&quot;. Click the Build button (ie - &quot;...&quot.
Now, it's set up for your Detail_Format event's subroutine. We'll get to that in a second.
At the top of the code page, right under the 2 top &quot;Option&quot; lines, add a new line that says &quot;Public OwnerString As String&quot;.

Next,
Go back to the Detail_Format event Sub.
You need to add code so it looks like this :

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Check for null string, if null, append nothing, otherwise, concatinate _
a seperator, and the current instance of the customer's name.
OwnerString = IIf(OwnerString = &quot;&quot;, &quot;&quot;, OwnerString & &quot; AND &quot & _
Customer.Properties(&quot;Text&quot
End Sub

Change where it says &quot;Customer&quot; to the name of the Customer field that you hid.

You can modify what gets put in as you want- my way gives you &quot;Mr. Smith AND Mrs. Smith AND Smith Kids&quot; etc. etc. etc.

Up near the top, you should see 2 drop down boxes right next to each other. If you're still in the Detail_Format sub, the left one should say &quot;Detail&quot;. Click on it, and select GroupFooter0 (or whatever number it's given to the Group Footer). IN the Right box, select &quot;Format&quot; if it's not already there.

Add lines so that it looks like the following:
Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Me.CustText.Properties(&quot;Caption&quot = OwnerString ' Set the Caption to the _
concatinated string
OwnerString = &quot;&quot; ' Clear the public variable.
End Sub

THAT... should be it. If not, and you took the other (better) approach to creating the table (correctly setting up unique primary keys, and linking to them with the bridge, using a Lookup on the Unique key, and requested fields), this is how you would do it.
You may have to do a lot of field name changing, since I have no idea how you set it up.

------- TABLE DESIGN TWO ---- ---- Displayed Fields ARE the LOOKUPS for the Primary (key) Fields -----
Go into Design mode for the report. Click on the little box in the far upper left of the form (where the vertical and horizontal rulers meet). Press ALT-ENTER (Shortcut for pulling up Properties).
On the Data Tab, click inside the box next to the Record Source property. You should see the Build button show up (&quot;...&quot. Click it. Click yes, you DO want to invoke the Query Builder on a table.
Double Click the astrisk (&quot;*&quot in your {BRIDGE} table field listing at the top. Now, next to that field listing, RIGHT-CLICK on the back, and you should see a bunch of options, including &quot;Show Table&quot;. Click that one.
Select {PROPS} and click add. Select {CUSTS} and click add. Click Close.
On the {PROPS} Field listing, double click the name of the field that contains the Textual field data for the property name.
On the {CUSTS}, double click the name of the field(s) that contain the customers names. (hopefully, you set them up as seperate &quot;LastName&quot; and &quot;FirstName&quot; fields, if not, don't worry 'bout it.
Now, these fields should be set up in your query....
On the Menubar, click File, and select Close.
Say YES, you do want to save your changes.

Now... RIGHT-CLICK anywhere on the form (not on a control) and select &quot;SORTING AND GROUPING&quot;.
Change the Group Footer property to YES, and the . Close the Sorting and Grouping dialog box.
Add a new label to the Footer (not the Page footer, the footer with the name of your {PROPS}'s primary key. Mine's &quot;PropID Footer&quot. Place a &quot;.&quot; in the label (see previous design for an explaination why), and hit enter.
Up near the top of Access, you should see a button that looks like a Wrench and a Hammer that are crossed. To the left of this, is the &quot;Field List&quot; button. Click it. You should see all your fields.

Hold down the CTRL button, and click each of the following:
The Customer's Last name field, the Customer's FirstName field.
Now, click on any of them, and drag the fields up to the form... into the Detail section.
Press ALT-ENTER. On the FORMAT page of the Properties Dialog, change the Visible property to NO.
Click on the CustomerID field (whatever it's called), go into properties (ALT-ENTER), and on the Format Tab, change it's &quot;Visible&quot; to No as well.

Click on the PropertyName field in the Fields List, and drag it up to the Property Group Footer.

Now we're ready for code...
Left-Click on the Detail section, and bring up Properties again.
On the EVENT tab, click in the Property field of On Format. Click the DropDown arrow, select &quot;[Event Procedure]&quot; Click the Build &quot;...&quot; button.
Modify the Detail_Format event sub to look like the following, but change the references to &quot;Me.CustLast.Value&quot; and &quot;Me.CustFirst.Value&quot; to be the names of the respective fields for Last and First names of your customers.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
OwnerString = IIf(OwnerString = &quot;&quot;, &quot;&quot;, OwnerString & &quot; AND &quot & _
Me.CustLast.Properties(&quot;Text&quot & &quot;, &quot; & Me.CustFirst.Properties(&quot;Text&quot
End Sub

At the top of the page, under the 2 OPTION statements, add the line:

Public OwnerString As String

In the top box on the left, that now says &quot;(General)&quot; select &quot;GroupFooter1&quot; (or whatever number they gave it).
If, the box on the RIGHT does not say &quot;Format&quot;, then click in it, and select that event.
Modify the new sub to read as follows (again, with the number automatically given, not necessarily &quot;1&quot;:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Me.CustText.Properties(&quot;Caption&quot = OwnerString
OwnerString = &quot;&quot;
End Sub

That, should be it...
You can close the Code page now.


The last things you have to do are just for aesthetics... You can move the fields we hid to the top of the Detail section, resize them to be nothing but a line, and then resize the Details section to be nearly nothing now. This way you don't have huge gaps between your records.

Well... That's it. Sorry if I was long-winded, I wanted to write this in a way that ANYONE could do it. Hope it didn't make the content too difficult to weed out.

Good luck, and keep coding...