Delete Query (help please)

[dlingley]

I'm writing a simple delete query to delete records from one table if they match records on the the other table.

Purpose of the query: remove checks from the outstanding check list that have cleared the bank

The tables are "tblOutstanding" and "tblBankStatement" & each table has only two fields: "check" and "amount."

I want to match on "check"
I want to delete ALL the records from the "tblOustanding" if there is an exsisting (matching) record in the "tblBankStatement."

I have the following code that just doesn't work: I keep getting a parameter request. (what am I missing)?

DELETE
FROM Outstanding


WHERE EXISTS
( select Outstanding.Check
from Outstanding
where Outstanding.Check = BankStatement.Check
);

I tried it with no set relationships as well as an indeterminate relationship (listing all the records in the "tblOustanding" and only those records that match from "tblBankStatement")

[June7]

Personally, I would not have two tables. I would have a field for check status. This can be a Yes/No or a date or text (enter month or void).

[qa12dx]

i've never got a delete query to work when there is a join between tables. this is because i don't predefine relationships. i like to draw my own in queries.

how i do it is make a temp field- yes/no in the table i want to delete records from. run a update query and set the temp field to -1 where criteria matches. then a run a delete query to deleted where temp=-1

if you are good with defining relationships and like to work with them then the correct way is explained here.

http://office.microsoft.com/en-in/ac...010096302.aspx

[dlingley]

i've never got a delete query to work when there is a join between tables. this is because i don't predefine relationships. i like to draw my own in queries.

how i do it is make a temp field- yes/no in the table i want to delete records from. run a update query and set the temp field to -1 where criteria matches. then a run a delete query to deleted where temp=-1

if you are good with defining relationships and like to work with them then the correct way is explained here.

http://office.microsoft.com/en-in/ac...010096302.aspx

Hi, and thank you for the prompt response. Like I said, I removed the relationship and it still didn't work. I don't know that I "have" to have the relationship or not; either way it is not working. I need to keep the steps as simple as possible (I am changing a process to provide efficiency for others to follow). I do know know how your solution would apply to my situation. For example: How does the "yes/no" field get populated? (It can't be a manual process; there are over 200 records every month, so I can't add steps, I need to removed them. It all has to be done with the click of a button.)

I don't understand why I can't delete records where tblOne.check# = tblTwo.check# (???)

I guess I could make a query that populates a new field (with anything) if tblOne.check# = tblTwo.check#

I guess that is kind of like what you said, without the yes/no field.

I would really prefer to get the DELETE query to work with just the existing fields in the two tables because it is cleaner (and work I am passing on to others).

All suggestions welcome though... I HAVE to make this work.

Thank you.

[June7]

How do the records get into tblBankStatement if not manually entered? Is this a download from bank?

It is not necessary to define relationships in Relationship builder for queries to work.

This works:
DELETE
FROM Outstanding
WHERE Check IN
(SELECT Check
FROM BankStatement
);

[dlingley]

Personally, I would not have two tables. I would have a field for check status. This can be a Yes/No or a date or text (enter month or void).

Thank you for your help June7,

I do think it is best to keep the information in two tables (because they are from two different sources; the original data files for each have several fields in each) AND all the fields do NOT match. There are many items (checks) on the outstanding list that are not cleared from the bank (statement). At any rate, I haven't been able to get the DELETE query to work (I can select the data just fine, but can't delete it).

Thanks again.

[dlingley]

How do the records get into tblBankStatement if not manually entered? Is this a download from bank?

It is not necessary to define relationships in Relationship builder for queries to work.

This works:
DELETE
FROM Outstanding
WHERE Check IN
(SELECT Check
FROM BankStatement
);

Yes, the records are a data file (download) from the bank.

I've tried it with and without relationships.

I tried the code you suggested, but it says, "could not delete from specified tables."

[June7]

I created two tables and tested the query I posted and it worked for me. Don't know what to say. If you want to provide db for analysis, follow instructions at bottom of my post.

[dlingley]

I created two tables and tested the query I posted and it worked for me. Don't know what to say. If you want to provide db for analysis, follow instructions at bottom of my post.

Awesome, thank you. Yes, I don't know what the problem is AND now I have a new one. I put the files in a new folder and tested the two other queries (which were working) and now they won't work either -- it says that I cannot find the file "Outstanding$" (The file does not have a dollar sign on the end. Obviously, something is going awry now that I moved the files. I had an issue similar to this a couple of weeks ago when I moved a file from my USB to a network drive--it kept looking for my USB drive. It must be some sort of mapping issue that I don't know how to fix. ugh.)

There are two files.

The excel files that have the info for the two tables: checksCleared.xlsx (linked to the database)
The access database with 3 queries: BankReconciliation.accdb (DeleteClearedChecksFromOutstanding is the one I'm having trouble with, although the others won't work now that I moved it to a new folder).

Thanks again.

[eizquierdo]

your issues seems very similar to mine. This is how I solved it:
https://www.accessforums.net/queries...ery-32878.html

[dlingley]

Hi, I will give it a shot. I'm off from work this week, but will try it as some point during the break and report back here.

Thanks for all the help everyone. I WILL make this work...eventually.

[June7]

The tables in this db are links to Excel sheets. Cannot edit Excel sheets through links.

After moving files, refresh table links with the Linked Table Manager.

[dlingley]

The tables in this db are links to Excel sheets. Cannot edit Excel sheets through links.

After moving files, refresh table links with the Linked Table Manager.

I feel so silly. I should have known that I couldn't delete records outside the database (in an excel file).

Once I imported the data into tables, it worked like a charm.

Thanks again & thank you all for all your help. --Problem solved!