Delete Query does not work - wants to know which table to delete f

  • Thread starter Thread starter BlueWolverine
  • Start date Start date
B

BlueWolverine

Hello,
MS ACCESS 2003 on XP Pro.

When I try to run the query code that follows, i get an error asking about
specify which table to delete from. I don't know what to do about it.

The idea is to delete all records in Table B (t2) that are not also in Table
A (t1).

Thanks.


<<<
DELETE t2.Program, t2.ModelYear, t2.BuildPhase, t2.Month, t2.TagType,
t2.Projection, t2.Actual, t2.MonthName, t2.MonthDate, t2.CalendarYear,
t1.ModelYear, t1.ProgramName, t1.BuildPhase, t1.TagType, t1.Month,
t1.YearChoice
FROM t_TempCounts AS t1 RIGHT JOIN t_VehicleCounts AS t2 ON (t1.ProgramName
= t2.Program) AND (t1.ModelYear = t2.ModelYear) AND (t1.BuildPhase =
t2.BuildPhase) AND (t1.Month = t2.Month) AND (t1.TagType = t2.TagType) AND
(t1.YearChoice = t2.CalendarYear)
WHERE (((t2.CalendarYear)=[Forms]![frm_MAINMENU]![YearPick]) AND
((t1.ModelYear) Is Null) AND ((t1.ProgramName) Is Null) AND ((t1.BuildPhase)
Is Null) AND ((t1.TagType) Is Null) AND ((t1.Month) Is Null) AND
((t1.YearChoice) Is Null));
 
You are including columns from both tables in the DELETE clause. You might
find it simpler to use the NOT EXISTS predicate against a subquery, e.g.

DELETE *
FROM t2
WHERE CalendarYear = [Forms]![frm_MAINMENU]![YearPick]
AND NOT EXISTS
(SELECT *
FROM t1
WHERE t1.ProgramName = t2.Program
AND t1.ModelYear = t2.ModelYear
AND t1.TagType = t2.TagType
AND t1.YearChoice = t2.CalendarYear);

It goes without saying that the table should be backed up first before doing
anything like this.

Ken Sheridan
Stafford, England

BlueWolverine said:
Hello,
MS ACCESS 2003 on XP Pro.

When I try to run the query code that follows, i get an error asking about
specify which table to delete from. I don't know what to do about it.

The idea is to delete all records in Table B (t2) that are not also in Table
A (t1).

Thanks.


<<<
DELETE t2.Program, t2.ModelYear, t2.BuildPhase, t2.Month, t2.TagType,
t2.Projection, t2.Actual, t2.MonthName, t2.MonthDate, t2.CalendarYear,
t1.ModelYear, t1.ProgramName, t1.BuildPhase, t1.TagType, t1.Month,
t1.YearChoice
FROM t_TempCounts AS t1 RIGHT JOIN t_VehicleCounts AS t2 ON (t1.ProgramName
= t2.Program) AND (t1.ModelYear = t2.ModelYear) AND (t1.BuildPhase =
t2.BuildPhase) AND (t1.Month = t2.Month) AND (t1.TagType = t2.TagType) AND
(t1.YearChoice = t2.CalendarYear)
WHERE (((t2.CalendarYear)=[Forms]![frm_MAINMENU]![YearPick]) AND
((t1.ModelYear) Is Null) AND ((t1.ProgramName) Is Null) AND ((t1.BuildPhase)
Is Null) AND ((t1.TagType) Is Null) AND ((t1.Month) Is Null) AND
((t1.YearChoice) Is Null));
Click to expand...
 
This worked, sort of, but didn't return nearly as many records as were in the
original code when datasheet viewed.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


Ken Sheridan said:
You are including columns from both tables in the DELETE clause. You might
find it simpler to use the NOT EXISTS predicate against a subquery, e.g.

DELETE *
FROM t2
WHERE CalendarYear = [Forms]![frm_MAINMENU]![YearPick]
AND NOT EXISTS
(SELECT *
FROM t1
WHERE t1.ProgramName = t2.Program
AND t1.ModelYear = t2.ModelYear
AND t1.TagType = t2.TagType
AND t1.YearChoice = t2.CalendarYear);

It goes without saying that the table should be backed up first before doing
anything like this.

Ken Sheridan
Stafford, England

BlueWolverine said:
Hello,
MS ACCESS 2003 on XP Pro.

When I try to run the query code that follows, i get an error asking about
specify which table to delete from. I don't know what to do about it.

The idea is to delete all records in Table B (t2) that are not also in Table
A (t1).

Thanks.


<<<
DELETE t2.Program, t2.ModelYear, t2.BuildPhase, t2.Month, t2.TagType,
t2.Projection, t2.Actual, t2.MonthName, t2.MonthDate, t2.CalendarYear,
t1.ModelYear, t1.ProgramName, t1.BuildPhase, t1.TagType, t1.Month,
t1.YearChoice
FROM t_TempCounts AS t1 RIGHT JOIN t_VehicleCounts AS t2 ON (t1.ProgramName
= t2.Program) AND (t1.ModelYear = t2.ModelYear) AND (t1.BuildPhase =
t2.BuildPhase) AND (t1.Month = t2.Month) AND (t1.TagType = t2.TagType) AND
(t1.YearChoice = t2.CalendarYear)
WHERE (((t2.CalendarYear)=[Forms]![frm_MAINMENU]![YearPick]) AND
((t1.ModelYear) Is Null) AND ((t1.ProgramName) Is Null) AND ((t1.BuildPhase)
Is Null) AND ((t1.TagType) Is Null) AND ((t1.Month) Is Null) AND
((t1.YearChoice) Is Null));
Click to expand...
Click to expand...
 
Your code missed one line of equals, the BuildPhase field. That's added and
it seems to have worked.
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


Ken Sheridan said:
You are including columns from both tables in the DELETE clause. You might
find it simpler to use the NOT EXISTS predicate against a subquery, e.g.

DELETE *
FROM t2
WHERE CalendarYear = [Forms]![frm_MAINMENU]![YearPick]
AND NOT EXISTS
(SELECT *
FROM t1
WHERE t1.ProgramName = t2.Program
AND t1.ModelYear = t2.ModelYear
AND t1.TagType = t2.TagType
AND t1.YearChoice = t2.CalendarYear);

It goes without saying that the table should be backed up first before doing
anything like this.

Ken Sheridan
Stafford, England

BlueWolverine said:
Hello,
MS ACCESS 2003 on XP Pro.

When I try to run the query code that follows, i get an error asking about
specify which table to delete from. I don't know what to do about it.

The idea is to delete all records in Table B (t2) that are not also in Table
A (t1).

Thanks.


<<<
DELETE t2.Program, t2.ModelYear, t2.BuildPhase, t2.Month, t2.TagType,
t2.Projection, t2.Actual, t2.MonthName, t2.MonthDate, t2.CalendarYear,
t1.ModelYear, t1.ProgramName, t1.BuildPhase, t1.TagType, t1.Month,
t1.YearChoice
FROM t_TempCounts AS t1 RIGHT JOIN t_VehicleCounts AS t2 ON (t1.ProgramName
= t2.Program) AND (t1.ModelYear = t2.ModelYear) AND (t1.BuildPhase =
t2.BuildPhase) AND (t1.Month = t2.Month) AND (t1.TagType = t2.TagType) AND
(t1.YearChoice = t2.CalendarYear)
WHERE (((t2.CalendarYear)=[Forms]![frm_MAINMENU]![YearPick]) AND
((t1.ModelYear) Is Null) AND ((t1.ProgramName) Is Null) AND ((t1.BuildPhase)
Is Null) AND ((t1.TagType) Is Null) AND ((t1.Month) Is Null) AND
((t1.YearChoice) Is Null));
Click to expand...
Click to expand...
 
Back
Top