One-to-Many table quey

[kMan]

Hello guys,

I have a table, say tblItem, with a primekey. The second table, say
tblCustomer, contains the foreign key to the first table with a 1-to-many
relationship. That is for an item in tblItem, there may be one or more
entries in tblCustomer.

What i would like to do is to make a query such that it returns distinct
items in tblItem. However, one column should list the lastnames of the
customers separated by comma. eg:
item1_PK | item1_Name | item1_Price | blog, jo, john
...... and so on

is it possible to do this? if so what would the expersion for the last field
need to be?
Reason for wanting this is to produce a report in this format... I'm using
Access 2003.

Thanks for your help

 

[Jeff Boyce]

Your Customers can only have one Item? Perhaps I have a different domain in
mind, but in my domain, one Customer could have many Items, and one Item
could belong to many Customers. This is a many-to-many relationship, not
one-to-many.

Or have I misunderstood your example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[kMan]

Hey

In my application it's strictly one-to-many relationship. Many-to-many would
be more complicated, i'd imagine.

 

[John Spencer]

I suggest you might use Duane Hookom's Concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

After you download the code and install it in a module, you would end up
with a query that looks something like the following.

Assumption: The primary and foreign key are numeric fields. If they are
text fields, you would need to modify query string to include text
delimiters.

Select I.PK
, I.Name
, I.Price
, Concatenate("SELECT C.Customer FROM tblCustomer as C WHERE C.ItemFK =" &
I.PK) as custList
FROM tblItem as I

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[kMan]

I haven't yet implemented this, but seems like what I need... Is it
possible/illegal to kiss MVPs? (sidenote; what's with the MVP titles)

I can ditch the temperorary table method i'm trying to conjure

 

[John Spencer]

Try the following URL for an explanation.

http://en.wikipedia.org/wiki/Microsoft_Most_Valuable_Professional

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..