copy values from one table to another

  • Thread starter Thread starter Penstar
  • Start date Start date
P

Penstar

I have 2 tables, with the same number of records. I want to move the data
from 4 fields from table1 to matching fields in table2. The primary key on
each table is different, so I cant join the tables and do an update query.
How can I get this data across?
 
I have 2 tables, with the same number of records. I want to move the data
from 4 fields from table1 to matching fields in table2. The primary key on
each table is different, so I cant join the tables and do an update query.
How can I get this data across?
Click to expand...

With considerable difficulty, I fear! Access tables are Sets, not enumerated
sequences; unless there is data within the field that allows you to link a
record in one table with "the same" record in the other, you can't do a direct
update query.

Worse, if you're counting on the records in the two tables to have a stable,
predictable order, they don't. Access will store the records in Primary Key
order, ordinarily, but new records will be inserted wherever Access finds it
convenient, not necessarily at the end of the table.

What are the primary keys of the tables? If they're not the same, do they at
least define the same sort order between the two tables? You may be able to
write VBA code to open two recordsets and do the update that way, but as it is
you've dug yourself a pretty deep hole by storing data redundantly and
non-relationally!
 
A bit further explanation of why I have this dilemma:

I have a table containing timesheet records. I am transferring (then
deleting) selected records to a termporary table (as these are just a list of
names which will then be allocated to specific time slots once all names are
down). I am then loading a new timesheet in its place (which contains
different time slots). Then I want to copy the records (peoples names) back
from the temp table to the new timesheet.

The primary keys (timesheet_ID) are not the same on both tables as
Timesheet_ID is an autocounter in the main table.

I have got this far in working out a solution:
DoCmd.OpenQuery "AutodrawTempQuery", acNormal, acEdit
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.Close acQuery, "autodrawTempQuery", acSaveYes

DoCmd.OpenQuery "AutodrawTimesheet", acNormal, acEdit
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 3, , acMenuVer70
DoCmd.Close acQuery, "AutodrawTimesheet", acSaveYes

I feel like I am not "doing it properly" but this is the only way I can see
to do it. I think it might work.

Any thoughts?
 
A bit further explanation of why I have this dilemma:

I have a table containing timesheet records. I am transferring (then
deleting) selected records to a termporary table (as these are just a list of
names which will then be allocated to specific time slots once all names are
down). I am then loading a new timesheet in its place (which contains
different time slots). Then I want to copy the records (peoples names) back
from the temp table to the new timesheet.
Click to expand...

Well....

A timesheet should not HAVE any names in it.

The names should exist only in your Employees table; the timesheet should have
fields for the unique EmployeeID, the date/time started work, etc.

Can you join the two timesheets on any OTHER field - date and time? name
(which can be a problem as names are not unique and may be misspelled)?
The primary keys (timesheet_ID) are not the same on both tables as
Timesheet_ID is an autocounter in the main table.

I have got this far in working out a solution:
DoCmd.OpenQuery "AutodrawTempQuery", acNormal, acEdit
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.Close acQuery, "autodrawTempQuery", acSaveYes

DoCmd.OpenQuery "AutodrawTimesheet", acNormal, acEdit
DoCmd.DoMenuItem acFormBar, acEditMenu, 9, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 3, , acMenuVer70
DoCmd.Close acQuery, "AutodrawTimesheet", acSaveYes

I feel like I am not "doing it properly" but this is the only way I can see
to do it. I think it might work.
Click to expand...

Just that acFormBar code harks back to Access 2.0, back in the last century,
and that it should have been left there. Since you didn't post anything about
AutodrawTempQuery all I can presume is that you're copying and pasting from
the query into the table, overwriting records (possibly at random).

Good luck. You may need it. Back up your database first, as always when you're
doing bulk updates!
 
Back
Top