Importing data

[Tedd]

If I have a table set up in Access and this table contains
data, will importing new data into the table overlay the
existing data? For example, I am setting up a table with
an employee number(key field) and their salary. They get
a raise. I import the new data with their employee number
and new salary. Will I have two rows of data for this
employee? I want two rows so that I can have a history of
the employee.

 

[Norman Yuan]

It depends on how the database is designed and how do you import data. Is
the database designed to keep employment history (or salary change history)
in its table structure?

What you asked may indicate that the Access database is not designed
properly to keep information as you desired. To use Access, not like other
apps in MS Office, such as Word, Excel, you need some basic database
knowledge. In your case, if you want to keep track employees' employemtn
history (salary changes, duty changes...) you need more than one table to
organise these infornation:

a table to hold basic employee's information, such as first name, last name,
age, gender, ...
a table to hold employment history, such as salary changes, with fields
like, start date, end date, salary, and other benefits.... You may have
other table(s) to hold other employment related info. This (or these)
table(s) usually has MANY-To-ONE relationship to the basic employee table.

If I were you, I'd first look into the database design very hard to make
sure it is designed correctly before put data in (or seek some professional
help, if the data is very important to the business).

 

[John Vinson]

If I have a table set up in Access and this table contains
data, will importing new data into the table overlay the
existing data? For example, I am setting up a table with
an employee number(key field) and their salary. They get
a raise. I import the new data with their employee number
and new salary. Will I have two rows of data for this
employee? I want two rows so that I can have a history of
the employee.

Depends on how you do the import. Where are you importing *from*?

I'd suggest *linking* to the import source, and running an Append
query to append new records to your history table, just to be sure.

 

[Guest]

Thanks for the infomation. I have worked quite a bit with
SQL on a mainframe(mostly running queries) however this is
the first time I have been asked to set up a database in
Access.
This database needs to keep track of employment history.
I am given a file(from a mainframe) and need to put that
into an Access database so that it can be queried.
Everytime an employee changes their status a new record
will be sent to the database. So there could be multiple
rows for each employee (for example if someone gets a
raise the database needs to have their old salary and
there new salary and the date it changed).
From what you say, it looks like some work will need to be
done to the data before it gets to Access to seperate and
organize the data.
What do you think?

 

[Norman Yuan]

Again, first things first, you need your Access DB designed properly. In you
case, you have at least two tables:

tblEmployees tblEmpHistories
------------- -----------------
EmployeeID HistoryID
FirstName EmployeeID
LastName StartDate
Address End Date
.... PayType
.... PayAmount

You create a ONE-To-Many relationship between them.

Then it is obvious that how to get data into place where they should be. The
importing method totally depends on where the data is and how it is
structured (formatted). Assume you can get data in plain text format,
delimited by tab, comma, or space. You can write a few lines of VBA code to
read the records in text file line by line and then update data into
tblEmpHistories