linking one table field to a field in a different table

[WLHagen]

Hello all! I'm a complete NOOB when it comes to Access, so I'm hoping that y'all can get me "over this hump" and I can go on my merry way creating this DB.

The DB I'm trying to create is for a software users group for financial institutions (banks and credit unions). So far, I have in mind 2 tables

Table 1
Name of Financial Institution (FI)
Checkbox for "Bank" or "Credit Union"
Main Address of FI
.... other info

Table 2
Members name (2 fields for first name, last name)
email address
Name of Financial Institution(FI) that is their employer
...... other info

There is a "one to many" link between Table 1 (the "one") and Table 2 (the "many") What I would like to do is when I'm adding new people to Table 2, I want to have to choose the "Name of Financial Institution(FI) from the names in table 1, so the DB is consistent, and "Bank of America" is chosen for everyone that works there, and someone doesn't put in "B of A" instead. Ideally, this would be a drop-down, but for now, I'd be happy if the field on Table 2 only allowed EXACTLY what was in the similarly titled field on Table 1.

I looked in Data validation, and I really couldn't make this part "work".

Thanks in advance to any and all assistance!!

 

[alansidman]

Make your input form for table 2 a sub form of your input form for table 1. Here is a video to help you understand.

http://www.datapigtechnologies.com/flashfiles/subforms1.html

http://www.datapigtechnologies.com/flashfiles/subform2.html

Alan

 

[WLHagen]

Thanks, Alan - good info, but I think that's 15 steps ahead of where I am. For right now, I'm just trying to design the information tables. How do I link the FI Name on Table 2 to only allow valid FI Names from Table 1?

 

[Norie]

Add an Autonumber field to the institutions table (Table1).

Make it the primary key for the table.

You should just be able to do that by right clicking the field and selecting the little golden key.

(You might find another golden key on the ribbon, you can use that as well)

Almost forgot give the field a name, eg InstitutionID.

Now add a field with that name to the members table.

This is a foreign key that links a member record to the institution table.

That should give you a start.

 

[WLHagen]

Thanks, Norie!!! That got me started in the right direction. I've actually decided to use this for another field in the DB.

Y'all are the BEST!!!!!

 

[Norie]

What field?

What I outlined is really how you should set the relationship between these 2 fields up, unless there's more to the relationship.

 

[WLHagen]

One of the other fields in the FI table is "Core Processor" - so I wanted to apply the same theory of linking one table to another by creating a "core processor" table, then linking that together, rather than typing out all the core processor's names.

 

[Norie]

Oh, right.

I thought you were going to use another field for the link between the institution and member table.

By the way, how did you get on with those 2 tables.

What I posted really was just a start.

 

[WLHagen]

Thanks, Norie - I think I got them to work.. but I have a different issue now with my institution table. See my post http://www.mrexcel.com/forum/showthread.php?t=579390