Many to Many - Multiple clients and multiple contacts, and how to match them up

[jamierbooth]

Hi world,



I'm in the process of building a client management database for my team and I'd like some suggestions/assistance as to how I can build a particular part of the DB.

My design consists mainly of various different types of records that all relate back to the client, in a one to many style which is all very straightforward - addresses, financial, health, equalities etc

1 part of the DB requires staff to record contacts or support workers that are related to the client, and rather than allow support workers to be loaded many times - I'd like for people to - starting from within a form showing a clients record, go to a form where staff can pick contacts from something (list or search...) or add them if they're not already there, and connect the contact back to that client.

So far, to start the many to many relationship I have tblClients and tblContacts connected to eachother through a junction; tbljtnClientsContacts using the CustID and ContactID fields.

Any ideas or suggestions on how to proceed when building this with forms and queries?

Thanks!
Jamie.

[Dal Jeanis]

First, I'd refer you to MVP Roger Carlson's site at http://rogersaccesslibrary.com for his quick tutorials on database design. His examples of entity relationship diagrams should help you immensely in how to think about this stuff.

Second, I'd ask what the difference is between a "client" and a "contact". If you hand one of your coworkers a person's name, how would they know which kind of person it was?

I'm thinking that clients and related contacts all belong in the same table, and that any additional "status" information about the client/contact should be maintained in a related table. Information relating two client/contacts to each other would be in a junction table of some sort.

Check out Roger's tutorials, then come back with your thoughts.