Trying to understand Linked Tables and Relationships, help?

[pixelkicker]

Hello!

I am somewhat new to the more advanced features of Access. I am currently using Access 2010 and I am running in to some issues with something I thought would be simple. Here is the story:

We are creating a new Personnel table (from scratch) in Access. I'll refer to this table as "Access Personnel Table."

We have a automated system that spits out a excel file once a day with a list of personnel and about 4 other useful fields of information. I'll refer to this as "Excel Roster"

The Access Personnel Table needs to be auto-magically updated with the information from the 4 useful fields that are in the Excel Roster.

This update happens once a day. And CAN be managed by a person if necessary (but preferably not.)

I have tried importing the Excel file as a Linked Table but I am having issues with the relationships. I also don't know how to "Append" the Excel Roster to the table without overwriting it or changing it. I also don't know how to automate this.

Also, the Access Personnel Table is going to later be the used for the Primary Key in several other tables.

ANY help or guidance will be much appreciated!! Thanks in advanced!

 

[xenou]

Hi,
Linking Access to Excel files is evil and should be avoided (locks the Excel file, for one, and Excel doesn't understand things like keys and relations, for another, or even enforce columnar datatypes). My preference would be to import the data. Probably to a temp table where it can then be pushed around as needed.

What kind of info is in the Excel Roster? I would think a Personnel table wouldn't change much (names, ID, hire dates - these things don't change).

 

[pixelkicker]

Hi,
Linking Access to Excel files is evil and should be avoided (locks the Excel file, for one, and Excel doesn't understand things like keys and relations, for another, or even enforce columnar datatypes). My preference would be to import the data. Probably to a temp table where it can then be pushed around as needed.

I am fine with any/all options. How would you automate this?

It should also be noted that I am not worried about the second-order effects to Excel. This file is generated specifically for this purpose and if it is locked or "dead in the water" that is perfectly fine.

What kind of info is in the Excel Roster? I would think a Personnel table wouldn't change much (names, ID, hire dates - these things don't change).

Yes there is basic employee info like name, ID, status, section and position. It changes more than you would think. This is for a very large organiztion and people move around a lot.


Thank you for your response. Any additional guidance would be much appreciated!

 

[xenou]

You have two strategies here:

1) completely reload the table every time
2) just update new records and changed records

How large is large? Do you have a primary key such as employee ID that is reliable?

 

[pixelkicker]

You have two strategies here:

1) completely reload the table every time
2) just update new records and changed records

How large is large? Do you have a primary key such as employee ID that is reliable?

I am fine with either option as long as it doesn't effect secondary tables that will rely on that data. The roster doesn't have changes very often but it does get them. It currently has about 200 records.

I was planning on generating a primary key UID for each employee after the data is pulled from the excel file (just autonumber.)

 

[xenou]

Do you have any employee ID's in your data that you are importing? Problem is with an autonumber primary key is that it's not going to help you match to the imported data at all, so it won't help in identifying new records or changed records. Names are a problem (people marry, divorce so names change). Would help to have a key.

Also, how many fields in the table? If possible - field names and datatypes (text, number/integer, number/decimal, date, or boolean) Might as well give the table name and the name of the Excel file - so I don't have to guess or use dummy names in sample code.

 

[pixelkicker]

I got the excel file imported as a new table. Now I need to compare tblPersonnelExcel to tblPersonnelAccess and update records that have changed and add new records found to the bottom so as not to mess up my new primary key. How is this done? Thanks!

 

[xenou]

We need some way to match the records. Do you not have employee numbers or employee ids? Those are very common in almost any company.

 

[pixelkicker]

We need some way to match the records. Do you not have employee numbers or employee ids? Those are very common in almost any company.

No, that is what I am trying to add. Right now I have the following:

Table A (tblPersonnelExcel)

Last Name
First Name
Middle Initial
Work Status
AGR Days
AGR Value
Temp Days
Temp Value
School Days
School Value
Annual Days
Annual Value​

Table B (tblPersonnelAccess)

Personnel ID (Primary Key, initially just running an AutoNum)
Last Name
First Name
Middle Initial
Rank*
Squadron*
Work Status
AGR Days
AGR Value
Temp Days
Temp Value
School Days
School Value
Annual Days
Annual Value​

​

I need to bounce Table A off of Table B, apply any changes without altering the Personnel ID associated with the person (this is important because I will have other tables using this PID) then I need to find any completely NEW records and append them to the end, thus giving them a PID.

Thanks!! If you can help me get this to work you will be saving tax-payers money! Trying to automate some seriously outdated procedures. lol

​

 

[xenou]

Okay.
Just to be safe, can you list the datatypes of the fields in these two tables.

Table A (tblPersonnelExcel)
Last Name
First Name
Middle Initial
Work Status
AGR Days
AGR Value
Temp Days
Temp Value
School Days
School Value
Annual Days
Annual Value

Table B (tblPersonnelAccess)
Personnel ID (Primary Key, initially just running an AutoNum)
Last Name
First Name
Middle Initial
Rank*
Squadron*
Work Status
AGR Days
AGR Value
Temp Days
Temp Value
School Days
School Value
Annual Days
Annual Value

tblPersonnelExcel is a real table in Access, right? Not a linked table? I would generally prefer importing the Excel data into Access then linking to an Excel file.