Appeding data to tables

[Ryan]

I am drawing a blank on this so I was wondering if you
guys could help.

For the sake of simplicity lets say I have 2 tables with
the following fields:

Table1:

Area (PK)
Area_code (PK)
location

Table2:

Area (PK)
Area_code (PK)
ID (PK)
FName
LName
MI

----------------------------------------------------------
I would like to insert Area_code, ID, FName, & MI into
Table2 and have the field Area be updated automatically
from the relationship it has with Table1. Is this able to
be done or do I need to specify the area when I insert the
data into table2?


Thanks,
Ryan

 

[Tim Ferguson]

I would like to insert Area_code, ID, FName, & MI into
Table2 and have the field Area be updated automatically
from the relationship it has with Table1.

No you don't. The whole point of having the first table is to avoid having
to store 157 records with the same Area_Code information. The inevitable
result is that some of them will end up with the wrong Code for the Area
and then you have something worse than wrong data -- inconsistent data.
This is bad because it invalidates the _whole set_ because you don't know
which is right and which is wrong.

The idea of a relational database is to keep the information once and look
it up when needed. When you need to see the Area_Code, you join Table1 onto
Table2... When you notice one of the Area_Codes is misspelled, you only
have to change it once in Table1, not 157 times in Table2.

Hope that helps


Tim F

 

[Ryan]

Tim,

Thanks for replying but I don't think you understood
exactly what I was asking. I know it is easy to view all
of the information that you want once it is the tables but
I was sondering about entering data into the tables. I
wil tell you exactly what I am dealing with and then maybe
you will be able to see what I am asking...

I am creating a database and 2 of the tables in the
database look like:

Table1:

Area (PK)
Area_code (PK)
location

Table2:

Area (PK)
Area_code (PK)
ID (PK)
FName
LName
MI

The data in table 2 is updated weekly from an outside
source. Unfortunately this souce does not contain the
fields for Area. (This is why I created table 1..it has
all of the area codes associated with each area) I want
to be able update table 2 with a new record by inserting
the fields Area_code, ID, FName, LName, & MI and have the
Area field automatically be updated by the previous table
and I am not sure about how to do that.

I assume that all of my data is accurate (if its not then
I know how to change it)

Thanks again,
Ryan

 

[sbd]

Ryan,
My initial reaction when reading your original post was
the same as Tim's. it looked like you might be
denormalizing, but it wasn't clear. having read your
response to him, its still not clear.

Let me see if I can help clear it up. In Table 1 you have
a list of Areas and the area codes that reside in that
area. Is that correct? Because if it is, then I don't
understand why both Area and Area Code is a PK. If there
is only one area_code to an area (which would be the case
if both were a PK) then why bother with both?

It sounds like you want to enter the Area code for a
person and have the Area automatically assigned. But that
would denormalize the database by putting redundant data
in Table 2.

Maybe if you gave some examples of what Table 1 contains
and what you want in Table2 it might help.

 

[John Vinson]

I would like to insert Area_code, ID, FName, & MI into
Table2 and have the field Area be updated automatically
from the relationship it has with Table1.

Why?

Storing the Area field redundantly in your table is neither necessary
nor prudent. It wastes space, and risks the danger that you could have
a particular area_code refer to one value of Area in one table and a
different value of Area in the other.

Just use a Query linking the two tables by Area_Code to look up the
Area whenever you need it; or a Combo Box bound to the Area_Code but
displaying the Area.

 

[Ryan]

Scott,
The reason I have Area and Area_code both as a primary
key is because there can be many Area_codes in an area,
further there are many Id's that are associated with each
area code. an example of a couple records would be:

Table1:
<Record 1>
Area: Main Building
Area_Code: F1
Location: xxx

<Record 2>
Area: Main Building
Area_Code: F2
Location: xxx

Table2:

<Record 1>
Area: Main Building
Area_Code: F1
ID: 11111
FName: John
LName: Doe
MI: P

<Record 2>
Area: Main Building
Area_Code: F2
ID: 22222
FName: Ryan
LName: Simmons
MI: A

The reason I have these split up is because Location is
only dependant on Area and Area_code. Also, there are
more than 1 Areas I was ust using 1 to show that there
are more than one area codes associated with each.

Every week a database updates this information and I want
to append/update this info into table2. Unfortunately
there is not a field for Area in the source db so I would
like it to automatically update the Area fields in table
2 with the appropriate areas from table 1.

The reason I am trying to do this is because Acess wont
let me create a relationship between table with
referential integrity if I don't use this schema. I have
tried linking with just the Area_code field but that
doesn't work and I am not able to enter data and keep the
Area field blank since it is a primary key.

I hope this helps and sorry it is so long,
Chad

 

[Tim Ferguson]

Thanks for replying but I don't think you understood
exactly what I was asking.

Oops: sorry, yes I missed the compound PK in your description, but I am
afraid that I still don't understand what is going on.

...
I am creating a database and 2 of the tables in the
database look like:

Table1: ....
Table2:
...
The data in table 2 is updated weekly from an outside
source. Unfortunately this souce does not contain the
fields for Area.

My puzzlement is as follows:

The reason for having a compound PK is that there can be many Table1Things
that belong to each Area, and many of them in each Area_Code, but only the
combination of them is unique. In other words, there is (Main Building, F1)
and (Main Building, F2) and (Old Hut, F1) and (Old Hut, F2) but each of
these can only have one Location value.

Now, if the imported file only has the Area_Code value (F1, F2), you
presumably cannot know which Area the record belongs to. Therefore, there
is logically no way to fill in the missing data.

I strongly suspect that there is a design problem, and that what you want
is a simple translation query: but if you can post back with a bit more
information, we may be able to help.

Best wishes


Tim F

 

[Ryan]

Tim,
Thanks for your patiencs and sorry that I didn't
specify this before but Area_code is unique as well.
IE:
F1 is only related to the Main Bldg
(No other Areas will have an F1 Area_code)

That is what table1's data contains (every area code
associated with each area) So when updating table 2
without the Area I want to automatically have the area
updated by going to Table1 as a reference to see which
area the given Area_code is associated with. Let me know
if there is a better way to do this or if you don't
understand.

Thanks again,
Ryan

 

[Tim Ferguson]

Thanks for your patiencs and sorry that I didn't
specify this before but Area_code is unique as well.
IE:
F1 is only related to the Main Bldg
(No other Areas will have an F1 Area_code)

This is what I suspected, but it is most emphatically not what you have
created with the model as posted originally.

If each record has a unique Area or Area_code, then either would make a
good Primary Key, but using the combination of the two is Not What You
Want. See my previous post. Whichever one you choose not to be Primary
should have a Unique Index created anyway, to help the queries but mainly
to provide protection against duplicates.

That is what table1's data contains (every area code
associated with each area)

In other words, a simple translation table -- although I am a bit perplexed
by the Location field. I probably don't need to know.

Anyway, if you are importing data with the Area_Code value, then you really
don't need to copy the Area value anywhere. Whenever you need to see the
Area value, just join the tables in order to display the Area (and/ or the
Location). I suppose it is possible to organise some kind of field-swap to
change the stored value, but since they can both provide a unique key into
Table1 then you really don't have to.

Hope that helps


Tim F