Data gets jumbled on append qury..

[willmafingerdo]

Hello,
We use to use Excel to track our records at work until I made an access DB to track our records. I went through our old Excel log and broke it down into the appropriate tables ect and normalized all the data to match the DB. The first table is the employee info, the primary key is an auto number and this number feeds through the other tables to match up the data, so when I enter the employee's name ect that starts record 1 and the record number 1 flows to the benefits table where their benefits info is stored ect... so I appended the name table to our new DB and everything went fine. So I exported the table so I could match up the record numbers in the other tables so when I append them to the new DB record 1 will match record 1 ect.. Well this did not work...and I was wondering if anyone could tell me why this happened.

If more info is needed or anything let me know.


Thanks,
Will

[ssanfu]

Welcome to the forum.....

Sounds like it should have worked.
What are the table structures of the two tables?

Both the Employee table and the Benefits table have Primary key fields?
And the Benefits table has a foreign key field of type number (Long)? (this is the link to the employee table PK field)

[willmafingerdo]

Hello,
the main table , EmployeeInfo, primary key is just ID, and the primary key on the other main tables are RecordNumber. All of the primary keys are Number/Long Int. Not sure if itll help or not but below is the DB relationship

[ssanfu]

Not sure if itll help or not but below is the DB relationship

Yes, it helped.
You have four 1-to-1 relationships. It is very rare to see one 1-to-1 relationship, let alone 4. It looks like you did not do any normalization in converting from a spreadsheet to a RDBMS. (this has been called "committing spreadsheet" - and I am guilty of doing the same thing in the past)

Referring to the tables "tbl_EmployeeData_EmployeeInfo" and "tbl_EmployeeData_BenefitInfo",
1 employee can have many benefits
1 benefit record has 1 employee.

This is a 1-to-many relationship. The "EmployeeInfo" table PK field should be linked to a FK field in "BenefitInfo" table, NOT to the PK field.
("EmployeeInfo" PK field ---> links to ---> "BenefitInfo" FK field.)

Granted I don't know your business/project, but looking at the "BenefitInfo" table, it looks to me like it could be split into maybe 8 tables.


Before you proceed, I would suggest that you learn about normalization.

Here is info about PK fields:
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

All of my tables have an autonumber type PK field. This is why I use autonumbers
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers



You should take the time to work through these tutorials
http://www.rogersaccesslibrary.com/forum/forum46.html

[willmafingerdo]

Thanks ssanfu for the info. I think I get what your saying, I have to pretty much teach myself how to do this so its a little slow My question is, I set up the main tables (all the EmployeeData tables) as 1 to 1 so when the employee name SSN ect is entered I thought I would need to have that number flow to the other tables in order to link them. So if im getting what your saying, and what I got from the articles, I would basically have a table with all the foreign keys in it to pull them all together? Sorry if im being a little slow on this.

I also see how I should brake my tables down a little smaller, I just thought having all of the benefits info for example in 1 table was making it normalized.

Guess the main point is I need to keep researching and learning... I have been having other issues with this DB and I hope once I get this straightened out they will go away too Thanks again for the help

[ssanfu]

So if im getting what your saying, and what I got from the articles, I would basically have a table with all the foreign keys in it to pull them all together?

So lets look at three tables: "EmployeeInfo", "EODinfo" and "OrgCodes". (I didn't use the whole table name .... too much typing ...I might use "tblEmployeeInfo")

BTW, I use the suffix "_PK" to indicate a primary key field and "_FK" to indicate a foreign key field.
And I always cluster the FK fields just under the PK field.... easier to find the FK fields, especially when looking at SQL of queries.
(not necessary, but it works for me )


OK, In "EmployeeInfo", I would have field "EmployeeInfo_PK" as type Autonumber and the primary key field (the first field). This is the field that links with other tables. Then the other fields in the table would follow.

In "EODinfo", I would have "EODInfo_PK" as type Autonumber and the primary key field (the first field).
The second field might be "EmployeeInfo_FK" as type Number (Long Integer); it is the foreign key (_FK) field that links this table to "EmployeeInfo".
The third field might be "OrgCodes_FK" as type Number (Long Integer), which is the linking field to the "OrgCodes" table.

And the table "OrgCodes" would have fields "OrgCodes_PK" as type Autonumber, "OrgCode" (remember, no spaces in names), "OrgName, and "AppropriationCode".



Now adding another table, "EmpRecords", I might have "EmpRecords_PK" as type Autonumber, "EmployeeInfo_FK" as type Number (foreign key field that links to "EmployeeInfo"), then the other fields.



Is any of this making sense??

[willmafingerdo]

Thanks, im going back over stuff and doing some more research. Thanks again for all the info!!