Insert issue

Needing to Insert field into multiple tables

Page 1 of 1

2 Replies - 962 Views - Last Post: 27 February 2008 - 02:02 PM Rate Topic: -----

#1 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Insert issue

Post icon  Posted 27 February 2008 - 01:07 PM

Ok, so as I have mentioned in earlier topics, I am trying to normalize an Access db. The issue that I have now run into is that the field that I am using as a PK of sorts (can contain duplicates) needs to be inserted into every table that I have. All of the information, is going to be enters by the user on a form.
Here is the issue....how do you write the insert code for this. I do not want to have a query for each table that the data needs to be in.
Thanks for any help!

Is This A Good Question/Topic? 0
  • +

Replies To: Insert issue

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,127
  • Joined: 18-April 07

Re: Insert issue

Posted 27 February 2008 - 01:32 PM

You might want to post a few examples here for us to see because it appears you might be going down the wrong path in your normalizing. Some of the factors you have mentioned make me believe you have problems in the normalization because of you mentioning primary keys having duplicates. If they have duplicates, it shouldn't be a primary key. Primary keys should always be unique unless you join it with another field and make it a composite key in which it again will be unique. One of the rules of normalization, in first normal form, is that values are atomic. That is, no repeat data.

Make sure you straighten that out first and foremost. It is crucial before you attempt to move your keys elsewhere.

After that is hammered out, it sounds like you want to rebuild key to key relationships across your tables. Insert the new primary key for a record to its related records in other tables. This is a very difficult task if for some how you lost or deleted your original keys (that or they had no keys to begin with). You will have to find out how to link the records across, but that can be risky and lead to incorrect relationships. Hopefully this is not what you are asking about. Let me know if it is.

Lastly, if you find yourself entering form data the user entered into multiple tables, then that is another red flag that your normalization is not right. The idea of normalization is to cut down repeat data, keep it in one location (typically in one table related to its key), and then be able to form relationships between the pieces of data to answer queries.

For example, if you find yourself entering a persons name, age, sex, height, weight into 5 tables you should instead create a table called something like "person" which contains all those fields and a primary key. Then insert the key into each of those 5 other tables in a field like "personID". But why? That is because if the person's name changed (which is customary for women who marry) you change it in the Person table, not issue five different update statements to update the name in each of the 5 tables. One update instead of 5. It works great for deletes too and allows you to "cascade" the delete if need be. You delete the person from the person table and you can have it delete all related records referring to that person in the other 5 tables.

I am not one for teaching all there is to know about database normalization theory, but if you step through at least the first three normalization steps of the Boyce-Codd Normalization process you will be on your way to avoiding this potential problem you are having.

Boyce-Codd Normalization steps

I hope that answered the question somewhat, even if it wasn't what you wanted to hear. :)
Was This Post Helpful? 0
  • +
  • -

#3 P4L  Icon User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,788
  • Joined: 07-February 08

Re: Insert issue

Posted 27 February 2008 - 02:02 PM

Hey Martyr,
Thanks for the info. I think I mis-spoke when I said pk with duplicates. Let me give some more background on what exactly I am trying to do. Last year a database was created for specific people in a department to be able to audit the others work. In the creation of the database, which only took about 3 months, the creator lumped all of the data for each task that can be performed into separate tables. What I have done is I have taken one table, and broke the data out into multiple tables. I have put an autonumber in place as the PK to link the tables together since the policy number, I work for a Life Insurance Company, need to be able to have duplicates..for if the policy gets audited in the various stages of being issued, and can be re-auditted in a specific stage, and needs to be entered again. So, here are what the tables look like:

tblUserId:
Id (PK, autonumber)
Policy
UserId

tblAudit_Date:
Id (PK,autonumber)
Policy
Audit_Date

tblAuditMonth:
Id (PK,autonumber)
Policy
AuditMonth

tblAuditor
Id (PK,autonumber)
Policy
Auditor

tblTask:
Id (PK,autonumber)
Policy
Task

tblTask2:
Id (Pk,autonumber)
Task

tblAssembly:
Id (PK, autonumber)
Policy
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)
ScoreInfo (yes/no)

I put tblTask2 in place so that the user will be able to select on the form the task that is being auditted. At the same point, the auditor, and user will also be selected from a combo box on the form.

From all this, I have to be able to create a report for each user, that entails all the tasks that they did in a particular month, and the average score for that month per task, and all users do not do all tasks.

What prompted the post was the fact that I need to be able to insert the policy number into each of the tables, and since I need to be able to enter duplicate policies, the policy number could not be the PK.

I hope that this gives more insight into what I am trying to do.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1