Combining multiple query results into one line return

[sross81]

Hello,

I have a query that has a field called encounternbr. For each encounternbr multiple measures may be attached.

Is there a way that I can return just one row for each encounternbr and have a new field called MeasureList for example that looks at each measure that was attached and just gives a list.

For example
encounternbr, measure
1 , measure1
1 , measure2
1 , measure 3

I want it to return instead

encounternbr , measurelist
1, (measure1, measure2,measure3)

Thank you for your help
1

 

[boblarson]

I believe you need this:
http://allenbrowne.com/func-concat.html

 

[sross81]

Thank you! This is exactly what I need.

I created the function in a module and compiled like you described and then I am using it in a query.


MeasureList: ConcatRelated("ActualSpecificVariance","qry_VarianceMeasureTracking","EncounterNbr = " & [EncounterNbr])

I get an error Undefined Function 'ConcatRelated' in expression.

I assume this means it can't find my function?

 

[boblarson]

Did you name the module something OTHER than ConcatRelated? You can't have the name of the module be the same as the procedure name.

 

[sross81]

No I did not . I fixed that. Good to know!

Okay so I put this in a query instead of a text box control source is that okay?

I get an error 3061: too few parameters. Expected 1. I thought I was passing it in below.

I didn't change anything in the function that was on your site.


MeasureList: ConcatRelated("ActualSpecificVariance","qry_VarianceMeasureTracking","EncounterNbr = " & [EncounterNbr])

 

[boblarson]

No I did not . I fixed that. Good to know!

Okay so I put this in a query instead of a text box control source is that okay?

I get an error 3061: too few parameters. Expected 1. I thought I was passing it in below.

What is the SQL of the query - qry_VarianceMeasureTracking?

I didn't change anything in the function that was on your site.

It isn't my site. It is Allen Browne's.

 

[sross81]

I got it.

I read more into that article and my primary key is actual a text and number mix value so I added the brackets and that solved my problem.

MeasureList: ConcatRelated("[ActualSpecificVariance]","qry_VarianceMeasureTracking","[EncounterNbr] = """ & [EncounterNbr] & """")

 

[boblarson]

I got it.

I read more into that article and my primary key is actual a text and number mix value so I added the brackets and that solved my problem.

MeasureList: ConcatRelated("[ActualSpecificVariance]","qry_VarianceMeasureTracking","[EncounterNbr] = """ & [EncounterNbr] & """")

Okay, good to hear.

 

[sross81]

I do have one more question for you.

It gives me the measure list for each encounter but it still duplicates. If there was going to be 3 instances of the encounter before with three separate measures it shows the encounter 3 times each time listed with the three separate measures as the measure list.

I tried group by but it just runs and never ends.

Can I add something to the function to make it group?

 

[boblarson]

Try going into the SQL and add the word

DISTINCT

just after the word

SELECT

so it says

SELECT DISTINCT ...

 

[sross81]

I tried that, but it seems to get hung up. I am waiting still and its been a few minutes with no results and no errors. The green bar that says running query is almost full, but nothing happens.

 

[sross81]

I tried putting it in the function too, select distinct, but it still gives me the duplicates.

 

[boblarson]

Can you post a copy of the database (using fake data of course)?

 

[sross81]

I would love to, but it would take a while to get it all cleaned up to the point where I could post it with fake data. It is a really big database. I will try to do it soon. If any other thoughts come to your mind let me know. I will keep experimenting. Thank you!

 

[boblarson]

Normally when you have multiples like that it is due to one of the tables containing multiple records for a particular ID from the other table. So, if this one is like that, perhaps creating a separate query for just the ID and the concatendated field and then using it in this query that you are having trouble with instead of the table in question.

 

[sross81]

I guess I can just make another database object and copy in what I have. I will do that.

 

[sross81]

I did try creating another query that uses the query that I made the measure list in and I still had the duplicates and it won't let me group by or select distinct.

Normally when you have multiples like that it is due to one of the tables containing multiple records for a particular ID from the other table. So, if this one is like that, perhaps creating a separate query for just the ID and the concatendated field and then using it in this query that you are having trouble with instead of the table in question.

 

[boblarson]

Well, I'm out of ideas unless I can actually see what is happening and play with it.

 

[sross81]

I understand. I will work on getting you a copy. Thanks.

 

[sross81]

Okay here is a very basic view of the problem I am having.
qry_measurelist is what I want to be unduplicated.