PLEASE HELP

[Santi]

I’m want to be able to update and include new information in a Master table
using a secondary table that will be fed on a monthly basis by new or
existing information. Want I want is for the master table to update records,
take in new information and erase any old information. For example:

If the Master_table has item#: 12345 with a unit price of $ 90.00 (regular
price) and Item# 12345 with a price of 80.00 for buying 10cs and if the
NYS_table has the same Item# 12345 but with a price of 105.00 (regular
price), Item# 12345 $ 100.00 for buying 5cs and Item# 12345 $95.00 for
buying
10cs then I want the master table to be updated with this information by
replacing the original data.

Below is the current query I’m using


UPDATE Master
RIGHT JOIN NYS
ON Master.[Item#] =NYS.[ Item]
SET Master.[DESCRIPTION] = NYS.[Description],
Master.[UNIT PRICE] = NYS.[PRICE],
Master.[COMMENTS] = NYS.[Disc $]

 

[S.Clark]

My guess would be that you would have to:
1. Check for the existance of Records in master
2. Update those that match from NYS to master
3. Append the new records from NYS to master that didn't have matches

or simply

1. Delete any Master records (based on item#)
2. Append all the NYS records.

So, use two queries. Use a macro or VBA to automate the execution of them.

 

[Gina Whipp]

Santi,

Right off the bat you have a contradiction in what you want... "...update
and include new information..." If you just want to UPDATE the changed
information then you need the below...

UPDATE Master RIGHT JOIN NYS ON Master.[Item#] = NYS.Item SET
Master.Description = NYS.[Disc $], Master.[UNIT PRICE] = NYS.[PRICE],
Master.Comments = NYS.[COMMENTS]
WHERE (([Master].[Description]<>[NYS].[Disc $])) OR (([Master].[UNIT
PRICE]<>[NYS].[Price])) OR (([Master].[Comments]<>[NYS].[Comments]));

BUT if you want to "...include the new information..." then you need to
write an APPEND query.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm