Query on Calculated value with data entry (1 Viewer)

[bayouc]

First time posting - but have learned a lot from this forum in the past... a lot of talent out there!

My DB entry is built around a single form that has specific behaviors. I feed it different SQLs to allow the user to access the different sections of the db. It has worked great until now... I cannot figure out how to generate an SQL line that allows data entry when the join is on a derived quantity.

I have a function JD which calculates a value based on an entry in a table. I have another table which I want to link to that calculated value. I thought the right method was to create a separate query - QLineTime - with the calculated value:

SELECT Lines.ID, JD([Lines]![Line]) AS JD FROM Lines;

Then perform my joins using the query:

SELECT DISTINCTROW MBESCalibration.* FROM (MBESCalibration LEFT JOIN QLineTime ON MBESCalibration.JD = QLineTime.JD) LEFT JOIN Lines ON QLineTime.ID = Lines.ID;

It displays exactly what I want to see - but I cannot do data entry with that SQL. I suspect it is because of the use of the interim query, but do not know how to do the join in one line.

How do I fix this??? Is it even possible (seems like it should be)?

BayouC

 

[bayouc]

If I allow inconsistent updates it works fine. Not sure why since I have other similar queries that work with multiple tables and consistent updates... but this fix works.

 

[boblarson]

If you look here: http://allenbrowne.com/ser-61.html

you'll see this:

It contains a DISTINCT predicate. Set Unique Values to No in the query's Properties.

 

[bayouc]

That is an excellent reference! Thanks!

Unfortunately, now I am more confused on this particular problem. I thought DISTINCTROWS was updatable, even though DISTINCT is not. And going through the rest of the list it is not obvious what is causing the read-only... unless it is related to the index

 

[bayouc]

Clarification:

If I set up a query with no calculated value - everything works fine and I have read access. If I perform a calculation, it becomes read-only.

This query can be updated:
SELECT DISTINCTROW Sheets.*
FROM (Sheets INNER JOIN QTest2 ON Sheets.ID = QTest2.ID2) INNER JOIN Lines ON QTest2.ID = Lines.ID;

IF the query for QTest2 is:
SELECT [Lines]![Sheet] AS ID2, Lines.ID, Lines.Type FROM Lines;

BUT NOT if the query is:
SELECT [Lines]![Sheet]*1 AS ID2, Lines.ID, Lines.Type FROM Lines;

That does not make a bit of sense to me??? It doesn't affect the subquery (Qtest2), that has read access regardless. Just the main query. Based on that article you sent I'd guess it was the indexing, but I have not idea how to fix it.

 

[bayouc]

Ok - learned that access cannot index calculated fields... so this is not possible without allowing inconsistent updates. Thanks though, Bob, the link you provided is most useful and bookmarked...