Re: One-to-Many table quey

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" <kMan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:83A9185B-A4CF-4826-9693-CC5BBF843001@xxxxxxxxxxxxxxxx
Hey

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

"Jeff Boyce" wrote:

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" <kMan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7E78AC88-24E3-4D02-95EC-AE3021302FC6@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • Re: One-to-Many table quey
    ... with a query that looks something like the following. ... Access MVP 2002-2005, 2007 ... could belong to many Customers. ... I have a table, say tblItem, with a primekey. ...
    (microsoft.public.access.queries)
  • Re: One-to-Many table quey
    ... Your Customers can only have one Item? ... mind, but in my domain, one Customer could have many Items, and one Item ... I have a table, say tblItem, with a primekey. ... tblCustomer, contains the foreign key to the first table with a 1-to-many ...
    (microsoft.public.access.queries)
  • Re: Re: Need some input on an Event Procedure for a search
    ... could try it with just needed a slight alteration to the SQL query. ... specific Features which some Customers would be looking for. ... The Customers table includes contact details, ... and the specific command for a mailout which is the event ...
    (microsoft.public.access.gettingstarted)
  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: I need a customer sales listing that includes no-sales months.
    ... > query of sales per month per customer, but I need the non-sales months ... > All my data is in one query called QryINVOICE. ... This sounds like a perfect candidate for a crosstab ... FROM (Customers INNER JOIN Orders ...
    (microsoft.public.access.queries)