By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

SQL command help

P: n/a
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

Mar 9 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Mar 9 '06 #2

P: n/a
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
Mar 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.