Solved WHERE on Allen Browne's ConcatRelated module issue.

[klsblues]

Hi everyone,

I have hit a brick wall I am going round in circles trying to figure out the correct WHERE part of the Concatenation module from Allen Browne.

I need to Concatenate the Qualifications for each candidate, but the query is returning all Qualifications against each candidate. So for instance, Meryl has 3 qualifications Jennifer has 2, Meg has 5 and so on, but the concatenated field is adding all 10 to each candidate.

I have attached a database with the tables and the query is qryConcatQuals. I haven't put the WHERE bit in, but if someone can help, that would be great.

Kind regards - Kevin.

 

[theDBguy]

Hi Kevin. On my phone right now so I can't help much at the moment but just wondering if you have seen or tried this simple function?

Code Snippets

 

[MajP]

I also use the @theDBguy function or roll me own for that specific case. The Allen Browne code ends up being too much work in order to try to be flexible.

 

[MajP]

FYI. I was going to provide the solution but it is not worth it until you get rid of the table lookups. It will make doing this really confusing

The Access Web - The Evils of Lookup Fields in Tables

If you want a demo get rid of the table lookups and post back. Lookups go in forms and not tables. Why MS thinks this is a good feature, know one knows.

 

[Gasman]

That is because you are using the incorrect table for the concat?
You should be looking at tblCandQuals for the records. having table lookups does not help the matter?

 

[klsblues]

FYI. I was going to provide the solution but it is not worth it until you get rid of the table lookups. It will make doing this really confusing

The Access Web - The Evils of Lookup Fields in Tables

If you want a demo get rid of the table lookups and post back. Lookups go in forms and not tables. Why MS thinks this is a good feature, know one knows.

Hi MajP

I wasn't aware of the issue with lookups - it's all I've ever known. They are integrated into the main database, so I'll have to have a look at it, but i will take your point on board (after reading your link!)

Unless anyone else can help, I'll need to re-think.

Thanks and regards.

 

[MajP]

I fixed it.
qryConcatQuals qryConcatQuals

CandName	Quals
Hugh Johnman	KS1,KS2
James Pond	KS1,KS2,Mathematics L1,Mathematics L2
Jennifer Bannister	KS1,KS2,Mathematics L1
Meg Cryin	KS1,KS2,Mathematics L1,Mathematics L2,SEND,Sports Degree 324
Meryl Sweep	KS1,KS2,SEND
Ryan Goosling	English L1,KS1,KS2,SEND
Tom Floose	KS1,KS2,Mathematics L1,SEND,Sports Degree 324

 

[Gasman]

Hi MajP

I wasn't aware of the issue with lookups - it's all I've ever known. They are integrated into the main database, so I'll have to have a look at it, but i will take your point on board (after reading your link!)

Unless anyone else can help, I'll need to re-think.

Thanks and regards.

Did you see my post?
Meg seems to have more than two as well?

 

[klsblues]

I fixed it.
qryConcatQuals qryConcatQuals

CandName	Quals
Hugh Johnman	KS1,KS2
James Pond	KS1,KS2,Mathematics L1,Mathematics L2
Jennifer Bannister	KS1,KS2,Mathematics L1
Meg Cryin	KS1,KS2,Mathematics L1,Mathematics L2,SEND,Sports Degree 324
Meryl Sweep	KS1,KS2,SEND
Ryan Goosling	English L1,KS1,KS2,SEND
Tom Floose	KS1,KS2,Mathematics L1,SEND,Sports Degree 324

Hi MajP

Thank you very much indeed - this and the Grid issue you helped me with is brilliant. As I said, I'll take on board the lookup in tables issue and now it's been pointed out, it does make sense!

Thanks and regards - Kevin.

 

[klsblues]

Hi Kevin. On my phone right now so I can't help much at the moment but just wondering if you have seen or tried this simple function?

Code Snippets

Hi DBGuy,

As you can see MajP has solved this for me, but a thank you to you as well - he used your function!

Thanks and regards - Kevin.