1

I am trying to run a delete query that contains a subquery and an inner join and get the error

Query must have at least one destination field.

I have read elsewhere that you must use either of the following.

  • DISTINCTROW
  • WHERE EXISTS

But I still get the error message. I have read How to delete in MS Access when using JOIN's? and MS ACCESS delete query syntax combined with inner join problems but can't get it to work.

Here is my query.

DELETE FROM tblUp 
WHERE NOT EXISTS (
    SELECT tblUp.request_id, 
           tblUp.gid, 
           MAX(tblUp.savings_year) AS MaxOfsavings_year, 
           LAST(tblUp.savings_month) AS LastOfsavings_month
    FROM tblUp 
    INNER JOIN tbl_w ON tblUp.request_id = tblW.id
    GROUP BY tblUp.request_id, 
             tblUp.gid, 
             tblW.Cde
    HAVING (((tblW.Cde)="ML"))
    ORDER BY tblUp.request_id, 
             tblUp.gid,
             MAX(tblUp.savings_year),
             LAST(tblUp.savings_month)
)
Parfait
  • 104,375
  • 17
  • 94
  • 125
Brian
  • 2,078
  • 1
  • 15
  • 28
  • I think it's delete * in access, the having maybe a where too?? I dont think the order by is needed in delete? – Nathan_Sav Jan 14 '22 at 15:19
  • @Nathan_Sav I added `*` before, and again now, and it works but returns no records. I need the `ORDER BY`, I think, to get the right records to delete (Note the Last function). Not sure about your statement on `HAVING`. – Brian Jan 14 '22 at 15:28
  • 1
    Having is the result of aggregation, so i'm presuming you're just interested in where the .cde is "ML"? – Nathan_Sav Jan 14 '22 at 15:30
  • @Nathan_Sav Yes. However, I still get no results when I change `HAVING` to `WHERE` and remove the `ORDER BY`. – Brian Jan 14 '22 at 15:47
  • I dont think you're using Exists correctly as tey way it is, it will always be true or always be false. Exists would be select t.id from tblup as t where not exists (select 1 from tblDown as d where d.id=t.id) The only thing yours needs to be true/false is a .cde of "ML" and be in tblUP Can you show some example data of what you want to achieve. – Nathan_Sav Jan 14 '22 at 16:13
  • Just a waring: (Please make a backup BEFORE trying the query.....) – Luuk Jan 14 '22 at 16:51
  • @Brian In a nutshell, what records do you want to delete from tblUp? Do you want to delete all records without a value of tblW.Cde="ML"? Secondly, and just as a tip, whenever using subqueries, always use a table alias when a table name is used in both the outer query and in the subquery (e.g., `FROM tblUp WHERE (SELECT u.[request_id] FROM tblUp AS u INNER JOIN...`. This helps the database engine avoid confusion. – bugdrown Jan 14 '22 at 18:05
  • @bugdrown I want to delete all records from tblUp where tblW.Cde = "ML" AND only the last record of the final year. I have 13 records for a year long project that may start in the middle of the year. I need to delete the last record. So I need to delete the last July for each project and only retain 12 months (records). – Brian Jan 14 '22 at 19:03
  • Be wary of using LAST and FIRST aggregate functions. Tables do not have inherent order and these can produce unexpected results. Might edit question to provide sample data and desired result as text tables. – June7 Jan 15 '22 at 12:16
  • "I need to delete the last record." - this seems odd. Delete the most recent record by date and retain older? Is saving_month the month name or month number? – June7 Jan 15 '22 at 12:36

0 Answers0