How do I add an auto primary key to data imported on a regular bas

[Guest]

I want a database which I will import data to on weekly or monthly basis and
I'm not sure how you handle the primary key if you need one to be system
generated by Access automatically on or after import.

Thanks for your help!
Kristine

 

[Guest]

Rather than importing the data directly, eithe link it as a table or import
it into an existing table that has all the fields except the primary key.
Use an Append query to copy the data into your production table that has the
primary key field. Jet will populate the field for you as it appends the
records.

 

[Guest]

Okay that seems like it will work! One more thing though....I have created
the append query for two tables and have about 5 more to do. The autonumbers
added for the Primary Keys end up the same on each table. They all start with
1, etc. Won't this be a problem? I was thinking each Primary Key needed to be
unique throughout the database.

Kristine

 

[Guest]

No, primary keys need only be unique to the table. Also, autonumbering is
specific to a table. That is why you are seeing them all start with 1.
Now, what you did not say was whether any of the tables will be child tables
to another table. If this is the case, you have a whole new set of problems.

 

[Guest]

Ah yes...now I think we're getting to the crux of my problems. It seems the
big picture of what I am trying to do is take data from a huge relationship
database and recompile my specific data into the reports I need so that I can
simply import the data each month and I will instantly be able to pull up the
reports. Now we have this horrible Excel Workbook where formulas have to be
copied and pasted, some data is keyed in manually, other data is moved from
one column to another, etc. With what I am trying to do it seems the data has
changed from relational to very specific and although it seems fathomable to
somehow contrive relationships out of it all that seems like way more work
than it's worth. I was hoping I could somehow use the data as I get it from
the huge database into the 7 or so tables then figure out how to create the
reports without really having relationships. It seems so close to possible to
create the reports I need from these tables because the tables are already so
similar to the reports (sorry this is difficult to explain). I know this is
not how Access is meant to be used. Is there something other than MS Access I
should try?

At any rate, thanks for your help!
Kristine

 

[Guest]

No, Access is a great tool. Maybe you need to rethink what you are doing.
Tables should always remain normalized and relational. That is what a
database is all about. You use queries to transform the data.