More ConcatRelated Problems

[GendoPose]

Hi All,

Thanks to pr2eugin I managed to get my concatrelated query working how I wanted it. However now I've tried using the same principle in a different query and it's not working in the same way.

Currently my table relationships are like this;

My query looks like this;

The concatrelated looks like this;

and the results of the query look like this;

Now, it's listing several results in the NewInfo column. It seems to be bringing in the related records from several bands and several users into one user's field, i.e Band 3 User 1 is showing the New Info for that band, other users in that band, and other bands that user is in.

Any ideas?

Link to previous thread; http://www.access-programmers.co.uk/forums/showthread.php?t=260866

 

[plog]

Your relationships are incorrect. There shouldn't be multiple paths between the same tables. I can trace 4 ways from tblBands to tblUserInstruments.

tblBandUserInstruments shouldn't be connected to tblBands, it should be connected to tblBandMembers.

From there it gets a little murky, because I don't understand how your data exactly works, but if you answer this questions I will know all I need to:

Must users be assigned to a band? That is, do you have a group of users who you want to track instrument use outside of a band? Or must every user belong to a band?

Does every instrument a user play automatically need to be associated with the band they are in? If Steve plays banjo and harmonica, can he only play banjo for his band The Awesomes? Or does he automatically play both banjo and harmonica for The Awesomes because he plays both of those anyway?

 

[GendoPose]

Your relationships are incorrect. There shouldn't be multiple paths between the same tables. I can trace 4 ways from tblBands to tblUserInstruments.

tblBandUserInstruments shouldn't be connected to tblBands, it should be connected to tblBandMembers.

From there it gets a little murky, because I don't understand how your data exactly works, but if you answer this questions I will know all I need to:

Must users be assigned to a band? That is, do you have a group of users who you want to track instrument use outside of a band? Or must every user belong to a band?

Does every instrument a user play automatically need to be associated with the band they are in? If Steve plays banjo and harmonica, can he only play banjo for his band The Awesomes? Or does he automatically play both banjo and harmonica for The Awesomes because he plays both of those anyway?

All users will have an assigned band, whether it was past or present, every user will have one.

No, Steve may be able to play both, which will show up in his record, but he only played one in that specific band which is what I'm trying to track.

How would I relate tblBandUserInstrument to tblBandMembers instead of tblBands?

 

[plog]

I believe your relationships should be like the attached image.

You would have 2 instances of the same table in your relationships (tblInstruments). This would only be 1 table, but for relationship purposes it appears twice.

 

[GendoPose]

I believe your relationships should be like the attached image.

You would have 2 instances of the same table in your relationships (tblInstruments). This would only be 1 table, but for relationship purposes it appears twice.

I can't understand how this would work, as BandMemberPK isn't related to any specific user, it's just the primary key in that table, it serves no real function.

The table BandMember just relates users to bands and the dates they were in those bands.

I also tried adding another instance of the table, but it just reset the relationships back to how they originally were and the 2nd table was ignored by access.

 

[plog]

...as BandMemberPK isn't related to any specific user...

First in tblBandUserInstrument that should be BandMemberFK, the relationship to tblBandMembers is the same. Which means it does relates to a specific user, actually it relates to a specific user/band permutation. Follow the relationship line.

For example:

Steve is in the Awesomes, in tblBandMembers. that connection gets BandMemberPK 81. Now in tblBandUserInstruments, wherever you have BandMemberFK=81 you know that Steve plays that instrument for the Awesomes.

 

[GendoPose]

First in tblBandUserInstrument that should be BandMemberFK, the relationship to tblBandMembers is the same. Which means it does relates to a specific user, actually it relates to a specific user/band permutation. Follow the relationship line.

For example:

Steve is in the Awesomes, in tblBandMembers. that connection gets BandMemberPK 81. Now in tblBandUserInstruments, wherever you have BandMemberFK=81 you know that Steve plays that instrument for the Awesomes.

Ahhhh ok, that makes more sense now, thank you, I'll give it a try now and see what happens.

 

[GendoPose]

Ok so I've changed my table relationships to how you suggested and I can see how that would work, except now I'm getting a 3601 error, too few parameters, expected 1, and I can't see for the life of me where this error is occuring.

This is my query;

with the following SQL;

SELECT tblBands.BandPK, tblBandMembers.BandFK, First(tblBands.BandName) AS FirstOfBandName, tblUsers.UserPK, tblBandMembers.UserFK, tblUsers.FName, tblUsers.LName, ConcatRelated("Instruments","tblInstruments","UserFK = " & [UserPK]) AS NewInfo
FROM tblBands INNER JOIN (tblUsers INNER JOIN (tblBandMembers INNER JOIN (tblInstruments INNER JOIN tblBUI ON tblInstruments.InstrumentsPK = tblBUI.InstrumentFK) ON tblBandMembers.BandMemberPK = tblBUI.BandMemberFK) ON tblUsers.UserPK = tblBandMembers.UserFK) ON tblBands.BandPK = tblBandMembers.BandFK
GROUP BY tblBands.BandPK, tblBandMembers.BandFK, tblUsers.UserPK, tblBandMembers.UserFK, tblUsers.FName, tblUsers.LName, ConcatRelated("Instruments","tblInstruments","UserFK = " & [UserPK]);

Here is the expression in the query;

 

[plog]

Your error means you are passing your function 3 things and it wants only 1. Did you write ConcatRelated? What's the code for it?

 

[GendoPose]

Your error means you are passing your function 3 things and it wants only 1. Did you write ConcatRelated? What's the code for it?

It's Allen Browne's concatrelated function, the idea is to concatenate the records in the Instruments field where the User is the same.

 

[plog]

I'm not familiar with that function, but it might have to do with your criteria argument:

"UserFK = " & [UserPK]

There is no UserFK field in tblInstruments. You will probably have to create a query linking tblInstruments to tblUsers (or possibly tblBandUserInstruments depending on what instruments to return).

Honestly, I think your query is improperly structured from the get-go. It seems you are using GROUP BY's to get around an error. Your SELECT clause is using data from tblBands, tblUsers and tblBandusers so why are those other tables in the query?

 

[GendoPose]

...You will probably have to create a query linking tblInstruments to tblUsers (or possibly tblBandUserInstruments depending on what instruments to return)...

I managed to fix it thanks to that, I didn't even think of creating a query and relating to that rather than the actual tables.

Whether this is a work around or fix I'm not sure, but it's doing the job so far so thank you!