424,054 Members | 1,044 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,054 IT Pros & Developers. It's quick & easy.

Displaying multiple field values in 1 query record

P: n/a
I have a table that looks like below:

Name, Address, Favorite Pet
Bill Smith, 123 Smith St, Dogs
Bill Smith, 123 Smith St, Cats

The "Dogs" and "Cats" are actually in a lookup table, but have a "PetID
assigned to them". I want my query result to be:

Bill Smith, 123 Smith St, Dogs and Cats

How do I get my grouping or result such that it will combine the last
column like above?

Thanks for any help you can provide!
Darrell

Feb 27 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Presumably have 3 tables, something like this:
- Customer (names and addresses)
- Pets (lookup table of pet types)
- CustomerPets (junction table, with CustomerID and PetID)

You will need to base the form on the Customer table only, and use a
function call to concatenate the values from the Pets table to generate the
3rd field in your example.

See:
Return a concatenated list of sub-record values
at:
http://www.mvps.org/access/modules/mdl0004.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ve***@aol.com> wrote in message
news:11*********************@t39g2000cwt.googlegro ups.com...
I have a table that looks like below:

Name, Address, Favorite Pet
Bill Smith, 123 Smith St, Dogs
Bill Smith, 123 Smith St, Cats

The "Dogs" and "Cats" are actually in a lookup table, but have a "PetID
assigned to them". I want my query result to be:

Bill Smith, 123 Smith St, Dogs and Cats

How do I get my grouping or result such that it will combine the last
column like above?

Thanks for any help you can provide!
Darrell

Feb 28 '06 #2

P: n/a
Guess I'm still a novice at the function part of this but two
questions:

1. I eventually want to output to a new table so can I use a query
instead of a form? I took the answer literally.
2. I thought I followed instructions and put
=fConcatChild([tb_providers],[provider_id_co],[hosp_1],[Text],[«varIDvalue»])
in my form. I don't really understand the last field which is "10255"
in the example. I don't know what that's referencing.

I'm trying to take the hosp_1 field (in many table) and concatenate it
with the provider_id_co so I'd have the Bill Smith 123 Smith St. Dogs
and Cats answer above. I'm assuming Bill Smith is the provider_id_co
and Dogs is hosp_1 and Cats is hosp_1. I'm getting a #Name? error.

Mar 15 '06 #3

P: n/a
Re Q1:
You can use the function in the query (as per the example in the web page),
or in the Control Source of a text box on
your form or report.

Re Q2:
The function arguments are incorrect. Do you have a structure similar to my
previous reply? If so, the first argument would be your CustomerPets table
(not tb_Providers, which would have a unique record for Bill Smith if I
understand you.)

The arguments are explained in the article.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ve***@aol.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...
Guess I'm still a novice at the function part of this but two
questions:

1. I eventually want to output to a new table so can I use a query
instead of a form? I took the answer literally.
2. I thought I followed instructions and put
=fConcatChild([tb_providers],[provider_id_co],[hosp_1],[Text],[«varIDvalue»])
in my form. I don't really understand the last field which is "10255"
in the example. I don't know what that's referencing.

I'm trying to take the hosp_1 field (in many table) and concatenate it
with the provider_id_co so I'd have the Bill Smith 123 Smith St. Dogs
and Cats answer above. I'm assuming Bill Smith is the provider_id_co
and Dogs is hosp_1 and Cats is hosp_1. I'm getting a #Name? error.
Mar 16 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.