Unable to edit fields inside a query

I have a query, really simple one, 4 fields from a table, and one calculated field based on the values of the other 4 fields.


Problem is, when I browse the query I am unable to change the fields, but if I go into the table itself I can edit no problem.


What is causing this, and more important how do I get around it?

Can you post your SQL?

what is the sql of the query.

SELECT Control.Muni, Control.Type, Control.Number, [Muni] & [Type] & Format([Number],"000000.000") AS LookUp
FROM Control;

on the face of it no reason why it shouldn't work. Although I only see 3 fields, not 4.

You are using reserved words - type and number - which can produce unexpected errors. see this link
https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe

other thing to check is the query properties - it should not be snapshot which does not allow updates

You are correct the 4th field is the recordID (autoinc) which is not needed in the query.


I changed the variables names Type and Number to something else and it look like it wants to work now. Thanks for the assistance.

One level deeper and its happening again. I have a query that takes in two other querys. I can edit the data in both of the parent queries, but not the data in this query. I checked for control words, didn't see any. Sort of as to a loss why I keep bumping into this.


The SQL is as follows:

SELECT QRoll.ID, QRoll.munino, QRoll.rollnotype, QRoll.rollno, QRoll.recordtype, QRoll.ward, QRoll.hospital, QRoll.school, QRoll.neighborhood, QRoll.rhota, QRoll.psta, QRoll.surveydesc, QRoll.moresurvey, QRoll.ownership, QRoll.dwellings, QRoll.frontage, QRoll.frontageum, QRoll.addr, QRoll.lud, QRoll.community, QRoll.RollID, QControl.*
FROM QControl LEFT JOIN QRoll ON QControl.LookUp = QRoll.RollID;


QControl:
SELECT Control.MuniCtl, Control.RollType, Control.RolllNo, [MuniCtl] & [RollType] & Format([RolllNo],"000000.000") AS LookUp
FROM Control;



QRoll
SELECT Roll.ID, Roll.munino, Roll.rollnotype, Roll.rollno, Roll.recordtype, Roll.ward, Roll.hospital, Roll.school, Roll.neighborhood, Roll.rhota, Roll.psta, Roll.surveydesc, Roll.moresurvey, Roll.ownership, Roll.dwellings, Roll.frontage, Roll.frontageum, Roll.addr, Roll.lud, Roll.community, [munino] & [rollnotype] & Format([rollno],"000000.000") AS RollID
FROM Roll;

Normally once you are into joined queries of queries you will not be able to edit the results.

If you construct the joins carefully, you can sometimes overcome this, but you have calculated fields in both sub queries and I think that mean a no.

There are a myriad of reasons for this quite a few are covered here http://allenbrowne.com/ser-61.html

left joins can also prevent a query from being updateable - one thing to try is in the query properties change the recordsettype to 'dynaset - inconsistent updates'

If the query is a recordsource to a form they you will would need to change it there as well

I still fail to see why you need this rollID, in your latest query you can just join on the three relevant fields. Besides which it is probably the most inefficient way of doing things since you cannot use indexing - just do your calculated value in an unbound control on your form/report.

I'm guessing you have done this in order to prevent duplicates across the three fields, and you have assumed the only way of doing this is as a primary key. If so, you have not researched enough. You can create multi field indexes set to no duplicates, they do not have to be the primary key. You can then set your autonumber recordID as the primary key and store that in the other table as a family key. It will take many issues away

OK I was afraid of that. Not a problem, I will rework the queries so that I am not doing Queries of Queries.


Thanks for all you help everyone!!!