473,287 Members | 1,964 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Importing and Updating Access XP

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

Nov 13 '05 #1
2 1335
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

Nov 13 '05 #2
Your queries worked perfectly!!! Thanks for the help!! I appreciate it!

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Oleg | last post by:
Hi, Not really a PHP specific question, but I am using PHP on Linux box so it's kinda relevant. I'm developing a site which will periodically update inventory via a large batch file. I'm...
3
by: Mark Line | last post by:
Hello! I'm a python n00b! I've been writing in c++ for a few years so programming's not new to me, just python that I don't know the syntax!
11
by: Grim Reaper | last post by:
I am importing a .csv file into Access that has 37 fields. My problem is that sometimes the last field only has data at the end of the column (it looks like when you import a file into Access, for...
2
by: enrio | last post by:
I need to process the source code associated with the forms outside access, and import the changes back to Access. I find that I can export the source code of a form, but when I subsequently...
1
by: Brian Graham | last post by:
There's an old Access app here that I've been updating. The original "programmer" set things up poorly and every month the end-user has been loading multiple spreadsheets by creating new tables and...
1
by: winzy | last post by:
I have a table in Access which needs updating from an Excel file. Instead of importing the whole Excel like a new table in Acess, how do I do a partial import/update?? For example, Access table...
0
by: Mike Collins | last post by:
I am importing a XML file and have not been having the best of luck in doing this, but I do have the following solution below. I will not be importing more than 2000 records at a time, but will be...
2
by: Mike Collins | last post by:
I am importing a XML file and have not been having the best of luck in doing this, but I do have the following solution below. I will not be importing more than 2000 records at a time, but will be...
4
by: GastonFranzini | last post by:
I export some data from access to excel, then I update the data by importing it. The way I update the records is relating them with a unique ID, Is there a way to add a new record ? One without an...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.