Multiple Subforms - Question

[Exiael]

Hello all,

I have a question regarding forms. I've been trying to get my company a database for their variety of different items that need tracked.

Currently I have a customer form. On the customer form I then have a subform with the detailed customer information. That works fine. What I am wanting to do is have another subform that shows the customer's boss information. I have a table that links my customer to their boss. I'm just not sure how to get that subform onto the table and also be controlled by the combo box that currently controls the rest of the form information.



Ideally, I would like to have the base customer information in the main part of the form and then a sub navigation form with multiple aspects for the subforms.

[ranman256]

the client table would have a BossID field (to link back to customers)
make a boss sub form,join the linked fields
master field: tCustomer.BossID
child field: tCustomer.CustID

[Exiael]

As long as I'm understanding what is being said, I don't know if that would work. Per another person's advice all of my "people" are in one contact table with a field that designates a category (client, boss, vendor, etc). So what I've done at this point is make a table that links the client with the boss. I also have tables linking people with properties, etc. Because of it all being in one contact table I'm not sure this would work. I could be wrong as well. I have very little experience with Access. I'm kinda learning as I go.

[ranman256]

it does work...i use it.

[Exiael]

I would say I'm doing something wrong then somewhere. When I try to follow what you said all I am getting is a form based on my join table, which shows me the client and their boss. I'm not getting the boss's details based on what I have. I think if I added another subform linking it to the previous form I could do that. Maybe. Like I said earlier, I'm pretty new to all this, and this is my first attempt at creating any kind of even simple form.

[June7]

So you have a junction table that associates client with boss. So boss info is also in the People table? Need to join People table to bossID in the junction table to retrieve boss details.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Exiael]

Yes. I'm attaching the DB. My people table (contactlist) is where I have everyone stored. Then I have a contacttype that is referenced in the contactlist. That's how I differentiate between types. Then I have the junction table to associate a specific customer with boss/location etc.

[June7]

So which table links client with boss?

[Exiael]

It's actually guardian not boss. I was using boss as a way to describe what I wanted without describing everything else lol. The join table is Cguardian. Then I have a query to pick out only clients or guardians etc from the contactlist table.

[June7]

Always helps to use real info. You don't have relationships set up and really need better understanding of data.

So a client can have multiple guardians and a guardian can associate with multiple clients?

You have ClientID as foreign key in CGuardians. Which table primary key is this supposed to link to - ContactList? Have a subform bound to CGuardians with a combobox to select guardian. Combobox RowSource: SELECT ID, LastName & ", " & FirstName FROM ContactList WHERE ContactType = "Guardian";

However, is it possible a person could be a multiple contact types - doctor as well as guardian?

[Exiael]

Yeah, I fully understand I need more knowledge here. That's most of the reason I was asking questions. Everything I've learned has been through youtube or google searches, and that doesn't always apply to what I'm wanting to do specifically. I had most of my basic relationships set up, and thought I had one set up that should cover what I was asking. Maybe not though. I will fully admit to getting confused when I got further into relationships beyond the simple. A guardian may have multiple clients, but only one guardian per client.

The primary does link back to the ContactList, yes. It goes through the query for guardians so that it only pulls up guardians rather than the entire list though. With what my company has at the moment, there are no cross overs, but realistically it is possible, just unlikely.

[June7]

I meant I needed better understanding of data and since no relationships set in Relationships Builder, relationships were not clear to me.

If a client can have only one guardian then why not just have another field in ClientInformation for Guardian?

[Exiael]

Ahhh...it didn't have relationships? That's weird. I guess I didn't have any showing when I sent it over. I was hiding them as I made them so I didn't get confused with so many tables up.

Hmmm...you know that may work. I would still need more information on the guardian but rather than have a whole separate table to join them, could just use the clientinformation table to join them.

I had the tables a bit different when i started. I got advice from some people in the Microsoft forums about normalization, and really changed about 75% of my tables around to fit that.