Concatenating values in related tables

[Eric the Viking]

Dear All
I have three tables:
Event related on to many with Procedure
Procedure relates on a one to one basis with the description of the procedure in a table called ProcedureCodes.

I wish to have a query which outputs three columns:

Event - Prcedure1, Procedure2, etc - Description1, Description2, etc.

I have tried the Allen Browne module (http://allenbrowne/func-concat.html

This gives me:

Event - Procedure1, Procedure2, etc using the expresion:

Expr1: ConcatRelated("[Procedure Code]","[tbl-procedures]","[tbl-procedures].[Event number]=" & [Event no]) to concatenate the procedures.

But I am struggling to get the final column!

I have tried the following:

Expr2: ConcatRelated("[short description]","[tbl-procedure code]","[tbl-procedure code].[ProcedureCode]=""" & [Procedure Code] & """" And "[tbl-procedures].[Event number]=" & [Event no])

But this does not return what I want.

Any ideas/help/suggestions would be much appreciated.

Regards

Eric

 

[CJ_London]

But this does not return what I want.

Not very helpful - better if you can explain what you are getting instead and what is wrong with it.

I think you are experiencing the classic reason why you should not have spaces in your table and field names - try this instead

ConcatRelated("[short description]","[tbl-procedure code]","[tbl-procedure code].[ProcedureCode]='" & [Procedure Code] & "' And [tbl-procedure code].[Event number]=" & [Event no])

 

[jdraw]

How exactly should the output look? example please

 

[vbaInet]

I think this is what he's after:

I wish to have a query which outputs three columns:

Event - Prcedure1, Procedure2, etc - Description1, Description2, etc.

Two fields, Procedure and Description to be concatenated into one.

I don't know how Allen Browne's Concat related code was written so I can't say for sure if it will do what you want on one call.
In any case, you need to run the code twice, one for Procedure and the other for Description then join both.

 

[jdraw]

vbaInet,

I hope you're correct. I was thinking that this
Event - Prcedure1, Procedure2, etc - Description1, Description2, etc.

might represent

Event - Prcedure1, Procedure2,Procedure3, Procedure4,..Procedure99 ..etc - Description1, Description2, Description3, Description4... Description99.. etc.

 

[vbaInet]

jdraw, yeah that's my thinking.

 

[Eric the Viking]

jdraw your thinking was correct three tables joined in query:

Event - one to many with - Procedure - one to one with - Description

Event is an event for a patient who can have several procedures in that event each of which maps to a description in the third table.

I need to create an output that has one event, concatenated procedures in the next column and concatenated descriptions in the final column.

Sorry if I failed to make sense in initial post.

Using the Allen Browne module I can get the concatenated procedures in clump 2 of the query output but I am struggling with the final column.

Eric

 

[Eric the Viking]

sorry not clump but column

 

[jdraw]

Just curious, if Procedure--Description is 1 to 1, why not have description in the Procedure table?

tblProcedure
ProcedureID PK
ProcedureName
ProcedureDescription

I don't have Access on this machine so cannot test. Did you try the code that CJ suggested?

 

[Eric the Viking]

Correction sorry was having a stressful day and misinformed re procedure to description relationship which is in fact "many to one"
Still not sure how to solve the problem
Eric

 

[jdraw]

Please post a sample input record(s) and your desired output for same record.

 

[Eric the Viking]

Hi have attached dummy database so you can see how the query works and the filed I wish to concatenate i.e. Description
Cheers
Eric

 

[Eric the Viking]

Dear All
I would appreciate any advice on the posted database example that will help me resolve my issue.
Many thanks
Eric

 

[vbaInet]

Eric, the answer has already been provided --->

In any case, you need to run the code twice, one for Procedure and the other for Description then join both.

... call the function twice.

Otherwise amend the function to :
1. take two fields in one parameter
2. split the fields in the function
3. concatenate both fields in separate variables
4. at the end of the loop, concatenate both variables and return this value

 

[Eric the Viking]

Well once again thanks to you all my failure to engage brain has been remedied by your help ....!You are all stars!
Eric

 

[vbaInet]

Did you manage to amend the function to your needs?