Possible to associate a single field on a form with multiple fields on another form ?

Hello, I am a relative beginner to MS Access, having worked on my first database for the past couple of months. I am at a stage where I can manipulate VBA code, but not really write it.

1.      Database set-up
ClientTable –client information table
ClientForm -for data input into ClientTable
ProjectTable -project information table
ProjectForm -for data input into ProjectTable
ProjectFormContin -a continuous form listing project information from a ProjectTable-based query

Each project can have up to 4 clients associated with it. Client information is input into ProjectTable (fields: Client1, Client2, Client3 and Client4) via a combo box on ProjectForm which is pulling the information from ContactTable. ProjectFormContin includes 4 fields for displaying the contacts.

2.      What I would like to do
Have a button on ClientForm which would open up ProjectFormContin, displaying only these projects with whom the current ClientForm client is associated. If there was only one client associated with each project, it would be a simple matter to create a button linking the current ClientForm client to the client field on ProjectFormContin, however since there are 4 client fields on ProjectFormContin, I am not sure how this should be done. One possible way to do it would be to create 4 separate buttons on ClientForm, each associating the current ClientForm client with one of the 4 client fields on ProjectFormContin. However, this seems a poor solution in terms of usability. If anyone has any ideas on how this could be done more elegantly, they would be greatly appreciated. Thank you very much in advance for any help.
Paul McCabeAsked:
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.

BitsqueezerCommented:
Hi,

the problem is that your data model is wrong. Each time where you begin to create more than one field of the same type in one table you can be relatively sure that your data model is not OK. So creating fields C1-C4 means: You are trying to create columns where you need to have rows.

What you need is a so-calles "m:n" table which connects projects and clients. The table consists usually of an auto id as primary key and two further fields for projectID and clientID. This makes it possible to link any project with any client (and additionally lets you add as many clients to any project as you want, not only 4).
Then you can delete the four client fields from the project table.

In a normalized model one table should only hold one class of information so the project table should describe only attributes belonging to the project, the client table should describe only attributes belonging to the client, the m:n table brings both classes together and itself describes no attribute of each of the other tables, it describes, which record of one table belongs to which of the other. It can also have additional attributes, i.e. to describe everything regarding to this reference, that could be the date when the client was added to the project or an information that the client was blocked in this project only or anything else strictly only belonging to the description of this reference between both. For example, if you want to describe that the client itself should be blocked in all projects, this would be an attribute for the client table.

In the end you have only one clientID field (in the m:n table) which you must use to search for so your button can simply query this m:n table to find out any projectIDs of this clientID which then can be used to filter the details in the project table (form).

This can also be done without a button, simply by adding a query on the m:n table as subform to the client form, Access links both tables and if you change the record in the client main form you automatically have a list of all projects in the subform - without any programming. You can then i.e. add a hyperlink to the listed project field of the subform to open the corresponding record in the project field or you can add a button instead if you want.

Cheers,

Christian

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
Paul McCabeAuthor Commented:
Hi Christian,

Everything you says makes sense and has really helped put me on the right path. Thank you very much !
BitsqueezerCommented:
You're welcome...:-)
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.