Autofill data on import

[Brian Rogge]

The database is for retail products. Two columns are department and
department ID. The data is prepared in Excel and then imported to
Access. In Excel, only the department name is loaded. I would like to
have the department ID automatically loaded. The department name and ID
info is loaded into a separate table.

Thanks,

Brian

 

[Tim Ferguson]

Two columns are department and
department ID. The data is prepared in Excel and then imported to
Access. In Excel, only the department name is loaded.

It's quite rare, but in this case the Excel model is the correct one. You
should join this table to the Departments table in a query in order to see
the other Department details.

If you try to store both in the same table, it will not be long before you
have one row that says DepartmentID=1023, Department="Ladies' Underwear",
and in another row it'll be DepartmentID=1023, Department="Sports
Footgear". Which one are you going to believe then?

B Wishes


Tim F

 

[Brian Rogge]

Figures I'd grab something rare

I'm just learning Access. I know my way around Oracle well enough to
know about normalization. I'm just looking to get pointed in the right
direction for updating the ID field. I have a one to many relationship
set between the product and department tables. But I haven't figured
out how to auto update the field during auto load. The data form is
setup to auto update, so I avoid the very situation you are referring to.

I could easily set up a lookup function in Excel to accomplish that
prior to the load, though I've never setup a lookup into an Access db
before, just to static data on a worksheet.

R/

Brian

 

[John Vinson]

I have a one to many relationship
set between the product and department tables. But I haven't figured
out how to auto update the field during auto load.

I'd do it in a slightly different manner. Use File... Get External
Data... Link to link to the spreadsheet; then create an Append query,
joining the Department Name in the spreadsheet to the Department Name
in the Department table. Make this query an Append query and you'll be
able to append the ID from the Department table, and the rest of the
data from the spreadsheet.

Spelling errors in the spreadsheet will, of course, cause loss of
data. You may want to use a Left Outer Join to the department table -
this will store a NULL into the DepartmentID if there is no match, but
at least you won't lose the data.

 

[Brian Rogge]

Actually, I don't need to append, but update the existing records. But
thanks for the info on linking to the Excel table. That may come in
handy. I ended up bringing in the Access data into Excel, and running a
lookup function and then re-improting back to access. Not the best way,
I'm sure, but it works. I'll come up with something better tomorrow,
after I read some more.

I was hoping there was a db trigger that would take care of that.
Probably is, I just haven't figured it out yet. The update query
probably requires a loop which I'm too tired to write right now.

Thanks, all.

Brian

 

[John Vinson]

I was hoping there was a db trigger that would take care of that.
Probably is, I just haven't figured it out yet. The update query
probably requires a loop which I'm too tired to write right now.

Access (well, the Jet database engine) doesn't support table triggers.
Yes, that would be the right way but...

The Update query will not need a loop. Just join the two tables on
department *name*, not DepartmentID, and update
targettable.DeptartmentID to Departments.DepartmentID.