swapping data from one table to another

[JString]

This is probably a stupid question, but for the life of me I can't remember
how to do this.

I have 2 tables. Table1 has a full list of names and record ID's. Table2
contains a matching list of names. What I need to do is look up the ID value
in table1 based on the name contained in Table2 and then update that value in
an ID field in Table2 so that I can remove the names from Table2.

I tried using DLookup but it's giving me too many errors to be of any use.

 

[John W. Vinson]

This is probably a stupid question, but for the life of me I can't remember
how to do this.

I have 2 tables. Table1 has a full list of names and record ID's. Table2
contains a matching list of names. What I need to do is look up the ID value
in table1 based on the name contained in Table2 and then update that value in
an ID field in Table2 so that I can remove the names from Table2.

I tried using DLookup but it's giving me too many errors to be of any use.

Is the ID the primary key of either table? If so you should be able to just
join the two tables in a Query, change it to an Update query, and update the
field in Table2 to [Table1].[fieldname].

However... it really sounds like your table design may not be optimal! Data
should be stored only once; having a name in two tables is a bit suspicious
(there should be a People table with one record per person, containing their
name and other bio information). What's the application, and how are the
tables structured and related?

 

[John Spencer]

Seems as if you need a rather simple update query.

Assumptions:
The Name field in both tables is set up identically and is only one field.
Field names are FullName and IdField

UPDATE Table1 INNER JOIN Table2
ON Table1.FullName = Table2.Fullname
SET Table2.IDField = [Table1].[IDField]

In design view (query grid)
-- Add both tables
-- Drag from the name field in table1 to name field in table2
-- Add Table2.ID field to the grid
-- SELECT Query: Update from the menu
-- In UPDATE TO under Table2.ID enter
[Table1].[IDField]
-- Run the query.

Note this will not match records if the values are not identical in the
name field.

Jon Spencer will not match John Spencer
John P Spencer will not match John Spencer


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[JString]

You're right... the tables are not optimal as this is a conversion job. Oh
how I wish I could start from scratch! I did end up using an update query
along the lines that you described but, again I can't remember exactly what I
did since I must have created two dozen or so queries for this project.

Organizing the tables has been very tricky. The database basically stores
case file information, and there are very few unique identifiers like case
#'s, etc. I ended up splitting the main table into several to produce a
composite key for each file based on Plaintiffs, Defendants and property
addresses. There's still a chance that a case file with a pre-existing key
could be blocked from being created, but I think that will be very unlikely.

This is probably a stupid question, but for the life of me I can't remember
how to do this.

I have 2 tables. Table1 has a full list of names and record ID's. Table2
contains a matching list of names. What I need to do is look up the ID value
in table1 based on the name contained in Table2 and then update that value in
an ID field in Table2 so that I can remove the names from Table2.

I tried using DLookup but it's giving me too many errors to be of any use.

Is the ID the primary key of either table? If so you should be able to just
join the two tables in a Query, change it to an Update query, and update the
field in Table2 to [Table1].[fieldname].

However... it really sounds like your table design may not be optimal! Data
should be stored only once; having a name in two tables is a bit suspicious
(there should be a People table with one record per person, containing their
name and other bio information). What's the application, and how are the
tables structured and related?