Setting up form/table so typing one field updates others.

[Guest]

I have a simple situation I'm struggling with:

1) 3 Tables: A) Members B) Events C) Member registration for events.

Obviously Table C obtains alot of information from tables A and B... like
MemberID, MemberName, MemberCity from Table A and EventID, EventLocation from
Table B.

2) I want to be able to just enter one field into C (table or form), such as
MemberID, and have the MemberName and MemberCity that's related to that
MemberID be "filled in automatically" into the appropriate fields in Table C.

How do I accomplish this ?? I'm sure it has something to do with
establishing relationships, maybe setting one or more of these fields as
Lookups, etc...

This must be very simple but I just need a bit of a "push" in the right
direction. Thanks very mcuh.

 

[Albert D.Kallal]

How do I accomplish this ?? I'm sure it has something to do with

establishing relationships, maybe setting one or more of these fields as
Lookups, etc...

The above is the right idea. You seem to hint that those other tables needs
a bunch of values from the other tables. This is wrong, and misses the idea
of a relational database. In a relational database, we don't copy the
information over and over...we leave it in ONE place.

Obviously Table C obtains alot of information from tables A and B... like
MemberID, MemberName, MemberCity from Table A and EventID, EventLocation
from
Table B.

Ok...it obtains the information..but the ONLY thing you need is the member
id. Any information like name, phonenumber, or member City etc. will come
from the main table (a I think in yuour case). So, you NEVER need to copy
the city...but use relational database joins to grab this information.

2) I want to be able to just enter one field into C (table or form), such
as
MemberID, and have the MemberName and MemberCity that's related to that
MemberID be "filled in automatically" into the appropriate fields in Table
C.

no....you don't need to do that. Just enter the member id..and *display* the
other information. You can use a sub-form to do this. I give some ideas and
examples here

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

You also could simply build a query based on tables..and JOIN in the other
fields from the member table. They would actually be editable..and no code
(or as above a sub-form) would even be needed. So, you could in place of a
sub-form use a query to pull in the member information based on the
member_id

How do I accomplish this ?? I'm sure it has something to do with
establishing relationships, maybe setting one or more of these fields as
Lookups, etc...

Yes...just remember you do NOT want to write code to "copy" that
information. What happens if you miss-spelleed, or update the city
field...you now have to go and find the zillion places you copied the data
to??? (this is just not workable..and if you setup the relations
correctly..then the updated city value will appear updated
everywhere.....and will do so because you setup your relations correctly,
and in fact only have ONE copy of the city field.......

 

[Guest]

You don't. You only need one piece of data to share from table A and that is
the MemberID. The same goes for EventID. Both the MemberID and EventID fields
should be the primary key fields in their tables.

Your Member Registration table should have it's own primary key. An
autonumber is probably the best bet. As I said before you should have a
MemberID field which is the foreign key to the Members table and EventID
which is the foriegn key to the Events table. The combination of EmeberID and
EventID should be a unique index to keep from accidently entering the data
twice. Your Member Registration table is known as a bridging or linking
table. When you need to see what members are registered for which events, you
create a query that links these two tables though the Member Registration
table.

I suggest that you read Database Design for Mere Mortals by Hernandaz to get
a good grasp on relational database design.

 

[Guest]

Thanks Jerry.... I think you and Albert are saying the same thing. So I don't
need to "copy" any of the related information to Table C. Just the "key"
field from each of Table B and C.

This makes so much sense...

Then I can run reports, queries, etc. on Table C and it will "find" the
related information in Tables A and B...

I'll give this a try this evening and let you know where I'm at with it.