Update Tables and Relationships

[DarkOne72]

Hello,

I hope someone can help me asap!
I have 2 tables, tblMaster and tblCompleted. I have ALL of my data in the tblmaster with a primary key of ID AutoNumber; and the tblcompleted will have some of the information from the master like name, address, city, state, zip and its primary key is also an ID Autonumber seeing as how both tables I allow duplicate names and address for businesses and suites.
Heres what I need to know:

Lets say I have 3 records in the tblmaster named 'Johns Computer Shop' with different addresses like this:

Johns Computer Shop 123 Test Way
Johns Computer Shop 456 Runway Rd
Johns Computer Shop 789 Go Home St

and now I put a record in the tblcompleted table (since I done a job there):

Johns Computer Shop 456 Runway Rd

How do I make these two stay static to only each other and also if for some reason the name changes and I want to change it in the tblcompleted table and only that one record in the tblMaster table too not all the locations in the master?

Is this possible? How would I accomplish this and what relationship do I need to join on etc?

Thanks for your help in advance.

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

The basis of all tables in a relational database system, such as Access, is normalization of the tables. A hint, you don't have the same data in two different tables such as companyname, address, etc.
Also, you might want to search these forums on the Autonumber bit. I wouldn't advice using autonumber as a primary key. Make up your own.

 

[MajP]

So a couple things to clarify what PHV is saying.

if you have tblMaster with a "primary key of ID AutoNumber". This should look like

1 Johns Computer Shop 123 Test Way
2 Johns Computer Shop 456 Runway Rd
3 Johns Computer Shop 789 Go Home St

"and now I put a record in the tblcompleted table (since I done a job there):"

To put a table in tblCompleted for
"Johns Computer Shop 456 Runway Rd"
you would not repeat the above information you would only put the value 2 in a field in the tblCompleted. Ex:

Field name: compShopID_fk
2

2 links to "Johns Computer Shop 456 Runway Rd". In a relational design you do not repeat the same information in different tables. If "Johns Computer Shop 456 Runway Rd" changes it name in tblMaster, and tblMaster and tblCompleted are linked by ID to compShopID_fk then if you change the name in tblMaster no problem.

Also what does it mean to have three Johns Computer Shop with different addresses? Does this represent three different shops with the same name?

 

[DarkOne72]

Thanks guys for the help, I was able to finally get it, especially with the help of MajP.

However fixing this problem I came to wanting to know how to create a drop down combo list based off of a Tech table and select the Tech number it will show his name in another text box and in turn show all of his jobs only in a subsheet on the same form.

Oh and btw MajP yes it was the same company with different stores at various parts of town.

Thanks again!