Joining multiple tables in one "ProfileTable" - Struggling to grasp many-to-many relationships.

[Dirty_Head]

I'm working with 4 tables right now.

ProfileTable - This is the joining table for the other tables. This table has fields: [PK]ProfileID, EmployeeID, EmergencyID, JobTypeID.

ExternalEmployees - Employee Data: [PK]EmployeeID, FirstName, LastName, PhoneNumber1, PhoneNumber2, Address, City, State, ZipCode, SSN, Birthdate, [FK]ProfileID

EmergencyContacts - Employee's Emergency Contacts: [PK]EmergencyID, FirstNameE, LastNameE, AddressE, PhoneNumber1E, PhoneNumber2E, [FK]ProfileID

JobTypeTable - Simple list of job types we hire for: [PK]JobTypeID, JobType, [FK]ProfileID

Relationships: [ProfileTable] ProfileID -> Profile ID[ExternalEmployees]. [ProfileTable]ProfileID -> ProfileID[EmergencyContacts]. [JobTypeTable]JobTypeID -> JobTypeID[ProfileTable]
All Relationships are one-to-many.




So on my form I'm trying to place "Existing Fields" to add new entries. I need to place a ComboBox dropdown allowing multiple checkboxes for JobType. Our employees could be a general labor, hydroblaster, CDL driver, AND a painter. How do I go about storing multiple JobTypeIDs? Or am I going about this wrong? How should I do this?

[ranman256]

You'd have a table of all types,
then in the subForm, a combo would let you assign this type to the sub record.
add as many records,(and types) as you need for the 1 person.

[orange]

You have described HOW you have done something. Could you tell us in 4 or 5 lines WHAT the underlying business is that you are trying to support with the proposed database. Use simple, plain English --no jargon. Please tell us where the many to many relationship(s) exists. Once readers understand what you are dealing with, I'm sure you'll get some advice and options for how it might be achieved in MS Access.
Good luck with your project.

[Dirty_Head]

Database will record list of employees, their job types, emergency contact info. Will allow issuing expendable items, allow checkout of reusable items, and allow basic filter/search for employees themselves.

I've got my checkouts section working, issue items working, now I'm trying to integrate those two into this "third" system of also saving specific employee information.

[June7]

A dropdown with checkboxes requires a multi-value field setup in table. I NEVER use multi-value fields. Suggest you study and fully understand MVF before opting to use. https://support.office.com/en-us/art...C-6DE9BEBBEC31

Ideally, as Ranman already described, have a related dependent table for associating employees with multiple jobs. Each record would be employee/job pairing.