Main Contact, various Profiles -- how do I connect?

[JacksMamma]

Hi Everyone! :hi: I'm new here, and a beginner (to say the least!).

Help...please???? It's a 2-part question and I'm totally stumped. I'd look for a solution on my own but I don't know the best way and I see so many examples that only answer one part of things -- so I don't know if I need to do a query, function, procedure, or module.

OK - here goes!

The database is in MSSQL and I have dbo_linked tables to Access 2016.

I have a contact table with basic info like first_name, last_name, etc. and it has 1-1 relationship with what I'll call "c_tables" -- which are profiles for each contact. A contact can have many profiles. Profiles include c_employee, c_songwriter, c_performer, c_user. (e.g., one contact can be a writer and an employee, and I have to capture a lot of data for each profile.) Note: a contact can have more than one c_performer profile but only one of the other profiles.

I have a c_profile table that is a join table. It should capture the contact_id from the contact form.contact_id and a profile id as you'll see below as each profile is created.

All that said - I'm creating forms and I've made my basic form for contacts bound to the contact table.

Question 1: How do I best pass the id to another form? I ask because there are so many fields for each profile that it's impossible to just make the profiles be subforms on the main contact table. I've added command buttons on the contact form to open frm_employee, frm_writer, frm_performer, frm_user. Each form is bound to the correct table for the profile. I'm currently passing the id by typing in the Default property on the profile text box for contact_fk. Is this the best way??? To the bigger question, I'd like to add a "Save" button to each profile form and when clicked I'd like it to refresh the form, update the appropriate c_table, and add a record to the c_profile table capturing (eg) c_writerprofile_id.

Question 2: Once done with filling out any given profile form, I'd like to return to the contact form and see (somewhere on there) all of the profiles that that contact has. Is this even do-able? Does an object like this have a name??

Thank you in advance should any one of you take pity!!!

[HiTechCoach]

I take that this "1-1 relationship with what I'll call "c_tables" means you have each profilr4 in a separate table. If that is true that is the first issue and will make this very difficult. That would explain why yu are having so much trouble finding a solution.

First, if all the profiles were in a singe table that would give you a one-to-many relationship. Then this would become significantly easier.

If you will use a single profile table then I can help you. Otherwise, I don't have a clue if it is even possible or practical with Acess

The key to success is a good foundation. With database applications that foundation the tables. The key to success for you will be a well normalized table design.

[JacksMamma]

Hi HiTechCoach!

Thanks for your reply. I have a 1-1 from contact to c_employee. I have a 1-1 from contact to c_writer. A 1-1 from contact to c_performer. Etc. The schema designer did this because the basic contact info in the contact table is approx. 50 fields while each profile has 30+ fields. It's too many fields for one table (contact) so he broke them up into smaller tables with a 1-1.

My second problem is how to show which (if any) profiles have been created for any given contact on the main contact form -- and I'd like to show it in datasheet view or an option group of checkboxes set to true or a list box ... (maybe on closing the form after a profile record has been created in employee or performer or writer).

If this is still just all wrong, please do let me know and I'll contact the database designer! Thanks!!!

1-1 tables may be rare, but this seemed the right solution for such massive tables. I have everything working in terms of passing the id's to the profile forms when they open (passing/inserting the contact id to the employee profile/record, for example), but I don't know if this is the best way to do this. I was wondering if there is some sort of procedure that could be written that could declare each of the 1-1 relationships so I'm not doing that at the form level.

[HiTechCoach]

Hi HiTechCoach!

Thanks for your reply. I have a 1-1 from contact to c_employee. I have a 1-1 from contact to c_writer. A 1-1 from contact to c_performer. Etc. The schema designer did this because the basic contact info in the contact table is approx. 50 fields while each profile has 30+ fields. It's too many fields for one table (contact) so he broke them up into smaller tables with a 1-1.

My second problem is how to show which (if any) profiles have been created for any given contact on the main contact form -- and I'd like to show it in datasheet view or an option group of checkboxes set to true or a list box ... (maybe on closing the form after a profile record has been created in employee or performer or writer).

If this is still just all wrong, please do let me know and I'll contact the database designer! Thanks!!!

1-1 tables may be rare, but this seemed the right solution for such massive tables. I have everything working in terms of passing the id's to the profile forms when they open (passing/inserting the contact id to the employee profile/record, for example), but I don't know if this is the best way to do this. I was wondering if there is some sort of procedure that could be written that could declare each of the 1-1 relationships so I'm not doing that at the form level.

I recently was looking at a old database I have create with Access 2000 that was used to store a libray of recordings. This handled your library of vinyl, tapes, CDs, DVDs. This could be music, TV, movies, etc.

That reminded me about your post. After looking at my database, it does everything you want plus a lot more.

After reading your posts several times I think you see objects within the data as a single object of contact with profiles as attributes. That is a spreadsheet with all the data on a single row. . That would explain why you think a 1-1 relationships makes sense.

What you have I would not consider a well design relational databases but how someone would set it up in Excel.

I look at the data the opposite way you have. I see Employee, songwriter, performer, etc. all as separate objects. What they all need is contact data. This can be shared with a contacts table. This will get you what you want.