Import Data from Csv AND set relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All

I have a database that imports details of employees on a daily basis. These
employees share a number of repetitive and Common attributes such as OFFICE /
DEPT / GRADE and afew others. This table is not unique as I have a number of
other tables that have similar fields

I have run for some time by deleting the contents of the table and updating
the employee information daily, however as I try to learn more have thought
about making the database relational and creating releations to the aspects
above.

Can someone point me in the direction for the code that is required to
reinfore and update the references ... the acImport aspect is relatively
straight forward ... however I am not to sure about creating the
relationships, initially performed throught the Table Analyzer (Table aspects
selected by myself)

If this is a daft idea, please tell me and I can probably live what I have,
although everything I read suggests this other option is a tidier way to
manage the data

Many thanks for your help in advance
 
If I understand correctly, you were pretty much on the right tract. I would
suggest you create the table and establish the relationships. The if you
need to import into this table, link to the external data rather than import
it, then use either/and/or an append and an update query to move the data
from the linked table to the Access table. Then just drop the link. You do
that with the DeleteObject method.
 
Dave

Thanks for the update and I think I have the jist of what you are suggesting
I do, however for the dim and slow of thinking (Im Scottish) can you please
confirm:

I create the Table as Normal using the Import Mechanism within Access and
then use the TableAnalyser to set the relationships ?
I then create a link to the external data (updated table) ?
I then remove the data within the table and write an append query to update
the information from the linked information ?

And this will automatically update all the links ??

As an aside I have two columns in my table one called 'Home Office' and
another called 'Current Office' as staff can be transferred regularily. Both
these columns contain the same data set. Can I have both colums relating to
the same data set ??
 
Well, I am not Scottish, but I do drink a lot of Scotch. That may explain
why I am confused.

I was not suggesting using the import to create a table. I was suggesting
creating a table in design view. Then setting the relationships using the
relationship builder in Access.
The import would then be linking to the external table and moving the data
into the Access table using queries.
You mention deleting the existing data in the table. If the table has any
child tables, this could cause orphens to be left behind or it would create
an error depending on whether cascading deletes are set up.

Your last question I don't understand. That is because a dataset will have
a column, but a column will not have a dataset.
 
Dave

I dont intend to be obtuse and I can understand your confusion. Perhaps if I
explain the contents of this table may make this a bit simpler

I have an employees table which currently is in csv format. The columns in
the table Include 'Employee No' 'Forename' 'Surname' 'DOB' 'Home Office'
'Current Office' 'Grade' and afew others.

The Employee No is unique and thus the Primary Key
The Grade information can be 1 of a number values from Trainee to Manager.
Each employee has a grade and these values are repeated thoughout all emplyees
The Home Office and Current Office can be a choice of 10 Venues
The rest of the personnel information is relatively unique to each person

I thought rightly or wrongly that My main table would contain Emplye No and
the remainder of the personal information, however the Grade information
would be referenced in another table as it is repeated many times and
likewise for the Office Information which whether Home or Current is from the
same set of 10 venues.

Perhaps my choice of words is wrong or I am completely off the mark with
this proposal. I can get this to work using a single table but got to
thinking that there must be a more efficient way to do things.

Im sorry if I am talking nonsense, but even tobe told that would be a result
:)
 
Okay, that clears up a lot.
So the relationship from Employee to each of the other tables is one to many.
That makes the answer to your previous question clear. Yes, if you have one
Office Table and you want to maintain "Home Office" and "Current Office"
relationships to the Office table, you can do that.
Importing your external data should not affect any other data in the three
tables (Employee, Office, and Grade). The only issue you have is whether the
data being imported has the correct values for the fields that link to the
tables. If it does not, then you will need to devise a way to update the
imported records with the correct values. The problem is, once you have
deleted the old data, you no longer know this information. If this is the
problem, let me know. There may be a way around it.

One otther thing, you need to expand your Grade table to include my title:
Junior Assistant Flunky's Helper Trainee
 
Dave

Thanks Once again. I will persevere and no doubt be back (soon) to pick your
brain
 
Back
Top