Importing and Updating Access XP

[bsmith1111]

Before I start- Office Xp and only basic knowledge of Access. Here's
what I'm trying to do:
I've got an Excel file with four columns:
Name Description Place Misc

I import this file to a Temporary table in my Access database. (I have
queries to delete everything in this temporary table and puts the new
info here- everything works fine to this point)
I also have a Permanent table which has these 5 fields:
Name Description Place Misc Price

I need to update the Permanent table with records from the Temporary
table. Here's an example of what I'm trying to do:

Temporary Table (from Excel file)
Name Description Place Misc
Bob new new new
Paul new new new
Jill new new new

Permanent Table
Name Description Place Misc Price
Bob old old old 1
Jill old old old 1
Ted old old old 1
I need to replace the old from the Temporary with the new from the
Permanent.

Not all the Names in the Temporary table exist in the Permanent table
(like Paul as seen above). I would need to add Paul and any others not
in the permanent table.

Also not everything in the Temporary table is in the permanent table
(like Ted as seen above). I would need to keep Ted in the Permanent
table while still adding others from the Temporary table.

Final Permanent Table after editing
Name Description Place Misc Price
Bob new new new 1
Paul new new new
Jill new new new 1
Ted old old old 1
I've been working on this problem for days now- tried SQL, relationship
editing, update queries, etc., but I cannot get anything to work (I've
tried what I could find from the net, but I really don't know much
about these techniques). I halfway tempted just to do these things by
hand, but it would take way, way too much time in the long run.
Anyways, thanks for your time.

Brett

[MacDermott]

This is a two-step process.
I'd suggest that first you write a query to update your existing records:
UPDATE Temporary INNER JOIN Permanent ON
Temporary.[Name]=Permanent.[Name] SET
Permanent.Description=Temporary.Description,
Permanent.Place=Temporary.Place, Permanent.Misc=Temporary.Misc;

Then write a query to add the new records:
INSERT INTO Permanent ( [Name], Description, Place, Misc) SELECT [Name],
Description, Place, Misc FROM Temporary WHERE Temporary.[Name] NOT IN
(SELECT Permanent.[Name] FROM Permanent)

HTH

<bs********@isp.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Before I start- Office Xp and only basic knowledge of Access. Here's
what I'm trying to do:
I've got an Excel file with four columns:
Name Description Place Misc

I import this file to a Temporary table in my Access database. (I have
queries to delete everything in this temporary table and puts the new
info here- everything works fine to this point)
I also have a Permanent table which has these 5 fields:
Name Description Place Misc Price

I need to update the Permanent table with records from the Temporary
table. Here's an example of what I'm trying to do:

Temporary Table (from Excel file)
Name Description Place Misc
Bob new new new
Paul new new new
Jill new new new

Permanent Table
Name Description Place Misc Price
Bob old old old 1
Jill old old old 1
Ted old old old 1
I need to replace the old from the Temporary with the new from the
Permanent.

Not all the Names in the Temporary table exist in the Permanent table
(like Paul as seen above). I would need to add Paul and any others not
in the permanent table.

Also not everything in the Temporary table is in the permanent table
(like Ted as seen above). I would need to keep Ted in the Permanent
table while still adding others from the Temporary table.

Final Permanent Table after editing
Name Description Place Misc Price
Bob new new new 1
Paul new new new
Jill new new new 1
Ted old old old 1
I've been working on this problem for days now- tried SQL, relationship
editing, update queries, etc., but I cannot get anything to work (I've
tried what I could find from the net, but I really don't know much
about these techniques). I halfway tempted just to do these things by
hand, but it would take way, way too much time in the long run.
Anyways, thanks for your time.

Brett

[B Smith]

Your queries worked perfectly!!! Thanks for the help!! I appreciate it!