Append Query Question (1 Viewer)

[Jakboi]

Ok. I have normalized a database I am working on. Now when I append the data from the one table to the several tables do I have to append the PK's in each one? If that even makes sense.

I have one table that has several fields. When I append the data to about several different tables does the data stay together? Meaning how will it know what data goes with other data, in terms of rows wise?

Sorry if that makes no sense just trying to understand whether or not I should append the PK to all the tables before I try it. If anyone can make sense of my question, thanks.

 

[pbaldy]

Generally you would have to append the PK of the main table to the child table to maintain the relationship.

 

[SamDeMan]

a normalized database will have relationships. review the sample northwind database.

so lets say you had one employee table with EmpIndexNumber(autonumber), EmpName, EmpBadge, EmpAddress, EmpHourly pay, EmpPayCheckAmount, EmpPayCheckDate. now you decide to normalize it, and you would make two tables, Employee table and Paycheck table. You will somehow need to relate a paycheck to an employee. you will probably want to have a field in the paycheck table called "PckEmpIndexNumber" this field (foriegn field) will relate back to to the employee table.

if you don't understand what i wrote, you will need to learn more about normalization. also, you can be a bit more descriptive of your table structure.

good luck,

sam

 

[KeithG]

The PK from the parent table will usually become an FK or part of a composite PK in the child table.

 

[Jakboi]

The PK from the parent table will usually become an FK or part of a composite PK in the child table.

Thanks for your responses. Now in terms of this response will this happen by simply doing the append? I did try to just append the fields without the PK, does this create the FK automatically if database is normalized with relationships setup?

I guess I didnt know if append query takes into account the relationships, if thats a right question to ask.

 

[pbaldy]

No, you have to include it in the append.

 

[Jakboi]

Thanks pBaldy, never quite used Append Queries before so bare with me. So when I append my new tables I should include the PK from the old table.


OldTable

Id (pk)
Stuff
Things
Somthing

StuffTable
StuffID (Pk)
Stuff

ThingsTable
ThingsID (pk)
Things

Thats a bad example probably, but Do I want to append the ID from the old table to the StuffID of the new table. Then append the ID from the old table to the ThingsID to the ThingsTable.

Thanks for any help. Trying to understand the correct way this should be done since I will have to append weekly from an imported Excel spreadsheet.

 

[pbaldy]

Perhaps something like:

INSERT INTO StuffTable(StuffID, Stuff)
SELECT ID, Stuff
FROM OldTable

INSERT INTO ThingsTable(ThingID, Things)
SELECT ID, Thing
FROM OldTable