Gary,
The best way requires a four step process.
Step One is to import your spreadsheet with the new data into Access. Lets
say this has 75 records and the old one has 50. If the number of records has
not changed, skip to step four.
Step Two is to do a union query on the order number field. Basically, you
take all the Order numbers from both tables, Old and New, put them into a
third table called tbl All Info, which should have at least 75 records (if
all are duplicates) and at most 125 records (if no duplicates). Let's say we
get 110 records, 15 were dups.
Step Three After the union query is done you do a make table query using the
union query and the old data table as sources. Include all the fields from
the old data except the order# and take the orders number field from the
union. You set the join to option two, all records from the union query and
only those from the old table that match, (right click on line between Order#
and select properties). This should get you all the order numbers with the
data from the old table. Using the numbers, you should end up with the same
110 records, but 50 of them will have information in them from the old table.
Step Four You need to do an update query using the All Info table that you
just made and the new data. Create a query with the All and New tables.
Again, you set the join to option 2, it should include all records from All
Info and only those from New that match. Now you need each field in the
query, just double click on every field, don't use the asterisk. Click on the
black Exclamation with Star icon and select Update Query. This will give you
a line for updating. So under each All Info field, in the update line, you
write [tbl.new].[VIN] to update the All Info VIN with whatever data is in the
New VIN. Do this for every field. Be advised, this will fill in the new
information ** AND ** write over any information already there. However, If
there is nothing new to overwrite, the old information remains. Again using
the numbers, the 110 records remain, the 60 empty records will now have data,
and anything in the old 50 will be written over by information in the new 60
if and only if the new 60 has something to enter, otherwise the old stuff
remains. I think this is what you want, the new information overwritting the
old and a table with all the order numbers.
Step Two, the Union Query, found under the Query Menu, SQL-Specific. You
have to write this out, using the graphical interface is not possible. Here
are the basic commands, modify it for your stuff.
SELECT [tbl New].[Order#]
FROM [tbl New]
UNION SELECT [tbl Old].[Order#]
FROM [tbl Old]
ORDER BY [tbl New].[Order#];
Sorry this is so long. You might be able to skip a step or two if the order
numbers are the same length and identical. It that is the case, that is, some
updated a spreadsheet with new information yet the total number of records
has not changed, then just do steps one and four. Get the new information
into Access and do an update query matching the old fields to the new ones.
Hope this helps.
--
Michael Conroy
Stamford, CT
GaryS said:
Hello - I have 1 table with many fields, 3 of which are [VIN], [ORDER #], and
[SHIP TO]. There is currently data in [ORDER #] and [SHIP TO] but not in
[VIN]. I have downloaded an Excel spreadsheet with updated data and have
used it to create a new table with the 3 same field names but with updated
data. I want to update the orignal table [VIN] and [SHIP TO] fields with
the updated data from the new table. Common field with data in both tables
would be [ORDER #]. Any help would be greatly appreciated.
