• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

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.
0
Paul McCabe
Asked:
Paul McCabe
  • 2
1 Solution
 
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
0
 
Paul McCabeAuthor Commented:
Hi Christian,

Everything you says makes sense and has really helped put me on the right path. Thank you very much !
0
 
BitsqueezerCommented:
You're welcome...:-)
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now