Ok..down to basics and still having trouble

[Sylvialn]

History of my problem: had 1 query pulled off of another one and it would not let me enter or change the data - was posted in this forum (yesterday and today).

Having problems with: "Recordset not updatable." I have a new query based off of 2 tables (back to the beginning).....here's what it looks like in SQL:

SELECT DISTINCT Patients.PatID, Patients.PatName, Max(PatEvents.EventCode) AS MaxOfEventCode, Patients.KLevel AS PatActLevel, Patients.PatTeam, 1 AS PatCount, Patients.AdmitDate, DateDiff("d",[AdmitDate],Now()) AS LOS
FROM Patients LEFT JOIN PatEvents ON Patients.PatID = PatEvents.PatID
GROUP BY Patients.PatID, Patients.PatName, Patients.KLevel, Patients.PatTeam, 1, Patients.AdmitDate, DateDiff("d",[AdmitDate],Now()), Patients.PatActive
HAVING (((Max(PatEvents.EventCode))<&quot;94&quot AND ((Patients.PatActive)=Yes))
ORDER BY Patients.PatID, Patients.PatName;


This is as basic as I can get the query in order to pull up what I need. Yet....it still tells me the Recordset is not updatable. Is there any hope for me and this problem? This query above goes into our patient table, pulls up all active patients (denoted by PatActive = Yes), then checks it against the Events table (where the information is stored). A patient is noted as inactive if they have PatActive = No and an event code of 94 or greater (events states that the patient has died, etc.). This is as basic as I can get the query. So..the query pulls up active patients. I can't figure out how I can get it to let me enter data.

Please..anyone..I'm at the end of the rope! &quot;Try everything at least once.&quot;

 

[MichaelRed]

The 'not updateable' occurs if Ms. Access (or any VALID relational db) cannot uniquely associate EVERY field of a recordset to it's origin. Unfortunatly, this is 'true' for the db engine even when it is (obviously) false for the user.

The simple soloution is to use you current query as the basis of creating another recordset which IS updateable, or to use it to identify specific records in the seperate tables which you are updateable.

It does depend (a lot) on the details of what you need to accomplish, but a simple approach would be to use your query as a recordset which is used to select (seek primary key of table from the recordset) the records to be modified, modify the record in the TABLE, then move on to the next record in the query.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Sylvialn]

I'm not to sure what you mean...How can I accomplish this????? If I pull up the query I already have (it is not updatable) how can I use that to create another one that I can edit - wouldn't both be not updatable? &quot;Try everything at least once.&quot;

 

[MichaelRed]

Hmmmmmmmmmmmmm,

It just gets murkier and murkier the deeper I go?

I will need to know a bit about your ability re Ms. Access / VBA before responding in detail.

Conceptually, you use the existing query as a recordset.

Within that recordset, you should (WILL) have the necessary fields to identify the records in the source tables which you want to modify (Edit).

So, Instantiate the (existing) query as a recordset.

From Each Record in this recordset, construct the criteria which identifies the record.

Use the Seek (or other available method) to locate the record you need to modify within the table.

Modify (edit) and Update the record.

Get the next record from the (existing) recordset (until there are no more).

Requires MODEST coding.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over