Can't update query

[kman42]

I had a form that used some Dlookups, but it was really slow, so I changed the form so that it is based on a query that uses subqueries in place of the Dlookups. But now I get an error that the recordset is not updateable. I googled this error and have tried to sift through the many explanations, but I can't seem to find a solution. I even changed the recordset type of the form and the underlying query to Dynaset (inconsistent updates) and it still doesn't work. Here's a skeleton view of my query:

SELECT tbl_Initiatives.pk_InitiativeID, tbl_Initiatives.Title, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=5 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualApprovalToDevelop, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=6 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualFOADevelopmentMtg, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=7 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualSubmitToENS, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=4 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualSubmitToDERA, (SELECT DateOfEvent FROM tbl_EventLog WHERE fk_EventID=3 and fk_InitiativeID=tbl_Initiatives.pk_InitiativeID) AS ActualSubmitToOD
FROM tbl_Initiatives INNER JOIN (tbl_Events INNER JOIN tbl_EventLog ON tbl_Events.pk_EventID = tbl_EventLog.fk_EventID) ON tbl_Initiatives.pk_InitiativeID = tbl_EventLog.fk_InitiativeID;

tbl_Initiatives has a 1:many relationship with tbl_Eventlog on pk_InitiativeID=fk_InitiativeID. tbl_EventLog has a 1:1 relationship with tbl_Events on fk_EventID=pk_EventID. tbl_Initiatives has a primary key on pk_InitiativeID, tbl_EventLog has one on pk_EventLogID, and tbl_Events has one on pk_EventID.

Any help would be most appreciated,
kman

[RuralGuy]

See if this link sheds some light on the subject: http://allenbrowne.com/ser-61.html

[kman42]

See if this link sheds some light on the subject: http://allenbrowne.com/ser-61.html

That's a good resource, thanks. Unfortunately, it says that I can't use subqueries in a SELECT statement and that I should use domain aggregate functions instead, which is what I was trying to avoid to begin with.

Oh, Access, how I love thee...

[RuralGuy]

At least now you know why the query was not updateable. It all has to do with ambibuous records. Sometimes just having good AutoNumber PrimaryKeys solves the issue.