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!

 

[strive4peace]

as mentioned, you need a way to match records so you can know what is new and what must be updated. Normally data coming in from other places needs to be parsed and verified. For this reason, it is a good idea to Import data as opposed to Linking so that you can modify it and the structure. If you are linked to Excel, all you can do is read the data.

On the other hand, if you link to the Access data from Excel, you can read and write ... but I do not feel that is the best solution as data structure in Access should be normalized, not set up to match flat imports.

Rather than 2 tables with redundant fields, you should normalize the data. An analogy: instead of putting blue books in one table and red books in another, put all books in the same table and add a column for color

In Access, it is best not to include spaces or special characters in field names. Use CamelCase to name your fields ... LastName, FirstName, ...

"Middle Initial" not only has a space, but it is 14 characters long ... how long is the data it is labeling? use a shorter field name.

Here are some video tutorials on planning a database that you should watch:

Learn Access By Crystal - YouTube

PID is a much better name for your PK than 'Personnel ID'

If some people are in both tables, or might switch, then you should store Skills that people can have in a separate table. For instance:

People
- PID
- etc

Skills
- SkillID, autonumber, PK
- Skl, text, Skill abbreviation
- Skill, text, name of the skill
- sklNote, text, 50 -- short note
- dtmAdd, date/time, DefaultValue =Now(), date/time record was added to the table

dtmAdd is a tracking field that gets added to every table. Access then tracks when records were created. This is VERY useful. Tracking fields are named the same in every table so generic code can be used to update them. Other tracking fields I add are date/time updated, who added, who updated, and more depending on what I am doing. If importing, another tracking field will be ImportID corresponding to the autonumber in the import data table (another reason to import, not link -- and I also add at least one long integer key field to the import structure).

I generally add a Note field to every table -- prefacing the field name with a table alias so the field name is unique in the database.

When data is imported, I rename the table to start with 'import', then briefly what it is. If it is a one-time import, I will append _yymmdd to the end. In your case, you will be repeating the process so you would want to keep the same tablename.

You have values in your imported data that look like they will change over time. Whatever does not specifically describe a person should go into another table. Then you can also track when values changed, if that would be useful.

Also, since references are made to Rank and Squadron, which are entities themselves, I suspect you have (or should have) tables for them. That means that in your people table, you would store RankID and SquadID, not the text values.

What else are you doing with this data?

 

[strive4peace]

it now occurs to me that your 2 tables may have been the Import data and the Access data ... not skills ... oh well. It is a good lesson anyway

To be clear, I keep the autonumber fieldname for import data = ID. This is the only time I do not change the autonumber ID field name to be relevent ... because with imported data, it is not. The import data won't be related to anything in the database. It is only useful if results need to be reviewed.

 

[strive4peace]

if Access and Excel are indeed skills, then a 3rd table would be required to cross-reference People with Skills

PplSkills
- PplSkillID, autonumber, PK
- PID, Long Integer, FK to People
- SkillID, Long Integer, FK to Skills
- pSkNote, text, 50
- dtmAdd, date/time, Default Value =Now(), date/time record added

 

[xenou]

Out of curiousity, what is this:
AGR Days
AGR Value
Temp Days
Temp Value
School Days
School Value
Annual Days
Annual Value

What do the values mean?

Note that I'll have a play with this to see what I come up with but it may take a few days (I'm exceptionally busy this week at work).