deletion query (1 Viewer)

[ethan.geerdes]

I have entries in a column named flag that I am using as a "flag" ie 0, 1 or 2. I need to know how to do a query to delete the one's.

Any help would be greatly appreciated.

 

[jdraw]

A DELETE query will delete records. Is that what you want?

Or do you only want to change the values of Flag that are currently 1, to another value. If so, you may need an Update query.

Just a cautionary note since I may have not understood your intent.

Often with database, records are not physically deleted. Records have a field valued (true/False) to indicate whether the record is active or logically deleted. Logically deleted records are ignored in many queries, but remain in the database for audit/historical purposes.

 

[Galaxiom]

DELETE *
FROM tablename
WHERE flag = 1

 

[ethan.geerdes]

does that code delete the whole record?

 

[Galaxiom]

does that code delete the whole record?

Yes. That is what Delete queries always do.

If you are wanting to change the value in a field then you need an Update query. Assuming you want a Null instead of the flag = 1.

UPDATE tablename
SET flag = Null
WHERE flag = 1

 

[ethan.geerdes]

Ok, so what if I wanted to delete an individual record with the flag of, but not all of them. How can I make it so that a single entry is cleared and not all of them?

 

[ethan.geerdes]

So this would be for moving a record, I have the records coming up properly on my forms but as soon as I click submit to right the other table, i need it to delete from the table it pulled the information from. I figured that a flag would solve it but I am not so sure if that is the case anymore.

 

[Galaxiom]

So this would be for moving a record, I have the records coming up properly on my forms but as soon as I click submit to right the other table, i need it to delete from the table it pulled the information from. I figured that a flag would solve it but I am not so sure if that is the case anymore.

Moving records from one table to another indicates incorrect data design. There should only be one table for this.

Whatever attribute that you are currently recording by which table the record is in should be indicated by another field in the single table.

 

[ethan.geerdes]

I realize that I have an incorrect design but I really don't want to go back to square one. Really though, how important is it that I not move records?

 

[Galaxiom]

I realize that I have an incorrect design but I really don't want to go back to square one. Really though, how important is it that I not move records?

Incorrect design usually leads to having to work around problem after problem. Fixing it rarely means going back as far as you expect.

Hard to be exact without knowing your database but it might involve something like this.

Add the extra field to one of the tables to indicate what had been indicated by the table it was in.

Update the field for those records.

Append the records from the other table and set the new field for them.

Create queries to select the records based on the value in the new field.

Use these queries as the RecordSources of your forms.

 

[ethan.geerdes]

Thank you for explaining it to me. I have gone through and re-structured my database to one table and then put a status button in. I want to run an update query so that I can change the status from "entered" to "assigned" but I can't seem to get the syntax right for running the update query.

 DoCmd.OpenQuery (Assign_qry,[View As AcView = acViewNormal],[DataMode As AcOpenDataMode = acEdit])

Also, if I run this it should change everything to assigned. I need it to change a single entry. Is there a way to just do it for one entry instead of multiple?

 

[Galaxiom]

Updating a single record with a query requires a WHERE clause to uniquely define that record. This is normally uses the primary key field.

However I get the impression you want to simply change the value on the current record in the form.

Me.fieldname = "Assigned"

 

[ethan.geerdes]

ok. Thank you. So what would the correct syntax be to run the query?

 

[jdraw]

Galaxiom gave you the sample syntax in post #5.

Here is a link to related info for Update query.

Note: The cautionary info for Update queries at the link page.
UPDATE Warning!!!

 

[ethan.geerdes]

I think that worked. thank you both very much for how to make this query work.

 

[jdraw]

Galaxiom and I re happy to help.
You should be able to confirm that your query worked.

A Select query looking for the specific record should confirm your work.

 

[ethan.geerdes]

DoCmd.GoToRecord , , acNewRec

Update Advisory_Messages
Set Column7 = "Assigned"
Where Column2 = Me.Combo14

So the code above is what i have in there right now and i am getting an error that states "Compile Error. Variable not Defined" and has the table name highlighted. Is it a Syntax Error?

 

[jdraw]

Ok. Just summarize what you have done so far.

The update query, from my view would be a stand alone operation, but I may have missed or forgotten some of your background.

You would not need the
DoCmd.GoToRecord , , acNewRec


If you are clicking a button or some other event, then your update query could be triggered by the event.

It isn't common to name fields in a table as column2 or column7 etc.. Columns in tables (fields) would normally have names such as Status, or EmployeeFirstName etc.

Your reference to me.combo14 indicates a control on a form, and that form must be open when the query is executed.

Have you backed up your database? Update queries can be unforgiving!!!!

 

[ethan.geerdes]

Hey, thanks for replying. my internet is stupid slow right now. So I have a form, I want to click a button, and you're right, I should have taken that line out because I am not creating a new value because it would have already been entered. I also have my columns named appropriately now and compiled it again but I got a "COMPILE ERROR: Variable Not Defined" with the table Name highlighted.

 

[jdraw]

Please show the code involved.