Selecet multiple values in Access 2003

Hello,
i'm really new to MS Access and i need to solve this problem. I have a simple databse for client references and i need to asign multiple people to particular client. I know that it is possible with Access 2007 relativly easily but don't know how to do that in Access 2003 i supose that i will have a table with up to 300 names so the solution should be user friendly as well.

Thanks
jarmo666Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
jarmo666,

Access 2007 does make use of multi-valued columns, but I advise you to stay away from those, as in my opinion (and that of several other Experts here), all that does is encourage sloppy design habits.  (It would also prevent you from using your app in earlier versions of Access, which might be a consideration.)

The traditional way to handle this situation is to use a junction table.  For example, you would have the following tables:

tblEmployees
------------------------------------------------------
EmpID (PK)
EmpFName
EmpLName
<others>

tblClients
------------------------------------------------------
ClientID (PK)
ClientDescr
<others>

tblEmployeeClient
------------------------------------------------------
EmpClientID (PK)
EmpID (FK)                <--- also build unique index on EmpID & ClientID
ClientID (FK)

When I do this in my applications, I will frequently have a main form for tblEmployees and a main form for tblClients, and then each of those main forms will have a subform for tblEmployeeClient so that I can modify those assignments from either main form.

Patrick
0
 
shambaladCommented:
One alternative is to use listboxes to display and make the available selections.
Check the following tutorial for how to set it up:
http://www.datapigtechnologies.com/flashfiles/listboxtrick.html
0
 
Patrick MatthewsCommented:
And BTW, when you do use multi-valued columns, Access is actually building a behind-the-scenes structure similar to what I described :)
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
jarmo666Author Commented:
It works fine, but i will need to know run some reports by asigned people, any sugestion how to do that?
0
 
jarmo666Author Commented:
Patrick: can you be more specific, i just build the tables and i need to help on setuping relationships between them. Do you thing that you can send me example as file?

Thanks
0
 
Patrick MatthewsCommented:
Setting up relationships is easy, and is covered in the Access help file.

If you need to report on employee-client assignments, just base your report on this query:

SELECT e.EmpLName, e.EmpFName, c.ClientDescr
FROM (tblEmployees e INNER JOIN
    tblEmployeeClient ec ON e.EmpID = ec.EmpID) INNER JOIN
    tblClients c ON ec.ClientID = c.ClientID
0
 
jarmo666Author Commented:
I thought what should be connect to what for ex. ID from tbl client to... :-)

Thanks
0
 
jarmo666Author Commented:
Patric: can you give me more guidence regarding to junkction table and how do i implement this into form? I also need to create user friendly form where users can select the multiple names acording to Client name.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.