Help with Deleting Queries.

[penandpaper]

Hello,

I'm very new with MS Access and have a brief background on SQL.
I'm having problems with deleting this query:

DELETE a FROM Sheet1 a
LEFT JOIN Sheet1 AS b ON
a.Subledger = b.Subledger AND
a.[Object Account] = b.[Object Account] AND
a.[Actual Ammount] = -b.[Actual Ammount]
WHERE b.Subledger IS NOT NULL

The error say's that Microsoft Can't Represent The Join Expression in
a.[Actual Ammount] = -b.[Actual Ammount]

Any Help on this is greatly appreciated.

 

[plog]

First give us a big picture synopsis of what this database is for. Then tell us why you think you need a DELETE query.

My experience is that action queries (UPDATE, DELETE, INSERT) are often hacks used by improper table structure and/or people not knowing how databases are to work. Generally, you want to mark data as old/deleted and not actually delete the data. Why do you want it completely gone?

 

[isladogs]

Well you definitely need to remove the - sign in the line that errors
The first line is also wrong

These are shown in RED below.

[COLOR="Red"]DELETE a FROM [B][COLOR="red"]Sheet1 a[/COLOR][/B][/COLOR]
LEFT JOIN Sheet1 AS b ON 
a.Subledger = b.Subledger AND 
a.[Object Account] = b.[Object Account] AND
a.[Actual Ammount] = [B][COLOR="red"]-[/COLOR][/B]b.[Actual Ammount]
WHERE b.Subledger IS NOT NULL

Beyond that, I'm unclear of the syntax in other parts of the query
Is Sheet1 a table?
Is 'a' a table alias or a field?
Are you using two copies of the same table?

Could you show this in query design view as it may clarify what this all means

This MAY be correct (but I'm guessing the answers to my questions)
However if it's the same table used twice, its probably going to delete all records

DELETE * FROM Sheet1 AS a
LEFT JOIN Sheet1 AS b ON 
a.Subledger = b.Subledger AND 
a.[Object Account] = b.[Object Account] AND
a.[Actual Ammount] = b.[Actual Ammount]
WHERE b.Subledger IS NOT NULL

 

[penandpaper]

Well I have 2 tables in that database and the 2 tables are named Sheet1 and Sheet2. I have a query that transfers duplicates that has the same subledger, object account and actual ammount from Sheet1 to Sheet2 and delete it afterwards.

The insert is working perfectly and my problem is with the delete query.

The syntax:

a.[Actual Ammount] = - b.[Actual Ammount] is to return identical numerical value regardless if it is positive or negative.

 

[isladogs]

Hi P+P

Several comments

1. You didn't reference Sheet2 table in your query
2. The first line (shown in RED before) is completely wrong
3. You can't use "a.[Actual Ammount] = - b.[Actual Ammount]" to return identical numerical value regardless if it is positive or negative."
Access cannot join tables in that way - if you need to do that, a different approach is needed
4. I'm guessing that you imported data from Excel.
Did you assign a primary key field to tables Sheet1 & Sheet2?
If not your delete query will never work

To explain a better way of doing this, I've created a small database (attached).
Its similar to yours with 12 records in table Sheet 1
4 of those are duplicates 2 & 11, 8 & 9.
So you need to delete 2 of those e.g. 2 & 8

As far as Access is concerned +£10 and -£10 are not duplicate values.
Actually they aren't duplicate values in any sense of the word.
If you pay me £10 & I pay you -£10, I'm £20 better off

I've written a query qryAppendFirstDupes to append those to table Sheet2
I've also written a query qryDeleteFirstDuplicateValue to delete these duplicate values from Sheet1

The updated code is very simple

DELETE DISTINCTROW Sheet1.*
FROM Sheet1 INNER JOIN Sheet2 ON Sheet1.ID = Sheet2.ID;

I've not bothered with table aliases as its so simple

If you study what I've done, you should be able to use the same ideas in your database. If you need to adapt to manage the negative values, this should still help you work out how!

Finally, it would be better still to prevent the duplicates being created in the first place!

Hope this helps