Hi,

I am dealing with a problem that i need to show more than one records in a one record separated by comma.

I am using Ms Access 2007
i.e

tables

Employees:
------------
empID
empDept

Departments:
--------------
deptID
deptName

empDepts:
-----------
empID
deptID



here one employee working for more than one departments.
i just want to show all related departments separated by comma in main EMPLOYEE table field empDept.

SQL help please.

__________________
Stay Beautiful,
Abdul Salam

I would keep your current junction structure (many to many), and then add the display element to forms and reports when needed, and not concatenate the department in the main table. I would also do this:

empDepts:
-----------
empDeptsID - PK
empID - FK
deptID - FK

I can show you how to display the departments for an employee using regular expressions and an extra text box. I do this sort of thing now and then on forms and especially in reports.

mmcdonal

Look it up at: http://wrox.books24x7.com

Actually, if database bloat is not an issue, you can create a memo field that contains this data as well as your current junction structure, and then update that memo field whenever the record is changed (Before Update event.)

Normally there are posts on this site that ask how to undo the sort of solution you are looking for.


mmcdonal

Look it up at: http://wrox.books24x7.com

Aha! I am not interested buying books .. thank you.

I know to write code for this but i need a query not code behind the form or report.

Well.... is there any way to define variable in access query and can i create loop .... if else for etc.. any loop ...

thanks