Combine multi-value field in Report

[lineone]

I have two tables

The first, MainTBL, has the following fields:

ID [Primary Key]
DefFIRST [text field]
DefMI [Text field]
DefLAST [Text field]
DefSUF [Text field]
DefDOB [Date/Time field]
Charge [Number - lookup field]
and several other fields.

The second table, ChargesTBL has the following fields:

ChargeID [Primary Key]
ID [number - linked to MainTBL]
Charge [Text]

The Charge field in ChargesTBL can hold many different values for each person in the MainTBL.

I would like to create a report that is grouped by DefLast, and lists all of the values of the charge field in a text box, separated by a comma.

The reports I've currently created list each charge on a separate line.

Thanks in advance.

[zmbd]

lineone
Unless you are creating a webapp/sharepoint site I highly advise that you change your lookup field
[Charge [Number - lookup field]]
to a plain numeric and here is why:
The Evils of Lookup Fields in Tables

You can and should normally create your lookup fields within either a query or form.

Set up your table relationships via the Database tools.
(if in ACC2013 you can get at this from the table design view too)

As for what you are asking, I assume you mean something like what is shown in the post >here< for the example report.

Once you have your relationships setup properly, you can build a query that pulls the main table and related table(s), verify the relationships are correct, and return the fields of interest. You might open the database referenced in the above post and follow the instructions there to see how the report is constructed.

Let us know how you progress...

[lineone]

ZMBD,

Thanks much for your prompt reply. I'm still a bit stumped (I'm new at this), and believe that I may not have describe how I actually setup the tables and relationships. I've included the database in the hope you can guide me through my inexperience. I'm using Access 2013 but saved this in the 2003 version to make it more accessible. Again, thanks for all you've done.

[zmbd]

I'll send you a copy of my "tool kit" via PM

You may also find the following to be useful if you are having trouble creating your relationships:
How to define relationships between tables in an Access database

This describes a lookup field: Create a lookup field which I personally avoid as noted in my first reply. :)

[lineone]

Thanks, ZMBD.

I was wrong about my Charge field being a lookup; it is indeed a numeric field. It seems I'm merely having a problem creating a report that will put all of the values from ChargesTBL.Charge into a text box, with each charge separated by a comma rather than a new line. I didn't explain myself very well. A JPG is attached showing my relationship between the two tables.

Thanks, again

My original post should have read:

I have two tables

The first, MainTBL, has the following fields:

ID [Primary Key]
DefFIRST [text field]
DefMI [Text field]
DefLAST [Text field]
DefSUF [Text field]
DefDOB [Date/Time field]
Charge [Number]
and several other fields.

The second table, ChargesTBL has the following fields:

ChargeID [Primary Key]
ID [number - linked to MainTBL ID Primary Key]
Charge [Text]

The Charge field in ChargesTBL can hold many different values for each person in the MainTBL.

I would like to create a report that is grouped by DefLast, and lists all of the values of the charge field in a text box, separated by a comma.

The reports I've currently created list each charge on a separate line.

[zmbd]

Ok, you'll need a tad of code.
Before I potentially lead down the wrong path, let me repeat things:

Currently you have something like:

Family PK - Family Name - - Retreat Name
1           Alpha           Annunciation
                            Dummy Data Rulles
 
2           Beta            Annunciation
                            Getting to know your spouse
...
15          November        Nativity
                            (etc...)                             

Instead you would like:

Family PK - Family Name - - Retreat Name
1           Alpha           Annunciation, Dummy Data Rulles
 
2           Beta            Annunciation, Getting to know your spouse, 3rd, 4th, etc...
...
15          November        Nativity, 2nd, 3rd, 4th, etc...
(etc...)

[lineone]

Yes, ZMDB, I couldn't have illustrated it better. Thanks.

[zmbd]

Ahh... I have a function built for that... but it is on the toasted-pc... arrrgghh/

If I can find my backup I'll post


The concept is to build a custom function.
It opens a record set that loops thru the related records based on the current record and builds the string then returns it.

I am pretty sure we've covered this exact thing about a year or so ago...

[NeoPa]

Combining Multiple Rows of one Field into One Result has an example of such a function.