Transferring data from a Temp table to permanent tables

[seestevecode]

Hi. I am exporting data from Excel into Access temp tables and then trying to write update/append queries to transfer the data to permanent tables.

I have this scenario:
STAFF table
StaffID [Autonumber] [PK]
StaffCode

PRODUCTIVITY table
ProductivityID [Autonumber] [PK]
Date
StaffID [FK to STAFF table]
WidgetsCranked

TEMP table [no PK]
Date
StaffCode
WidgetsCranked

For example:
STAFF table
ID Code
Auto ab123
Auto cd456

PRODUCTIVITY table
Date Code Widgets
1/4 ab123 10

TEMP table
Date Code Widgets
1/4 ab123 20
1/4 cd456 20
1/4 ef789 20

I would want the first record in the TEMP table to UPDATE the record in the PRODUCTIVITY table; the second record in TEMP to APPEND to the PRODUCTIVITY table; and something to happen for the third record in TEMP to prompt for the user to add details for ef789 so a record can be created in the STAFF table.

I think I have the first two bits sorted but don't know how to do the third, and whenever I have records in my TEMP table that don't have existing StaffCodes in the STAFF table, they aren't getting appended.

Thanks for any help you can offer.

 

[stopher]

Before running the import routine, I suggest running a test query that simply lists all the staff codes that are in the temp table but not in the STAFF table.

I would probably prevent running the import routine until all required data is input.

To try and create missing data as the data is being imported is fraught I think.

hth
Chris

 

[seestevecode]

I had thought of that, but was hoping there was something a little less manual. I'm grateful for your opinion, as at least I can now continue along that route knowing it's potentially the best I can do.

 

[stopher]

I don't really see it as less manual. The user has to enter the Staff info at some point.

I was thinking of maybe a button (Import Data) on form. On clicking the button, the code behind the button checks if there is any missing data (maybe a DCount on the query we've already mentioned). If there is missing data then a new form pops up listing the new staff to be entered so that the user simply enters the data. And maybe a button to Continue or Cancel. Continue obviously checks again and allows the data to be loaded if all the missing data is now present.

So the user only sees a nice interface.

Does that make any sense?

Chris

 

[stopher]

What Staff details are there to add exactly? If you just want the StaffCode added then yes that could be automatic.

CHris