[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

multiple entries in a form and a table

Hi,

I have a table with some fields: Training name, training time, and people.

My problem is that in the people field can exist more than one person and I want to have the ability to filter by people and enter the information of the people in a form. Any idea?
0
joyacv2
Asked:
joyacv2
  • 3
  • 2
3 Solutions
 
Ken ButtersCommented:
If it would be a better design to have a 'normalized' table.  By normalized I mean that a given field does not contain multiple things... like multiple names.

In this case I would design it as :

1) Training Table --- Which holds things that pertain to the Training.  Like the location, time, subject etc.

2) People Table -- Which hold things that pertain to people, like first Name / last Name etc.

3) An 'Associate' table.  This table would be used to define your relationship between the people and the Training.  one row in this table would typically consist of an ID from the Training table, and an ID from the people table.  This would then be used to define which people are taking what training.

A common name for this type of relationship is a "Many-to-Many" relationship.

Once you have your tables and the relationships built, then you would create a query that gives you the results that you are looking for, and your form would be based on the new query, not on any of the tables.
0
 
joyacv2Author Commented:
HI,

But what can I do with the form, where I enter in the form the list of participants. In the form I need to specify the training and the participants?
0
 
Ken ButtersCommented:
Sounds like you form you want is "Training" based... meaning that your focus is mostly on setting up a training class?

One way to solution that would be to create form that is centered on the Training (it can be sourced on the training table), and a subform that lists the people that can be added to the training class.

here is an example of this:

http://ms-access-tips.blogspot.com/2012/02/creating-form-for-many-to-many.html
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
joyacv2Author Commented:
sorry, but I don't understand because the only thing that I need is to create a field that accepts many person names in the form? How I can use a subform of this without making relationships between the tables?
0
 
Ken ButtersCommented:
You cannot create a sub-form like I descirbed without making relationships between the tables.

Is there some reason you cannot, or do not want to create the necessary tables?
0
 
Jeffrey CoachmanCommented:
joyacv2, (No Points wanted)

buttersk is correct, your data is not formatted properly to do what you are asking.

If one "training" can have a many "people" (and conversely, one Person can have many "trainings") Then your design need to be a bit more robust and flexible.

This is why you should always ask if your design is correct before you design your forms.

If you are new to database design (keys, relationships, ...etc), it will take a while to get this design up and running.
What buttersk  posted is the standard why of setting this up, so please continue working with them to get this set up....
But once you do, ...things like what you are asking here will be "easy" instead of difficult.

The good news is that there is a fairly good feature called a "Multivalued Field" (in Access 2007 and higher) to make this a lot easier to do...
See here for more info:
http://office.microsoft.com/en-us/access-help/guide-to-multivalued-fields-HA001233722.aspx

;-)

JeffCoachman
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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