CONCATENATE

[florent prunier]

Hello,

Imagine a very simple case study where I work on two fields, let’s say:
<Species> and <Locality>. I want to report the results of an inventory so
that I create a straightforward report.

So far, so good: all my information is correctly sorted. Unfortunately this
solution combining just few hundreds <Species> and few dozens <Localities>
takes thousand of lines! It soon becomes rather tedious to read and not
synthetic at all.

Looking in the archives of this forum, I found the very useful link to a
CONCATENATE function posted by D. Hookom

"There is a generic concatenate function and sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane."

This is really great. This works for a ONE to MANY relation. Unfortunately,
in my case, the relationship is the inverse sense, Mi set up is:
one <Locality> to many <Species> when I want to concatanate Localities
WITHIN species.

Is there another reference? Cheers

 

[JoyAA]

Search Microsoft.com for Article ID 210163: "How to Concatenate a List of
Items from a Many-Side Table"

 

[BruceM]

Have you tried grouping the report by Species? In report design view, click
View > Sorting and Grouping. One possibility is to group by Lacality
(select the Locality field, and set group Header to Yes). In the group
header's properties you can set the Force New Page property as desired. You
could place a text box bound to the Locality field in the group header, and
place the Species text box in the Detail section.
I'll just toss the idea out there and see if it could work for you. I don't
know the structure of your database, but one possibility is that each
locality may contain many species, and some species may be found in may
localities, so there is a junction table in your database, but I don't know
anything for sure, so I won't get too deep into the options just yet.

 

[Duane Hookom]

What's the difference between "ONE to MANY relation" and "one <Locality> to
many <Species>"?

If you could provide your table structure and desired results, someone could
help you.

 

[BruceM]

I meant to ask is you have tried grouping by Locality. The rest of the
reply was OK, other than some spelling.

 

[florent prunier]

Sorry for being unclear,

I use 4 main tables:

Table Locality: LocId(key)
Table Samples: LocId; SampleId(key)
Table SampleResults: SampleId; SpeciesId(Accept duplicates)
Table SpeciesDictionary: SpeciesId(key)

Locality is linked (one to many) with Samples. Samples is linked with
SampleResults (one to many). SpeciesDictionary is linked with SampleResults
(one to many). SampleResults is the Junction table.

I prepare a query with LocalityId;SpeciesId;SampleId to summarize all my
records and I would like to create a report such as:

Species 1
Sample 1, Sample 2, Sample 3
Species 2
Etc.

NB: Not every species present in SpeciesDictionary has a record within the
samples. There are duplicates of Species for the same sample.

I hope this is clear enough

 

[florent prunier]

I have looked at the:

ACC2000: How to Concatenate Data from the 'Many' Side of a Relationship

that's heading in the good direction but I have no clue what is the
"report's Declarations section" (point 9)

Can you help?

**********************************************************
8. Add an unbound text box named AllProducts to the report's Category Name
footer section.

9. Add the following lines to the report's Declarations section if they are
not already there:Option Compare Database ' Use database order for string
comparisons.
Option Explicit
Dim FirstPass As Integer


10. Add the following code to the CategoryName header section's OnFormat
property:Sub grpHeaderCategoryID_Format (Cancel As Integer, FormatCount As
Integer)

Me!AllProducts = Null
FirstPass = False

End Sub


11. Add the following code to the Detail section's OnFormat property:Sub
Detail_Format (Cancel As Integer, FormatCount As Integer)
On Local Error GoTo Detail_Format_Err
If Not FirstPass Then
Me!AllProducts = Me![ProductName]
FirstPass = True
Else
Me!AllProducts = Me!AllProducts & ", " & Me![ProductName]
End If
Detail_Format_End:
Exit Sub
Detail_Format_Err:
MsgBox Error$
Resume Detail_Format_End
End Sub

 

[florent prunier]

I found it (1st time I use VBA really). Many thanks!

 

[Duane Hookom]

I assume from another post, you found a solution. This is good since I can't
see anything in your table structures that you would want to concatenate as
"Sample 1, Sample 2, Sample 3". Now, if this was Location 1, Location 2,...
that would make more sense.