Delete Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

THe delete query works if I enter the critertia 2006

But when I join the query to the history table, to make the delete dynamic.
I get error message, "Specificy the table containing the records you want to
delete"
What do I need to do??


DELETE AKB_TY_TM_DER_HIST.YEAR
FROM AKB_TY_TM_DER_HIST INNER JOIN tbl_HIST_YR ON AKB_TY_TM_DER_HIST.YEAR =
tbl_HIST_YR.YEAR
WHERE (((AKB_TY_TM_DER_HIST.YEAR)=[tbl_HIST_YR]![YEAR]));


thanks
H
 
Try this

DELETE DISTINCT ROW AKB_TY_TM_DER_HIST.YEAR
FROM AKB_TY_TM_DER_HIST INNER JOIN tbl_HIST_YR ON AKB_TY_TM_DER_HIST.YEAR =
tbl_HIST_YR.YEAR
WHERE (((AKB_TY_TM_DER_HIST.YEAR)=[tbl_HIST_YR]![YEAR]));


If that fails then try using a subquery in the WHERE clause

DELETE AKB_TY_TM_DER_HIST.YEAR
FROM AKB_TY_TM_DER_HIST
WHERE AKB_TY_TM_DER_HIST.YEAR IN
(SELECT [tbl_HIST_YR].[YEAR]
FROM tbl_HIST_YR )


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks,

The second option worked, thanks agian..

John Spencer said:
Try this

DELETE DISTINCT ROW AKB_TY_TM_DER_HIST.YEAR
FROM AKB_TY_TM_DER_HIST INNER JOIN tbl_HIST_YR ON AKB_TY_TM_DER_HIST.YEAR =
tbl_HIST_YR.YEAR
WHERE (((AKB_TY_TM_DER_HIST.YEAR)=[tbl_HIST_YR]![YEAR]));


If that fails then try using a subquery in the WHERE clause

DELETE AKB_TY_TM_DER_HIST.YEAR
FROM AKB_TY_TM_DER_HIST
WHERE AKB_TY_TM_DER_HIST.YEAR IN
(SELECT [tbl_HIST_YR].[YEAR]
FROM tbl_HIST_YR )


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

hrskinn said:
THe delete query works if I enter the critertia 2006

But when I join the query to the history table, to make the delete
dynamic.
I get error message, "Specificy the table containing the records you want
to
delete"
What do I need to do??


DELETE AKB_TY_TM_DER_HIST.YEAR
FROM AKB_TY_TM_DER_HIST INNER JOIN tbl_HIST_YR ON AKB_TY_TM_DER_HIST.YEAR
=
tbl_HIST_YR.YEAR
WHERE (((AKB_TY_TM_DER_HIST.YEAR)=[tbl_HIST_YR]![YEAR]));


thanks
H
Click to expand...
Click to expand...
 
Back
Top