Delete query but want to keep 2 fields

  • Thread starter Thread starter Michelle.Watson
  • Start date Start date
M

Michelle.Watson

My data base is for keeping track of what is in our
freezers. Each cassette has four slots. In my table, the
these 2 fields (cassette and slot) are primary keys that
allows us to see which are empty or to find information.
I would like to "append" the information in the record to
a "dispensed" table and then "delete" the information
fields from the "freezer" table (currently done through
a "dispensed query"). I can append the information to the
Dispensed table (and keep my primary fields) but when I do
a Delete Query, it deletes the whole record but I want to
keep my fields - "Cassette and slot". Is there an easier
way? Is there a way to keep these 2 fields constant? If
not, my staff have to go in and delete the information in
the other fields manually.

Thanks

Michelle
 
It sounds like you need an Update query, not a delete query. When an item
is removed from a slot in your freezer table, set the item columns to NULL.

UPDATE Freezers
SET ItemField = Null
WHERE Cassette = 5 And Slot = 2;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
I can append the information to the
Dispensed table (and keep my primary fields) but when I do
a Delete Query, it deletes the whole record but I want to
keep my fields - "Cassette and slot". Is there an easier
way? Is there a way to keep these 2 fields constant?
Click to expand...

Just run an Update query rather than a Delete query; update all fields
except Cassette and Slot to NULL.
 
i'm kind of wondering about your table design, but...
instead of using a Delete query, how about using an Update query? write the
query as an ordinary Select query, to pull the records you want to change.
then change the query to Update, select the fields where you want the data
deleted, and set the "Update to:" line to Null, for each field.
be sure you try this on a COPY of your db, first.

hth
 
Thank you so much. It worked!! Great job!
-----Original Message-----


Just run an Update query rather than a Delete query; update all fields
except Cassette and Slot to NULL.


.
Click to expand...
 
Thanks a lot for the info. This works like a
charm....appreciate your time. (also sweet talked a "IT"
person to help) and he helped me do a macro with all my
queries --- with help from the email. Michelle
 
Back
Top