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]

John wrote:

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

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]

John wrote in message <12*************@corp.supernews.com> :

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

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