Grouping records: I'll explain. (1 Viewer)

[JGalletta]

I have a query that gathers information from the tables in my database and returns something like this:

Field    Pest
1    Insect1
1    Insect2
1    Insect3
2    Insect1
2    Insect3
2    Insect4

Field and Pest are fields in two separate (but joined) tables. Field exists only once in its table, and multiple pests are attributed to each field. I'm looking to create a table or query in which the values would be listed as follows:

Field    Pest
1    Insect1, Insect2, Insect3
2    Insect1, Insect3, Insect4

The purpose for this is to create a legend for a map used by another program based on the information gathered in the database. I do have other criteria for which fields and pests show up in this query, but this is the general concept I need to gain an understanding of.

Thanks in advance.

 

[boblarson]

Yep, here you go:

http://allenbrowne.com/func-concat.html

 

[JGalletta]

Thank you, sir!

 

[JGalletta]

Ugh.. I just realized that the database will have duplicate field values on occasion due to multiple users entering data for the same field. This creates an issue where the query (with the ConcatRelated function) returns the following:

FieldList    PestList
Field1       Pest1, Pest2, Pest3
Field1       Pest4
Field2       Pest1, Pest2
Field2       Pest2

What do I have to do to concatenate the Pest lists representing each field without creating duplicate list items? Is this beyond the ConcatRelated function?

 

[JGalletta]

The original concatRelated was using JoinPK from the second table in this image to concatenate pestFound values in the third table. Can I concatenate values in the third table based on Field Number in the first table, all while making sure duplicate values don't get concatenated twice (in the pestFound field).

 

[boblarson]

Post what you initially tried for the code.

 

[JGalletta]

This is what I tried at first:

ConcatRelated("pestFound","tblFieldRecInfo","tblFieldRecInfo.JoinPK = " & tblFieldRecJoin.JoinPK)

However, I made a query that builds a table I can use ConcatRelated() with based on Field Number. Everything is working now. Thanks.

P.S. I know this is probably not the best way to go about doing this, but I'm crushed for time and it works.. :-/

 

[boblarson]

Just so you know, for the future, you can use multiple fields in the where clause

ConcatRelated("pestFound","tblFieldRecInfo","tblFieldRecInfo.JoinPK = " & [JoinPK] & " AND FieldNumber=" & [FieldNumber])

 

[JGalletta]

Even if those fields are not in the table whose values are being concatenated?

 

[JGalletta]

This is the horrendous solution I came up with:

    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE tblFieldPestOverThreshold.* FROM tblFieldPestOverThreshold"
    DoCmd.RunSQL "DELETE tblOverThresholdMap.* FROM tblOverThresholdMap"
    DoCmd.RunSQL "INSERT INTO tblFieldPestOverThreshold ( [Field Number], [Pest Found] ) SELECT DISTINCTROW tblFieldInformation.[Field Number], tblFieldRecInfo.pestFound FROM (tblFieldInformation INNER JOIN tblFieldRecJoin ON tblFieldInformation.[Field Number] = tblFieldRecJoin.[Field Number]) INNER JOIN tblFieldRecInfo ON tblFieldRecJoin.JoinPK = tblFieldRecInfo.JoinPK GROUP BY tblFieldInformation.[Field Number], tblFieldRecInfo.pestFound, tblFieldRecInfo.overThreshold, tblFieldRecInfo.Treated HAVING (((tblFieldRecInfo.overThreshold)=True) AND ((tblFieldRecInfo.Treated)=False))"
    DoCmd.RunSQL "INSERT INTO tblOverThresholdMap( [Field Number], [Over Threshold] ) SELECT DISTINCTROW tblFieldInformation.[Field Number], ConcatRelated('[Pest Found]','tblFieldPestOverThreshold','tblFieldPestOverThreshold.[Field Number] = ' & Chr(39)  & [tblFieldInformation].[Field Number] & Chr(39)) AS [Over Threshold] FROM tblFieldInformation INNER JOIN tblFieldPestOverThreshold ON tblFieldInformation.[Field Number] = tblFieldPestOverThreshold.[Field Number]"
    DoCmd.SetWarnings True

 

[boblarson]

Even if those fields are not in the table whose values are being concatenated?

You would just create a query to pull the info together first.

 

[JGalletta]

Ok, great. That's what I did.