Hi Mark,
In that case it's simplest to do it in stages. Start by linking the
spreadsheet as a temporary table, or importing it to a temporary table.
Then use an update query that joins this table and your existing price
table to update the prices in the records that already exist.
If the tables are tblPrice and tblTemp, the part number or other unique
ID for each item is ID and the price fields are both called Price, the
SQL view of the query will look like this:
UPDATE tblPrice INNER JOIN tblTemp
ON tblPrice.ID = tblTemp.ID
SET tblPrice.Price = tblTemp.Price;
Next, use an Append query to append records from the temp table that
don't have counterparts in the Price table. This is a "frustrated outer
join" (John Vinson's term, I think). It will look like this:
INSERT INTO tblPrice ( ID, Price )
SELECT tblTemp.ID, tblTemp.Price
FROM tblPrice RIGHT JOIN tblTemp
ON tblPrice.ID = tblTemp.ID
WHERE (tblPrice.ID) Is Null);
Hi John,
Thanks for your reply...
The table I have needs to be amended by the import not added to. ie:
The Price of 'A' might be £32.56 one month
However, from the excel sheet the price of 'A' might have become £41.99
I need the import to change this entry without adding a new entry.
I can't simply remove the old table and create a new table because some
items on the imported price list might have been removed from the previous
one and I still need to maintain the old information.
Hope I have been clear and hope you can help further.
The error meesage I get is cimply that access can not perform the import.
Mark
John Nurick said:
Hi Mark,
1) If the new price list supersedes the old one, you'll need to delete
the old records before importing the new. (But make a backup copy of
your database first.)
2) "the import wizard refuses to import it": just what does happen? what
error messages do you get?
3) If your table of prices has no primary key and allows duplicates,
however can your database work out which price to use?
I have a table - imported from an Excel Sheet - containing a price list.
Periodically this price list will change - ie... new items, changes of
existing prices.
I have tried to update my table by importing the new price list but the
import wizard refuses to import it.
I have checked that all field names are the same and field types. Also
checked that Duplicates are allowed and removed the primary key but to no
avail.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
