Append/Delete/Update Query

[MikeinPhoenix]

Good morning anyone. I want to update a field on an existing table without
losing any of my record data. When I created an append query to my table, it
created duplicates of the original. Can I/should I create a delete query to
remove the old records? If so, how? Or is it better to use an update query to
update the one column? If so, how do I link it to the table? Thanks a million.

 

[Jeff Boyce]

I'm not quite clear on what you are trying to do...

If you update a field in a record, you'd "lose" that field's previous
contents, by definition of "update".

If you delete the old record, you lose the record's previous contents.

If you only want to make a change to a single field (or a few) in a record,
that sounds like an update.

I don't understand what you mean by "how do I link it to the table?" Link
what?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

Mike:

An update query is what's needed here by the sound of it. You can change
the value of a single column (field) or multiple columns simultaneously.
Lets say you have a table Products which includes columns UnitPrice,
DateEffective and Category and you want to increase the price of all products
in the category Widget by 10 percent, setting the DateEffective column to
today's date, leaving all others at their present price and date. In query
design view you'd do this:

1. Add the UnitPrice, DateEfective and Category columns to the query grid.

2. From the Querty menu select Update Query.

3. In the criteria row of the Category column enter:

"Widget".

4. In the Update To row of the UnitPrice Column enter:

[UnitPrice] * 1.1

5. In the Update To row of the DateEffective column enter:

Date()

In SQL the query would look like this:

UPDATE Products
SET UnitPrice = UnitPrice * 1.1,
DateEffective = DATE()
WHERE Category = "Widget";

Ken Sheridan
Stafford, England

 

[MikeinPhoenix]

Jeff - I do want to update and replace the previous data in that field. I
think the update query is the way to go. The only reason i mentioned the
append and delete, is because logically i thought that I could append the
table with the data I needed to change, and then delete the old records.
Sorry I made a mess of the question.

 

[MikeinPhoenix]

Thank you very much... it worked like a charm. Have a great day!
--
MikeinPhoenix

Mike:

An update query is what's needed here by the sound of it. You can change
the value of a single column (field) or multiple columns simultaneously.
Lets say you have a table Products which includes columns UnitPrice,
DateEffective and Category and you want to increase the price of all products
in the category Widget by 10 percent, setting the DateEffective column to
today's date, leaving all others at their present price and date. In query
design view you'd do this:

1. Add the UnitPrice, DateEfective and Category columns to the query grid.

2. From the Querty menu select Update Query.

3. In the criteria row of the Category column enter:

"Widget".

4. In the Update To row of the UnitPrice Column enter:

[UnitPrice] * 1.1

5. In the Update To row of the DateEffective column enter:

Date()

In SQL the query would look like this:

UPDATE Products
SET UnitPrice = UnitPrice * 1.1,
DateEffective = DATE()
WHERE Category = "Widget";

Ken Sheridan
Stafford, England

Good morning anyone. I want to update a field on an existing table without
losing any of my record data. When I created an append query to my table, it
created duplicates of the original. Can I/should I create a delete query to
remove the old records? If so, how? Or is it better to use an update query to
update the one column? If so, how do I link it to the table? Thanks a million.

 

[Ken Sheridan]

Mike:

As it happens you were not far from expressing a fundamental theoretical
truth of the relational model. Here's a quote from Chris Date's
'Introduction to Database Systems':


'In order to be able to "update tuples" we would need some notion of a tuple
variable or "tuplevar" – a notion that is not part of the relational model at
all! Thus, what we really mean when we talk about "updating tuple t to t',""
for example, is that we are replacing the tuple t (the tuple value t that is)
by another tuple t' (which is again a tuple value). Analogous remarks apply
to phrases such as "updating attribute A" (in some tuple).

Date goes on to say:

'In this book, we will continue to talk in terms of "updating tuples" or
"updating attributes of tuples" – the practice is convenient – but it must be
understood that such usage is only shorthand, and rather sloppy shorthand at
that'

C J Date, 'Introduction to Database Systems', Seventh Edition, 2000, pp133 sq.

In then language of the relational model a tuple is roughly equivalent to a
row in a table, and an attribute to a column.

So conceptually what you said was not that far removed from something said
by one of the greatest of the great and the good of the database world. So,
not such a mess after all. In the reality of a database management system
like Access of course the perceived operation is one of changing a value at a
column position in a row in a table.

Ken Sheridan
Stafford, England