SQL command help

[John]

Anybody

I have two tables like so:

Employee:

Employee_ID Name
------------------------------------------
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

Orders:

Prod_ID Product Employee_ID
-----------------------------------------------------
234 Printer 01
657 Table 03
865 Chair 03

I want to create an SQL statement that will return the following result

Name ProductList
-----------------------------------------------------
Hansen, Ola Printer
Svendson, Tove
Svendson, Stephen Table, Chair
Pettersen, Kari


Basically, I want to return a new column with a list of all the orders.
How
would I achieve this?

John

[Smartin]

Re: SQL command help

John wrote:[color=blue]
> Anybody
>
> I have two tables like so:
>
> Employee:
>
> Employee_ID Name
> ------------------------------------------
> 01 Hansen, Ola
> 02 Svendson, Tove
> 03 Svendson, Stephen
> 04 Pettersen, Kari
>
> Orders:
>
> Prod_ID Product Employee_ID
> -----------------------------------------------------
> 234 Printer 01
> 657 Table 03
> 865 Chair 03
>
> I want to create an SQL statement that will return the following result
>
> Name ProductList
> -----------------------------------------------------
> Hansen, Ola Printer
> Svendson, Tove
> Svendson, Stephen Table, Chair
> Pettersen, Kari
>
>
> Basically, I want to return a new column with a list of all the orders.
> How
> would I achieve this?
>
> John
>
>
>[/color]

Googling reveals numerous pages that explain verbatim how to do 95% of
what you ask. I suspect the remainder (one record per employee with a
comma separated list of products) will need to be done with VBA. To
start I would consider walking through any recordsets where
COUNT(EmpName) > 1 and concatenate the Product values.

--
Smartin

[RoyVidar]

Re: SQL command help

John wrote in message <120uuh07brbea8 9@corp.supernew s.com> :[color=blue]
> Anybody
>
> I have two tables like so:
>
> Employee:
>
> Employee_ID Name
> ------------------------------------------
> 01 Hansen, Ola
> 02 Svendson, Tove
> 03 Svendson, Stephen
> 04 Pettersen, Kari
>
> Orders:
>
> Prod_ID Product Employee_ID
> -----------------------------------------------------
> 234 Printer 01
> 657 Table 03
> 865 Chair 03
>
> I want to create an SQL statement that will return the following result
>
> Name ProductList
> -----------------------------------------------------
> Hansen, Ola Printer
> Svendson, Tove
> Svendson, Stephen Table, Chair
> Pettersen, Kari
>
>
> Basically, I want to return a new column with a list of all the orders. How
> would I achieve this?
>
> John[/color]

I think you'll need more than just SQL, I think you'll need to call a
VBA function, too.

Check out Duane Hookoms "Generic Function To Concatenate Child Records"

http://www.rogersaccesslibrary.com/OtherLibraries.asp

--
Roy-Vidar