Update Query?

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi Folks - My database has a students table. Every quarter, I receive a file
that contains updated info for the students table. I manually import the
update file, then manually tweak it so it is almost identical to the
students table structure.

Now, I need a way to update the students table with the new info from the
updated/tweaked students table. I thought an update query would work, but I
am having problems setting it up. Any deas or alternatives? Thanks.
 
Providing the tables were the same, in the update query you would relate the
Primary key of [TableA] the the Foreign Key of [TableB]. The Data in these
fields should match each other exactly.

Is there a unique record in both tables that can be linked in the query.
This could be a StudentID or equivelant. If so create a relationship between
these in your query then populate the relevant query field.

Another point to note is the "Data Type" of the related fields from both
tables should be the same.

When you have created the query, check the code and see does it look
something like this:

UPDATE Table1 INNER JOIN Table2 ON Table1.StudentID = Table2.StudentID SET
Table2.Field1 = Table1.Field1, Table2.Field2 = Table1.Field2, Table2.Field3
= Table1.Field3
WHERE (((Table2.Field1)<>[Table1].[Field1])) OR
(((Table2.Field2)<>[Table1].[Field2])) OR
(((Table2.Field3)<>[Table1].[Field3]));

This assumes the fields names in both tables are the same but will work just
as well if they are not.

hth

Paul
 
Hi,


If you allow me to compare students to inventory:


UPDATE oldstuff RIGHT JOIN newStuff
ON oldStuff.id = newStuff.id
SET oldStuff.id=newStuff.id,
oldStuff.UnitPrice = newStuff.UnitPrice,
...


would update existing "id" and append new ones.

That only works in Jet.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top