Another Newby Asking About an Excel Import

[E.Q.]

Greetings,
We've been using Excel spreadsheets as a sort of electronic e-log for a few
years. WE maintain five seperate files and combined we have about 60,000
rows (records).
The five files were designed similarly. Basically, we have the operators log
in with the word "sign-in" on a specific field which we use conditional
formatting to visually show the start of each shift.
I'd like to import all the data. I've taken a small part of the spreadsheet
database and split it up to "normalize" the data. I've numbered each log-in
and created a column for the remaining entries to relate to the login. I then
eliminated redundancies in the log files.
So I ended up with two tables; a "login" table with date, operator, work
area, shift, as well as a logID with the number I assigned. This table was
created using just the records containing "sign-in" in the appropriate
column. The "events" table consist of an Primary Key that I created in
Excel, a LogID to relate to the login table, and six other fields used to
characterize the event (5 to ease filtering and another to actually describe
the event).
I imported the two tables, linked them and was quite happy with the look of
the forms I could create with Access's wizards.
Unfortunately, when I tried to create a new record I found that the ID
fields wouldn't increment since they're not auto numbered.
I'm not terriably concerned about the number on the event table, I don't
expect a problem having Access import that and assign an autonumbered key
field; but I'm afraid with thousands of records to process I may have trouble
getting the right reference in the linked field. I can let Access create an
autonumbered key ID, but I'm concerned about trying to match the "login"
table to the respective log entries.
Any suggestions will be appreciated.
Regards.
E.Q.

 

[KARL DEWEY]

Backup the database.
Add the autonumber field to the table and make it the primary key. Add a
number - integer - long to the other table.
Build a query using both table and join your LogID fields. Change it to an
update query. Update the field that is 'number - integer - long' with the
autonumber field.

You can now set a one-to-many relation between the autonumber field and the
number field.

 

[John W. Vinson]

Unfortunately, when I tried to create a new record I found that the ID
fields wouldn't increment since they're not auto numbered.

Since autonumbers will always have gaps, and should be kept "under the
hood", it may not be appropriate to autonumber this field in any case.

If you *want* it autonumbered, you can copy and paste your current
table, *design mode only*, to a new table; change the datatype of the
field to Autonumber; and run an Append query from your existing table
into it. The autonumbering will pick up at one more than the highest
existing value.

Instead, you could use VBA code in a Form to automatically increment
the number when you create a new record. There are lots of threads
here about "custom counter" code - a Google Groups search should find
something useful.

John W. Vinson [MVP]

 

[E.Q.]

Thanks John,
I was making it harder than necessary. You're copy & paste method worked
quite well.
E.Q.