query not updateable - how can i make it updateable?

[mudstuffin]

Hello. I hope someone can help with a query I have about a query.

Here is the SQL for a query I am trying to run in an access db. It is not updateable. Can anyone tell me what I am doing wrong? I need it to be updateable.

SELECT tblCRESTdata.CRESTcaseID, tblCRESTdata.[OUT-REG-NO], tblCRESTdata.[OUT-GROUPNO], tblCRESTdata.[OUT-SUB-FORENAME], tblCRESTdata.[OUT-SUB-SURNAME], tblCRESTdata.[OUT-DOB], tblCRESTdata.[OUT-PAT-FORENAME], tblCRESTdata.[OUT-PAT-SURNAME], tblCRESTdata.[OUT-CLAIM-NO], tblCRESTdata.[OUT-GROUP-NAME], MyNewTable.NewTableID, MyNewTable.crestID, MyNewTable.TelNo, MyNewTable.Contact1, MyNewTable.Contact2, MyNewTable.Contact3, MyNewTable.Contact4, MyNewTable.ContactMade, MyNewTable.NeedToTryAgain, tblCRESTdata.[OUT-ACC-TYPE], MyNewTable.UnableToObtainTel, MyNewTable.UserNm, tblAdmin.AdminCRESTcaseID, tblAdmin.CaseStatus
FROM (tblCRESTdata LEFT JOIN MyNewTable ON tblCRESTdata.CRESTcaseID = MyNewTable.crestID) LEFT JOIN tblAdmin ON tblCRESTdata.CRESTcaseID = tblAdmin.AdminCRESTcaseID
WHERE (((tblAdmin.CaseStatus) Like "*suspen*"));


It is ok when it was like this:

SELECT tblCRESTdata.CRESTcaseID, tblCRESTdata.[OUT-REG-NO], tblCRESTdata.[OUT-GROUPNO], tblCRESTdata.[OUT-SUB-FORENAME], tblCRESTdata.[OUT-SUB-SURNAME], tblCRESTdata.[OUT-DOB], tblCRESTdata.[OUT-PAT-FORENAME], tblCRESTdata.[OUT-PAT-SURNAME], tblCRESTdata.[OUT-CLAIM-NO], tblCRESTdata.[OUT-GROUP-NAME], MyNewTable.NewTableID, MyNewTable.crestID, MyNewTable.TelNo, MyNewTable.Contact1, MyNewTable.Contact2, MyNewTable.Contact3, MyNewTable.Contact4, MyNewTable.ContactMade, MyNewTable.NeedToTryAgain, tblCRESTdata.[OUT-ACC-TYPE], MyNewTable.UnableToObtainTel, MyNewTable.UserNm
FROM tblCRESTdata LEFT JOIN MyNewTable ON tblCRESTdata.CRESTcaseID = MyNewTable.crestID;

But I need to add in the third table (tbladmin) to specify the criteria.

Regards,

Greg.

 

[Crowley16]

a query becomes not updateable if/when you have a 1 to many link because esentially the database don't know which record you want to be updating...

if you keep all your links to 1-1, and ensure the data exsists on both sides(inner joins), then it should let you update...



--------------------
Procrastinate Now!

 

[PHV]

And this ?
FROM (tblCRESTdata
INNER JOIN tblAdmin ON tblCRESTdata.CRESTcaseID = tblAdmin.AdminCRESTcaseID)
LEFT JOIN MyNewTable ON tblCRESTdata.CRESTcaseID = MyNewTable.crestID
WHERE tblAdmin.CaseStatus Like '*suspen*';

You may have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886