By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,380 Members | 3,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,380 IT Pros & Developers. It's quick & easy.

Query a one to many table

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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


Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.