Help..I need this by this Friday and I still can't figure it out

[Sylvialn]

Ok...I need to be able to pull up a list of active patients (which I already have a crosstab query pulling it up - called Sylactivepts) - then from that query I need to be able to pull up their CNAVisit (in order for a user to enter the visit numbers to print on a report). Here's what the first query looks like for the active patients:

SELECT DISTINCT Patients.PatID, [Addresses]![NamePrefix] & IIf(IsNull([Addresses]![NamePrefix]),""," &quot & [addresses]![Key2] AS Phys, qryNurse.Name AS CaseMgr, Patients.PatName, Max(PatEvents.EventCode) AS MaxOfEventCode, Patients.KLevel AS PatActLevel, Patients.PatTeam, 1 AS PatCount, Patients.TeamCaseMgr, Patients.DiagCode1, Diagnoses.DiagDesc, Patients.AdmitDate, Patients.MedicareNum, Patients.MedicaidNum, DateDiff("d",[AdmitDate],Now()) AS LOS, Patients.LWInCht, Patients.HCSInCht, Patients.DPOAInCht, Patients.StOrd, Patients.EmKit, Patients.HCS, Patients.DPOA, Patients.LWSigned, Patients.DNR
FROM Diagnoses INNER JOIN (((Patients LEFT JOIN Addresses ON Patients.Physician1 = Addresses.Key3) LEFT JOIN PatEvents ON Patients.PatID = PatEvents.PatID) LEFT JOIN qryNurse ON Patients.TeamCaseMgr = qryNurse.ID) ON Diagnoses.DiagCode = Patients.DiagCode1
GROUP BY Patients.PatID, [Addresses]![NamePrefix] & IIf(IsNull([Addresses]![NamePrefix]),""," &quot & [addresses]![Key2], qryNurse.Name, Patients.PatName, Patients.KLevel, Patients.PatTeam, 1, Patients.TeamCaseMgr, Patients.DiagCode1, Diagnoses.DiagDesc, Patients.AdmitDate, Patients.MedicareNum, Patients.MedicaidNum, DateDiff("d",[AdmitDate],Now()), Patients.LWInCht, Patients.HCSInCht, Patients.DPOAInCht, Patients.StOrd, Patients.EmKit, Patients.HCS, Patients.DPOA, Patients.LWSigned, Patients.DNR, Patients.PatActive
HAVING (((Max(PatEvents.EventCode))<&quot;94&quot AND ((Patients.PatActive)=Yes))
ORDER BY Patients.PatID, Patients.PatName;


This query has to stay this way - it is used troughout the entire datbase (created before I arrived).

I created a second query pulling off of this query and have the CNAVisit column reading in. It looks like this:

SELECT Sylactivepts.PatID, Sylactivepts.PatName, Patients.TeamCNA, Patients.CNAVisit
FROM Sylactivepts LEFT JOIN Patients ON Sylactivepts.PatID = Patients.PatID;


My problem is that I cannot enter data into the second query - it pops up with the famous phrase: Recordset not updatbale. I have tried switching it to Dynaset (Inconsistent Updates), yet nothing. I am stressing b/c....the original query (Sylactivepts) is also not updatable (as it should be). But...I'm guessing here that this is why I cannot enter in my second query. Is there any way around this, or some trick I can use in order to allow my second query to allow data entry (I have checked all the properties and they all seem like they should work)?

Any help is much appreciated! Thanks &quot;Try everything at least once.&quot;

 

[tlbroadbent]

A query with aggregates is never updatable. The Group By makes the first query non-updateable. In addition, the first query contains functions (IIf). Queries that contain functions in the select list are not updateable.

The JOIN to that query makes the 2nd query non-updateable. You'll need to revise your query or rethink the process to create an updateable query. Surely, you don't need to use the first query to identify active patients. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Sylvialn]

I was afraid you'd say that.. Thanks for the info - at leastn ow I know the reason why it's not working. Thank you! &quot;Try everything at least once.&quot;

 

[jfischer]

The technique I use in these aggregate update situations is to change the summary query from a &quot;Select&quot; query to a &quot;Make Table&quot; query and store the summarized values into a temporary table. Then I join the temporary table to the table that I want to update. Optionally, you can delete the temporary table afterwards.