Query a one to many table

[jomonto]

Hello -
I need to run a report or query that returns a family record with
associated reason codes - as one record.
For instance: FamilyID -1800 has three reasons for needing service (SP,
WK, ED) - I want the result of the query or report to show>

1800,SP,WK,ED

all I can come up with, results like this: (three records)
1800,SP
1800,WK
1800,ED

Now some families may only have one reason - up to six reason (not
likely but...)

The basic tables look like this
[tFamily].[FamilyID]

[tFamilyReason].[FamilyID].[ReasonCode]
(of course the two tables are linked via familyID - in a one to many
relationship)
Appreciate the help-
thanks,
-john

[Big Time]

I had a similar situation a little while ago and was able to use the
following two modules as reference to solve the problem.

http://www.mvps.org/access/modules/mdl0008.htm

and you can also look at

http://www.mvps.org/access/modules/mdl0004.htm

One thing I noticed was the query was fairly processor intensive however I
was concatenating in some cases up to 18 records into one row which may have
been why.

Hope this helps.

Mike

<jo*****@zeus.kern.org> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Hello -
I need to run a report or query that returns a family record with
associated reason codes - as one record.
For instance: FamilyID -1800 has three reasons for needing service (SP,
WK, ED) - I want the result of the query or report to show>

1800,SP,WK,ED

all I can come up with, results like this: (three records)
1800,SP
1800,WK
1800,ED

Now some families may only have one reason - up to six reason (not
likely but...)

The basic tables look like this
[tFamily].[FamilyID]

[tFamilyReason].[FamilyID].[ReasonCode]
(of course the two tables are linked via familyID - in a one to many
relationship)
Appreciate the help-
thanks,
-john

[jomonto]

Excellent !

I tried mdl0004.htm - it worked perfect.
Exactly what I was looking to do. Pretty simple solution huh?? I would
have eventually wrote that same function.... NOT ! <smile> - wow

Thanks for your help,

john-


Big Time wrote:

I had a similar situation a little while ago and was able to use the
following two modules as reference to solve the problem.

http://www.mvps.org/access/modules/mdl0008.htm

and you can also look at

http://www.mvps.org/access/modules/mdl0004.htm

One thing I noticed was the query was fairly processor intensive however I was concatenating in some cases up to 18 records into one row which may have been why.

Hope this helps.

Mike

<jo*****@zeus.kern.org> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Hello -
I need to run a report or query that returns a family record with
associated reason codes - as one record.
For instance: FamilyID -1800 has three reasons for needing service (SP, WK, ED) - I want the result of the query or report to show>

1800,SP,WK,ED

all I can come up with, results like this: (three records)
1800,SP
1800,WK
1800,ED

Now some families may only have one reason - up to six reason (not
likely but...)

The basic tables look like this
[tFamily].[FamilyID]

[tFamilyReason].[FamilyID].[ReasonCode]
(of course the two tables are linked via familyID - in a one to many relationship)
Appreciate the help-
thanks,
-john