UtterAccess Discussion Forums

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

ReyZRX

May 24 2012, 04:10 PM

Howdy all.
Need some help with a query whose results are not updateable, that is I cannot enter
the correct value into the desired field ([ICD9 with Period]).

I believe it might be because the query involves another query and the base table
([History Edit]) does not have a primary key.

This is the base query:

SQL

SELECT ICD9.[ICD9 w/o period], ICD9.[ICD9 with Period], ICD9.[4th or 5th digit
required]
FROM ICD9 WHERE (((ICD9.[4th or 5th digit required])=False));

The un-updatable query currently returns 5 rows and is:

SQL

SELECT DISTINCTROW HE.[ID#], HE.ICD9, qV.[ICD9 with Period],
HE.LAST_NAME, HE.DOS, HE.CPT4, HE.CITY, HE.STATE, HE.[INSURANCE
PLAN], HE.[DENIAL CODE]
FROM [History Edit] AS HE LEFT JOIN qryValid_ICD9 AS qV ON HE.ICD9 =
qV.[ICD9 with Period]
WHERE (((qV.[ICD9 with Period]) Is Null))
ORDER BY HE.[ID#];

When I revise the un-updatable query into a NOT IN query it takes longer and returns
no rows:

SQL

SELECT [History Edit].[ID#], [History Edit].ICD9, [History Edit].LAST_NAME,
[History Edit].DOS, [History Edit].CPT4, [History Edit].CITY, [History Edit].STATE,
[History Edit].[INSURANCE PLAN], [History Edit].[DENIAL CODE]
FROM [History Edit]
WHERE ((([History Edit].ICD9) Not In (select [ICD9 with Period] from ICD9 where [4th or 5th digit required]= false)))
ORDER BY [History Edit].[ID#];

However, if I revise it again into a NOT EXISTS, the revised query is fast and returns
the 5 rows but is still not updatable.

SQL

SELECT [History Edit].[ID#], [History Edit].ICD9, "" as [ICD9 with Period],[History Edit].LAST_NAME, [History Edit].DOS, [History Edit].CPT4, [History Edit].CITY, [History Edit].STATE, [History Edit].[INSURANCE PLAN], [History Edit].[DENIAL CODE]
FROM [History Edit]
WHERE NOT Exists (select [ICD9 with Period] from [ICD9] where ([icd9].[4th or 5th digit required]= false) AND [History Edit].[ICD9] = [icd9].[ICD9 with Period])
ORDER BY [History Edit].[ID#];

The query would be used in a subform for data validation and I'd like the user to be
able to make corrections on the subform.

Thanks in advance,

Rey

theDBguy

May 24 2012, 04:21 PM

Hi Rey,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Not sure what else to tell you, if the query result is not updatable, then I wouldn't try to force it to become updatable because there is no guarantee of the outcome. There is a very good reason why queries are restricted to be read only on certain cases.

To make the subform updatable, you can base it on a temporary table that you create from your query, but what I would really recommend is just add an "edit" button on the subform to give the user another place to edit the data where it is editable. For example, a popup form that is simply based on the single table, instead of multiple data sources in the query, that needs to be updated.

Just my 2 cents...

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.