Update and Append

[Mark Williams]

I have a spreadsheet that contains a price list that I must import into my
pricelist table each month.

The price list contains a list of codes, items and various other data
including the price of the items.

Each month an item might have its prices adjusted and/or there might be new
items added etc.. how do I update and append the new price lists into my
table so the prices are adjusted.

I am quite a new comer to this and have had advice on this but I couldn't
follow eactly what to do. If someone can explain VERY basically a step by
step guide for me it would be much appreciated.

Thanks

Mark

 

[Michel Walsh]

Hi,

In Jet:


UPDATE inventory As old RIGHT JOIN updatingData As new
ON old.ItemID=new.ItemID
SET old.ItemID=new.ItemID,
old.UnitPrice = new.UnitPrice,
old.Description = new.Description


where I used inventory and updatingData as tables.

Hoping it may help,
Vanderghast, Access MVP

 

[Mark Williams]

Sorry Michael but I think I said I was a newcomer... what is Jet and where
do I type this code ?

Mark

 

[Michel Walsh]

Hi,


Jet is the database engine you use if you use a dot-mdb. By opposition, you
have MS SQL Server as database engine if you use a dot-adp (Access 2000 or
later has the choice of either db).

You type the statement in the SQL view of a query, OR you can graphically
built it:

Bring the two tables.
Make a join on ItemID between the two tables, edit the join (right click
on the link bar) and select the option that keep all the records from the
UPDATING table.

Change the query SELECT type to a UPDATE query type (through the menu or
the toolbar). A new line, UpdateTo appear in the grid.

Bring the inventory fields (the old table to be updated) in the grid.
Under each of them, type, in their UpdateTo line,
[updatingTableNameHere].[realFieldNameHere] and do NOT FORGET to update
ItemID too.


Doing so, you have your query that update existing records, AND append
new ones, in one query.


You can't do it this way with MS SQL Server. With that db, you have to
make two queries, one for the update and one for the append.



Hoping it may help,
Vanderghast, Access MVP

 

[Mark Williams]

Michael,

You are a treasure - so far from the testing I have performed that works a
treat.

Thanks

Mark

Hi,


Jet is the database engine you use if you use a dot-mdb. By opposition, you
have MS SQL Server as database engine if you use a dot-adp (Access 2000 or
later has the choice of either db).

You type the statement in the SQL view of a query, OR you can graphically
built it:

Bring the two tables.
Make a join on ItemID between the two tables, edit the join (right click
on the link bar) and select the option that keep all the records from the
UPDATING table.

Change the query SELECT type to a UPDATE query type (through the menu or
the toolbar). A new line, UpdateTo appear in the grid.

Bring the inventory fields (the old table to be updated) in the grid.
Under each of them, type, in their UpdateTo line,
[updatingTableNameHere].[realFieldNameHere] and do NOT FORGET to update
ItemID too.


Doing so, you have your query that update existing records, AND append
new ones, in one query.


You can't do it this way with MS SQL Server. With that db, you have to
make two queries, one for the update and one for the append.



Hoping it may help,
Vanderghast, Access MVP

Sorry Michael but I think I said I was a newcomer... what is Jet and where
do I type this code ?

Mark

into

into

my step
by