Select Query to update table

[LB79]

Hello,

I have a select query which, when I change a result in it, should change the data in the table behind it, however, when I try to change the data via the select query, nothing happens (I cant make any changes like its locked). I cant seem to see an option to allow edits. Please can someone advise on this seemingly basic function.

Thanks

 

[redneckgeek]

It's possible that you're not using an updateable query.
Can you post your SQL?

 

[LB79]

Its just a basic select query... I thought you could update using SELECT.

SELECT tblClientBasic.ClientID, tblClientBasic.ClientForename, tblClientBasic.ClientSurname, tblClientBasic.ClientCreateDate, tblClientBasic.ClientFirstVisit, tblClientBasic.[Allergy Notes], tblClientContact.ClientTel, tblClientContact.ClientEmail, tblClientContact.ClientAddress1, tblClientContact.ClientTown, tblClientContact.ClientCounty, tblClientContact.ClientPostcode, tblClientHistory.[Date Of Appointment], tblClientHistory.Treatment, tblClientHistory.[Treatment Details], tblClientHistory.Colour, tblClientHistory.[Colour Details], tblClientHistory.[Total Time], tblClientHistory.[Total Cost], tblClientHistory.[Notes From Last Appointment]
FROM (tblClientBasic LEFT JOIN tblClientContact ON tblClientBasic.ClientID = tblClientContact.ClientID) LEFT JOIN tblClientHistory ON tblClientBasic.ClientID = tblClientHistory.ClientID;

 

[MSAccessRookie]

Its just a basic select query... I thought you could update using SELECT.

SELECT tblClientBasic.ClientID, tblClientBasic.ClientForename, tblClientBasic.ClientSurname, tblClientBasic.ClientCreateDate, tblClientBasic.ClientFirstVisit, tblClientBasic.[Allergy Notes], tblClientContact.ClientTel, tblClientContact.ClientEmail, tblClientContact.ClientAddress1, tblClientContact.ClientTown, tblClientContact.ClientCounty, tblClientContact.ClientPostcode, tblClientHistory.[Date Of Appointment], tblClientHistory.Treatment, tblClientHistory.[Treatment Details], tblClientHistory.Colour, tblClientHistory.[Colour Details], tblClientHistory.[Total Time], tblClientHistory.[Total Cost], tblClientHistory.[Notes From Last Appointment]
FROM (tblClientBasic LEFT JOIN tblClientContact ON tblClientBasic.ClientID = tblClientContact.ClientID) LEFT JOIN tblClientHistory ON tblClientBasic.ClientID = tblClientHistory.ClientID;

You are able to update using Select as long as all of the required information is present.

In your case, I believe that the LEFT JOIN Statements make the query non updateable, because Nulls are substituted as returned values for rows that are not present in all tables.

 

[LB79]

Ahh I see (sort of)... I will take a closer look.

Thanks

 

[Rabbie]

See this link to see what makes a query non-updateable.