One to Many, the many in one field

[Ivan Grozney]

I have a db with injury info. There is a related table with 0, 1, or more
Corrective measures.

What am trying to do in Access 2007 is write a query that will allow me to
put my basic Injury Info and then put all the Corrective actions in "one"
field so that I can write it to Excel. So

Injury table
InjuryID
WhatHappened
DateofInjury
....

CorrectPlan Table
CPlanID
InjuryID
StepNum
CorrMeas
....

Output >> InjurID,WhatHappend, Step num1 & CorrMeas Chr(13) & Chr(10))
Step Num2 & CorrMeas
Chr(13) & Chr(10))
And so on as necessary.

Any ideas?

TIA

Vanya

 

[Graham Wideman [Visio MVP]]

Hi Ivan,

What you're looking for is to join the CorrectPlan table to the Injury table
on InjuryId, and then perform a summary query that combines all the
CorrMeasure fields for a particular injury. If CorrMeasure was a numeric
variable, Access's summary functions could give you an average, sum or
what-have-you.

Sadly, Access doesn't have any string summary functions, unlike for example
MySQL which has a coallesce function to join strings in a summary in various
ways.

In Access, one somewhat kludgey way to do this is to turn the
Injury->CorrectPlan query into a crosstab query, using, say , "CM_" &
[StepNum] as column header, and CorrMeasure as the value variable. That at
least gets your CorrMeasure strings all in the same row. You can use the
crosstab result as input to a second query to glue all the strings together:

"1: " & [CM_1] & ", 2: " & [CM_2] .... etc

You probably want to detect which cols are actually occupied, so something
like:

iif([CM_1] is null, "", "1: " & [CM_1]) &
iif([CM_2] is null, "", "2: " & [CM_2])
.... etc

.... is probably a more complete solution

Graham

 

[John Spencer]

Quoting Duane Hookom

I use a generic Concatenate() function. The code is listed below with both ADO
and DAO. There are comments regarding which lines to comment or uncomment
based on which library you prefer. Access 97 is mostly DAO while the default
for 2000 and newer is ADO.

See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Download the function, paste it into a new vba module, make changes to it if
you are using it with DAO, and save the module

Then you would create a query like the following. Assumption is that InjuryID
is a number field.

SELECT Injury.*,
Concatenate("SELECT StepNum & CorrMeas FROM CorrectPlan WHERE InjuryID = " &
InjuryID & " ORDER BY StepNum", Chr(13) & Chr(10)) as CombinedList
FROM Injury


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ivan Grozney]

Graham & John,

Thank you for your responses. I'll give them a shot.

Vanya