Concatenate Related Records

[GendoPose]

Hi All,

In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.

Currently my query looks like this;

As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.

If you need any more info/screenshots, please ask.

Thanks

 

[jdraw]

I think you may need to Normalize your tables, but we don't know anything about your "business".
Can you show us a jpg of your relationships window?

You might also want to show us the SQL view of your Query.

 

[GendoPose]

I think you may need to Normalize your tables, but we don't know anything about your "business".
Can you show us a jpg of your relationships window?

You might also want to show us the SQL view of your Query.

As far as I'm aware my tables are properly normalised, I had some help on that beforehand.

Here is the SQL for the query;

SELECT tblBands.BandPK, tblBandMembers.BandFK, tblBands.BandName, tblBandGenres.GenreFK, tblGenres.GenrePK, tblGenres.Genres, tblUsers.UserPK, tblBandMembers.UserFK, tblUsers.FName, tblUsers.LName, tblBandMembers.MemberDateFrom, tblBandMembers.MemberDateTo
FROM tblUsers INNER JOIN (tblGenres INNER JOIN ((tblBands INNER JOIN tblBandGenres ON tblBands.BandPK = tblBandGenres.BandFK) INNER JOIN tblBandMembers ON tblBands.BandPK = tblBandMembers.BandFK) ON tblGenres.GenrePK = tblBandGenres.GenreFK) ON tblUsers.UserPK = tblBandMembers.UserFK
WHERE (((tblBandMembers.MemberDateTo) Is Null));

Here are the table relationships;

 

[jdraw]

Ok, I agree with the Normalization.

What exactly is the purpose of the query (I guess it's recordsource for form). Why do you need all the PK FK fields?

 

[GendoPose]

Ok, I agree with the Normalization.

What exactly is the purpose of the query (I guess it's recordsource for form). Why do you need all the PK FK fields?

Yeah the query is to show Current members with the bands, genres, instruments etc, which is then used in a sub form for each User.

The PK and FK fields are so I can find the related records on the main form, but in the form it looks similar to the repeated results in the query;

 

[GendoPose]

Bump, anyone got any ideas at all?

 

[pbaldy]

Sounds like you're looking for this type of thing:

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

 

[GendoPose]

Sounds like you're looking for this type of thing:

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

I've tried using that before but I cannot for the life of me seem to get this to work.

 

[pbaldy]

Not sure I can help, not knowing how you tried to use it or what went wrong.

 

[GendoPose]

Not sure I can help, not knowing how you tried to use it or what went wrong.

Well the underlying query looks like this so far;

The form looks like this;

And the control source for the text box looks like this;

I've gone through the example on the website so many times and tried googling around for any problems other people have had and I can't get it at all, the results of the form just look like this;

 

[pbaldy]

See a difference between yours and Allen's?

=ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])

Hint, the quotes aren't optional.

 

[GendoPose]

See a difference between yours and Allen's?

=ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])

Hint, the quotes aren't optional.

Ok so I've got it half fixed now, I've managed to concatenate the results, but it's come out like this;

My control source looks like this;

 

[pr2-eugin]

Edit the RecordSource of the Form to a GroupBy Query?

 

[GendoPose]

Edit the RecordSource of the Form to a GroupBy Query?

How do I change the Query to a GroupBy? I can't find any options for it, so would I have to recreate the Query in a different way?

EDIT; never mind, I've found that but it's not changed it how I was hoping

 

[pr2-eugin]

So your query which once was.

SELECT someBandName, someGenere FROM theTable;

is now,

SELECT someBandName, someGenere FROM theTable
GROUP BY someBandName, someGenere;

and it is not working?

 

[GendoPose]

So your query which once was.

SELECT someBandName, someGenere FROM theTable;

is now,

SELECT someBandName, someGenere FROM theTable
GROUP BY someBandName, someGenere;

and it is not working?

Nope..

My SQL is currently this;

SELECT tblBands.BandPK, tblBandMembers.BandFK, tblBands.BandName, tblBandGenres.GenreFK, tblGenres.GenrePK, First(tblGenres.Genres) AS FirstOfGenres, tblBandMembers.UserFK, tblUsers.UserPK, tblUsers.FName, tblUsers.LName, tblBandMembers.MemberDateFrom, tblBandMembers.MemberDateTo
FROM tblUsers INNER JOIN (tblGenres INNER JOIN ((tblBands INNER JOIN tblBandGenres ON tblBands.BandPK = tblBandGenres.BandFK) INNER JOIN tblBandMembers ON tblBands.BandPK = tblBandMembers.BandFK) ON tblGenres.GenrePK = tblBandGenres.GenreFK) ON tblUsers.UserPK = tblBandMembers.UserFK
GROUP BY tblBands.BandPK, tblBandMembers.BandFK, tblBands.BandName, tblBandGenres.GenreFK, tblGenres.GenrePK, tblBandMembers.UserFK, tblUsers.UserPK, tblUsers.FName, tblUsers.LName, tblBandMembers.MemberDateFrom, tblBandMembers.MemberDateTo
HAVING (((tblBandMembers.MemberDateTo) Is Null));

and the results in the subform with concat text box look like this;

 

[pr2-eugin]

I can only see three fields in the Form, so if you use only those three fields as the actual Recordsource of the Form, then it should get you working?

 

[GendoPose]

I can only see three fields in the Form, so if you use only those three fields as the actual Recordsource of the Form, then it should get you working?

Even with just those 3 fields in, it has the exact same results

 

[pr2-eugin]

Upload a Stripped DB maybe?

 

[GendoPose]

Upload a Stripped DB maybe?

The form I'm looking at is "frmsubUsers - CurrentBands" and the query is "CurrentMembers"

Thanks for the help so far!