Query reults not deletable?!? 1

[DarkOne72]

Hi All,

I have a problem maybe you awesome guys can help with.
I have a query that has 2 seperate tables in it and all it does is show the records that are in both tables. Now it works great but when I show the results and I want to delete those records from the results it says it is read only and it won't delete. I have primary keys on both tables and I am using access 2007. Here is the SQL for the query:

SELECT tblMaster.Company, tblMaster.Street_Number, tblMaster.Street_Name, tblMaster.Suffix, tblMaster.Post_Dir, tblMaster.Unit_Type, tblMaster.Unit_Num, tblMaster.City, tblMaster.State, tblMaster.Zip5
FROM tblCompleted INNER JOIN tblMaster ON tblCompleted.Customer_ID = tblMaster.Customer_ID
WHERE (((tblMaster.Customer_ID)=[tblCompleted]![Customer_ID]))
GROUP BY tblMaster.Company, tblMaster.Street_Number, tblMaster.Street_Name, tblMaster.Suffix, tblMaster.Post_Dir, tblMaster.Unit_Type, tblMaster.Unit_Num, tblMaster.City, tblMaster.State, tblMaster.Zip5
ORDER BY tblMaster.Zip5;


Is there something I am doing wrong? I need tp be able to delete the record(s) out of the tblMaster only; I need this option so if I choose to delete 1 record or more I can with these criterias.

Thanks guys!

 

[dhookom]

Why is this query a "Group By" query? Try remove the Group BY as well as the criteria since it doesn't seem necessary.

Duane
Hook'D on Access
MS Access MVP

 

[DarkOne72]

Thank yo ufor the reply, I tried what you said and it works except for the fact it deletes the record in the tblCompleted as well; maybe because it is joined by custoemr_id? I only need it to delete the record in tblMaster

 

[dhookom]

If you only want to delete from one table, why do you have two tables in your query?

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

The rules (or at least some of them) are

- Queries with UNION, GROUP BY, DISTINCT, LEFT JOIN, RIGHT JOIN are not updatable

- Queries that do not contain primary keys are not updatable.

- If a JOIN is one-to-one then both records will be deleted when you delete a JOINed record from the query.

- If a JOIN is one-to-many then the "many" record will be deleted but not the "one" record.

 

[PHV]

Have a look here:

http://support.microsoft.com/kb/148581

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DarkOne72]

I have the two tables in the query to compare against each other. Example: in the master table it holds all the records in the completed table it is records from the master tbale which are complete joined by the unique Customer_ID. So in turn I need to determine which records in the master are complete and if the id matches in the completed table then I want to be able to delete only those records in the master table which already show up in the completed table.

 

[Golom]

There's no reason to mess around with a JOIN. Just delete them.

Delete * From tblMaster

Where CustomerID IN (Select CustomerID From tblCompleted)

 

[DarkOne72]

Thank you Golom it worked perfect.
Also, thank you to all the rest of you who helped.