Update Table for Obsolescence

S

Steve

I am missing something or have a blind spot about how to do this

I have a table (TblAll Products) with all my products in it including
a yes/no field to determine if a product is obsolete or not.

I have used this years list of all existing products
(TblCurrentProducts) to create a query (qryObsoleteProducts) showing
any non matched items ie which are in TblAllProducts but not in
TblCurrentProducts and are therefore obsolete.

I tried to create an Update query from qryObsoleteProducts to set the
yes/no flag in TblAllProducts to Yes but am getting an error:
Operation must use an updatable query.

Where I am going wrong
TIA
Steve
 
J

John W. Vinson

Where I am going wrong
Click to expand...

No way to tell unless you tell us where you're going... please post the SQL of
the query. Also indicate the Primary Key of each table (if that's not part of
the join it may be a cause of the non-updatability).

John W. Vinson [MVP]
 
T

Tom van Stiphout

On Fri, 28 Dec 2007 17:29:26 -0800 (PST), Steve

What John said, but you most likely also have a database design
problem. In most cases it would be MUCH better to have tblProducts
with a Status field indicating Active or Obsolete, rather than two
tables.
Relational databases are not souped-up spreadsheets.

-Tom.
 
K

Keith B Wilkinson

Hi Steve

I assume that you are using "Find unmatched query Wizard"
Try running the (qryObsoleteProducts) as a "Make-Table Query"
This should create a new table of obsolete products.

Have Fun
DeltaTech
 
S

Steve

On Fri, 28 Dec 2007 17:29:26 -0800 (PST), Steve


What John said, but you most likely also have a database design
problem. In most cases it would be MUCH better to have tblProducts
with a Status field indicating Active or Obsolete, rather than two
tables.
Relational databases are not souped-up spreadsheets.

-Tom.








- Show quoted text -
Click to expand...

Thats exactly what I am trying to achieve. I have no control over the
new products and am trying to use the downloaded file contaiing these
to mark as obsolete the items in the main table that arent produced
any more
 
J

John W. Vinson

I am missing something or have a blind spot about how to do this

I have a table (TblAll Products) with all my products in it including
a yes/no field to determine if a product is obsolete or not.

I have used this years list of all existing products
(TblCurrentProducts) to create a query (qryObsoleteProducts) showing
any non matched items ie which are in TblAllProducts but not in
TblCurrentProducts and are therefore obsolete.

I tried to create an Update query from qryObsoleteProducts to set the
yes/no flag in TblAllProducts to Yes but am getting an error:
Operation must use an updatable query.

Where I am going wrong
TIA
Steve
Click to expand...

UPDATE tblAllProducts LEFT JOIN tblCurrentProducts
ON tblCurrentProducts.ProductID = tblAllProducts.ProductID
SET Obsolete = True
WHERE tblCurrentProducts.ProductID IS NULL;

Works only if there is a unique Index (such as a Primary Key) on ProductID in
tblAllProducts.

John W. Vinson [MVP]
 
N

noreply@noreply

On additional occasions, parties in a transactions may try to modify their declare on line which do not personage either have to reveal the state of the ending.Both state contracts may also digest gag orders to foreclose highly set employees from disclosing aoc gold[/url:nwqns9dm] swop secrets or apart clubby publication, decide for abundant aliveness after the (http://www.ugamegold.com/age-of-conan-gold/:nwqns9dm)ement has ceased.
 
Top