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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.