Delete Results from Append Query

[schweer]

Sorry, I really couldn't think of a solid title for this. I'll try and explain and provide some code as well.

I've got a series of tables, one of which is linked to a Mobile Device. I have a query (herein called Query A) that queries a masterfile, named MasterFileEdit, based on selections the user makes on the front end. Query A then returns the results, sorted by section number. Query B is set up to query based on the same selections, but instead DELETE all of those records from the masterfile. Doing this makes sure that no duplicate work is performed.

Here's the code for Query A.
INSERT INTO [TABLE A1] ( OID, SystemLoc, SystemPN, Location, Part_Number, Qty, Date_n_Time, NL, RCHKL, RCHKP, oddeven, Auditor, upperlower, area, aisle, [section] )
SELECT TOP 100 MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]))
ORDER BY MasterFileEdit.section;

Everything after the 'FROM MasterFileEdit' after is the user selections that come from a form.

I need help writing the DELETE query. Here's what I've got thus far:

DELETE MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]));

I can't get the DELETE to sort. The query runs, but doesn't select the records it should. In fact, it doesn't select any of the correct records.

Thoughts: does the TOP 100 modifier work with Deletes?
Is this query even possible?

Thanks in Advance for your Help.

Matt

 

[ThreeCrow]

From your post you it seems as though you just want to delete duplicate records. Is that the case.

 

[schweer]

They're not necessarily duplicate records, per se. They're records appended to another table from the masterfile. After appending them, I'd like to delete the selected records from the masterfile so I can make sure they are not selected in the future.

 

[ThreeCrow]

If you had to choose manual which records to delete how would you choose them.

I understand that you want to automate this.

I just trying to get a better feel for what we are doing.

 

[schweer]

The choices are made through 4 drop-down comboboxes on a form.
The query selects records based on these 4 choices.

If the deletion had to be manual, I would sort each by each of the drop downs in sequence (Oddeven, Upperlower, Area, Aisle). Then I would sort those results by Section Number, beginning with the lowest value. Then, I'd choose 100 records and delete them from the master.

Thanks for your time in this. Hopefully, we can make this work.

-Matt

 

[ThreeCrow]

We want to filter these records not sort, there is no need to sort something your going to Delete.

With what you choose from these comboboxes what would make these records unigue.(Differant than the the record you want save.

 

[schweer]

The primary key would be the SystemLoc.

I sorted the results of the query so the sections would be in order. The masterfile itself is NOT sorted by Section number but rather by SystemLoc.

There are many sections in many aisles.

 

[ThreeCrow]

The primary key would be the SystemLoc.

I sorted the results of the query so the sections would be in order. The masterfile itself is NOT sorted by Section number but rather by SystemLoc.

There are many sections in many aisles.

What is the SystumLoc

 

[ThreeCrow]

I'm reading to fast

The way to Filter these Records that you want to Delete must be unique.
Something that we can Filter on, to tell the query these Records and no others are to be Deleted.

 

[schweer]

I'm thinking maybe throw them in a temporary table and then somehow use EXISTS or IN after the delete statement. Something like:

Delete * from MasterFileEdit
where SystemLoc IN (Select * from TempTable1);

Is that a better way? Would that make them Unique?

 

[ThreeCrow]

What would you use to deside which records would go to the Temp Table

 

[schweer]

The exact same criteria that I'd use to select the records from the master and append them to the other table.

Instead of appending records to TABLE A1, I'd append them to the TempTable, then simply copy the records over to TABLE A1. This way, the records are unique from any other record in the master as they exists in BOTH tables.

What about a join.......

 

[ThreeCrow]

Using the comboboxes what would you choose to make these records the ones' that you would Delete

 

[schweer]

I can get the delete query to select based on Area, Aisle, Upperlower, Oddeven. That's not the problem.

The problem is getting the correct section numbers, which is why a TempTable might just do the trick but I just can't quite get it to work out.

 

[ThreeCrow]

I take it that there is more then one section number

 

[schweer]

Yes, as I mentioned earlier.

 

[ThreeCrow]

Be so kind as to understand that I can not see your database. And that I am trying help you with the problem that your are having.

What I would do say to do just as a band aid job is to add a field to your Table make Yes/No field.

Also add this field to your Form.

If you manualy choose in this Control on the Form the Records that you want to Delete. This will make these Records unique, base your Delete Query on this Query Field, with criteria as -1. Those Records that you want will be Deleted will be. Because they differant then the Records that you want to save. The -1 makes them they way.

Wish I could spend more time, But I've to goto work.
I will work on this and get back to you if no other solution come by.

 

[schweer]

I appreciate your help. I'll give that a try.

 

[schweer]

In ???

Can a DELETE query utilize the IN keyword?

I think the way to get this done is by using IN. If it works, it will simply delete all records from the Masterfile that are IN the TempTable. Since the TempTable is a linking table to a hand-held scanner, I believe that would work out. Or would EXISTS do the same thing? I guess I don't understand the difference between the two.

Any ideas as to how to make that work?

 

[ScottGem]

Can a DELETE query utilize the IN keyword?

I think the way to get this done is by using IN. If it works, it will simply delete all records from the Masterfile that are IN the TempTable. Since the TempTable is a linking table to a hand-held scanner, I believe that would work out. Or would EXISTS do the same thing? I guess I don't understand the difference between the two.

Any ideas as to how to make that work?

Yes it can and that's exactly how you would do it.