NOOB-setting relationship validations?

[WLHagen]

OK, this is a NOOB question, I know.... but I'm a total noob to Access and I'm trying to design this from scratch!

I'm trying to create a database of contacts for a small software users group. Right now, we have about 20-30 contacts in about 10-15 institutions. Financial institutions in the area can join the group for one fee, then have an unlimited number of employees in the group.

Ideally, I would have a table of all the financial institutions in the area, with "institution-specific" information (name, core processor, whether they were a member of the user's group or not, what type of institution they are (bank, credit union, S&L, thrift)), then another table of the persons that "belong" to the user's group - attend meetings, etc.

Where I'm having trouble is - ensuring that when I enter a contact, the "institution name" is from the table of institutions. Ideally, I'd like a drop-down box or something like that for when I enter a contact and get to "company name", I click the drop-down and choose.

I tried doing a "relationship" between the 2 tables, and matching one institution to many contacts, but that still didnt' get it for me. I must be missing one simple step.

Everyone here has been so helpful - thanks in advance - yet again!

 

[Dartagnan]

Honestly I am not sure what you are asking. Are you talking about table relationships? If so then I would create a 3rd table that combines data from both. Table would have a contact and an institution column. This would allow multiple contacts for an institution.

If you are asking about a drop down in your interface then you could use a combo box with the institution table as the source.

 

[WLHagen]

Thank you for your quick reply. Yes, I do believe I'm referring to table relationships.

I'm not sure what you mean by having a 3rd table, so perhaps I'll clarify:

Institution table includes the following fields:
Institution name, Core Processor, UG Member (yes/no), and other software related check boxes.

Contacts table includes the following fields:
First Name, Last Name, Position, Institution name, email address, phone number.

What I would like to do is,when I'm entering a new contact and get to the "institution name" field - be able to ONLY choose names from the "institution name" field of the Institution table, so that the naming is consistent - so that I can easily pull everyone from "Bob's Bank", and not miss those from "BOB'S BANK" and "Bob's Bk" and "BOBS Bk".

 

[Dartagnan]

"What I would like to do is,when I'm entering a new contact and get to the "institution name" field - be able to ONLY choose names from the "institution name" "

I see. Open the Properties window for the combo box and select the Data tab. Make sure the Limit to List option is Yes. This will ensure the user can only enter items found in the list. Even if the user tries to type something else Access will throw a message indicating the entry is not found in the list.

 

[WLHagen]

Thanks, yet again!

Forgive me for yet another simplistic question, but I tried to change the combo box you describe, but that's not one of the data type options on the table?

I have the "institution name" field of the contacts list set to text. I see there is a "Lookups Wizard" option for the data type, could I use that? and if so, how?

 

[Dartagnan]

"but I tried to change the combo box you describe, but that's not one of the data type options on the table?"

Not sure I am following. Seems you are asking about 2 different things. Setting Limit to List to Yes is done while in Design View. Right click on while hovering over the combo box and select Properties. Once there select the Data tab and set Limit to List to Yes.


Having the Institutions Name as text seems correct.

Ultimately if you want to post your database I can get a better idea of how everything is related.

 

[WLHagen]

I looked at the thread for copying the DB in to the thread, but that confused me more than helped, so I'll try here.... .

My DB right now has 2 tables - the relevant fields are posted in the 3rd post in this thread. All of the relevant fields are set up as text fields.

I have one relationship set up - a "one to many" relationship between the institution name on the institution table to the institution name on the contacts table.

I have one form that I'm working on to input new contacts.

I guess the form (not the table?) is where I want to require that I choose a financial institution for the new contact that is from the institutions table?

Following this, I will also need to create a form to enter a new institution as well?

 

[Dartagnan]

Ok I think I see where you are going.

For new contacts you will want the Record Source, of the form, to be the Contacts table. The fields, on the form, will be bound to the table fields.

To ensure only the institutions listed on the Institutions table are selected/chosen you want the Control Source, of the combo box, to be the Institution table. Specifically you want the Insitution Name column from that table. As I mentioned before, make sure you set the Limit to List property to Yes.

For new Institutions, your form will have the Institutions table as the Record Source and all the fields will be bound to the table fields.