Deleting Records in a Query -- with a 2 point join in 2 Tables

[Guest]

Hello,

I have a query that has 2 tables ("Main" and "Update") joined by 2 fields --
"ID" and "SubID". I then select all fields from "Update" and put them into
the grid. I run the query (select query) and then proceed to select all
records and hit delete. Unfortunately, it deletes the data from THE WRONG
TABLE! It deleted the records from "Main" instead of "Update." How and why
does this happen and how do I get around it instead of doing a manual delete
by opening up table "Main?"

Please let me know if I am being unclear and thank you in advance!

MN

 

[Guest]

Its hard to answer your question without seeing the actuall delete SQL (can
you post it?).

Also, does the records in Update are still in the table?
Is there any relationship declared for this table?

 

[Guest]

Here is the sql but it's a little long. Please note that "IFT - MA 9-24-07"
in the SQL is "Update" down below and "Allocation Items to Replace" is the
'Main" Table. Also, JDE Bus Unit and JDE GL are the 2 fields that are
joined. Here is the SQL:

SELECT [IFT - MA 9-24-07].[Facility Manager], [IFT - MA 9-24-07].[JDE Bus
Unit], [IFT - MA 9-24-07].[JDE GL], [IFT - MA 9-24-07].Responsibility, [IFT -
MA 9-24-07].[Contracted Service], [IFT - MA 9-24-07].[A-01], [IFT - MA
9-24-07].[A-02], [IFT - MA 9-24-07].[A-03], [IFT - MA 9-24-07].[A-04], [IFT -
MA 9-24-07].[C-May], [IFT - MA 9-24-07].[C-June], [IFT - MA
9-24-07].[C-July], [IFT - MA 9-24-07].[C-August], [IFT - MA
9-24-07].[C-September], [IFT - MA 9-24-07].[C-October], [IFT - MA
9-24-07].[C-November], [IFT - MA 9-24-07].[C-December], [IFT - MA
9-24-07].[B-01], [IFT - MA 9-24-07].[B-02], [IFT - MA 9-24-07].[B-03], [IFT -
MA 9-24-07].[B-04], [IFT - MA 9-24-07].[B-05], [IFT - MA 9-24-07].[B-06],
[IFT - MA 9-24-07].[B-07], [IFT - MA 9-24-07].[B-08], [IFT - MA
9-24-07].[B-09], [IFT - MA 9-24-07].[B-10], [IFT - MA 9-24-07].[B-11], [IFT -
MA 9-24-07].[B-12], [IFT - MA 9-24-07].[F-Jan], [IFT - MA 9-24-07].[F-Feb],
[IFT - MA 9-24-07].[F-Mar], [IFT - MA 9-24-07].[F-Apr], [IFT - MA
9-24-07].[F-May], [IFT - MA 9-24-07].[F-Jun], [IFT - MA 9-24-07].[F-Jul],
[IFT - MA 9-24-07].[F-Aug], [IFT - MA 9-24-07].[F-Sep], [IFT - MA
9-24-07].[F-Oct], [IFT - MA 9-24-07].[F-Nov], [IFT - MA 9-24-07].[F-Dec],
[IFT - MA 9-24-07].[Re-Forecast Assumptions], [IFT - MA 9-24-07].[Budget
Assumptions], [IFT - MA 9-24-07].[PA-Jan], [IFT - MA 9-24-07].[PA-Feb], [IFT
- MA 9-24-07].[PA-Mar], [IFT - MA 9-24-07].[PA-Apr], [IFT - MA
9-24-07].[PA-May], [IFT - MA 9-24-07].[PA-Jun], [IFT - MA 9-24-07].[PA-Jul],
[IFT - MA 9-24-07].[PA-Aug], [IFT - MA 9-24-07].[PA-Sep], [IFT - MA
9-24-07].[PA-Oct], [IFT - MA 9-24-07].[PA-Nov], [IFT - MA 9-24-07].[PA-Dec]
FROM [Allocation Items to Replace] INNER JOIN [IFT - MA 9-24-07] ON
([Allocation Items to Replace].[JDE GL] = [IFT - MA 9-24-07].[JDE GL]) AND
([Allocation Items to Replace].[JDE Bus Unit] = [IFT - MA 9-24-07].[JDE Bus
Unit]);

Also...to answer your other questions. The records that were SUPPOSED To be
deleted after running this query are still IN that table. There is also no
relationship established (in the relationships window) between these 2
tables. If that will rectify the situation, please let me know!

Thanks a lot!
MN