Update Queries using queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I have an update query, but would like to limit the values to be updated to include only those values which appear in another query. Now, I can do this with an In (Select ...) statement in criteria, but for some reason it runs EXTREMELY slowly that way.

Here's the deal
I want to update tblActivity.PrintedOn to Now(
I only want to do so if tblActivity.ActivityID = qryHCFA1500.ActivityI

Here's what I hav
UPDATE tblActivity SET tblActivity.PrintedOn = Now(
WHERE (((tblActivity.PrintedOn) Is Null) AND ((tblActivity.ActivityID) In (SELECT qry1500.ActivityID FROM qry1500)))

Now, originally I had In (SELECT qry1500.ActivityID FROM qry1500 WHERE qry1500.ActivityID=tblActivity.ActivityID) but that gave me a 'the field tblActivity.ActivityID could refer to more than one field in your SQL Statement. I assume that's because qry1500 includes tblActivity as a source table and so does my update query. Anyway, I was wondering if there's a different bit of SQL I might use, or if you have any other suggestions for workarounds, or if I'm just stuck with a VERY slow running update

Note: qry1500 runs fairly fast.
 
"Vel" wrote
I have an update query, but would like to limit the values to be updated to include
Click to expand...
only those values which appear in another query. Now, I can do this with an In
(Select ...) statement in criteria, but for some reason it runs EXTREMELY slowly that
way.
Here's the deal:
I want to update tblActivity.PrintedOn to Now()
I only want to do so if tblActivity.ActivityID = qryHCFA1500.ActivityID

Here's what I have
UPDATE tblActivity SET tblActivity.PrintedOn = Now()
WHERE (((tblActivity.PrintedOn) Is Null) AND ((tblActivity.ActivityID) In (SELECT
Click to expand...
qry1500.ActivityID FROM qry1500)));
Now, originally I had In (SELECT qry1500.ActivityID FROM qry1500 WHERE
Click to expand...
qry1500.ActivityID=tblActivity.ActivityID) but that gave me a 'the field
tblActivity.ActivityID could refer to more than one field in your SQL Statement. I
assume that's because qry1500 includes tblActivity as a source table and so does my
update query. Anyway, I was wondering if there's a different bit of SQL I might use,
or if you have any other suggestions for workarounds, or if I'm just stuck with a
VERY slow running update.
Note: qry1500 runs fairly fast.
Click to expand...

Hi Vel,

It probably would help if you provided
the SQL for qryHCFA1500.

Does the following give you the same error?

UPDATE tblActivity INNER JOIN qryHCFA As q
ON tblActivity.ActivityID=q.ActivityID
SET tblActivity.PrintedOn = Now()
WHERE ((tblActivity.PrintedOn) Is Null);

or does qryHCFA1500 use aggregates/distinct
that makes unupdateable?

Knowing qryHCFA1500 would help if it
is not too much bother.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
qry1500, which I mistakenly referred to as qryHCFA1500 at one point in time, is unupdateable. In an effort to make my report based on a single query as opposed to using subreports, etc., that particular query is very large and has many relationships which, although they look sensible in the design grid, look rather convoluted and confusing in SQL. I have attached the SQL of the query below, but I assume many here will find it confusing and overcomplicated..

SELECT tblProcedure.ProcCode, tblProcedure.Cost, qryActivityAuth.AuthNo, tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge, tblActivity.ProcID, tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode, tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo, tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID, tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation, tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender, tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID, tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID, tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage, tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2, tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2, tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital, tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC, tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID, tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer, tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident, tblClient.CarState, [tblClient].[lastname] & " " & [tblClient].[firstname] & " " & Left([middle],1) AS ClientName, [tblProvider].[Firstname] & " " & [tblProvider].[lastname] AS Provider, tblInsurance.Address AS InsAdd, tblInsurance.Address2 AS InsAdd2, [tblInsurance].[City] & ", " & [tblinsurance].[state] & " " & [tblinsurance].[zip] AS insCityStateZip, tblActivity.TimeIn, tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn, tblClientIns.insuranceID, Sum(IIf(IsNull([payment]),0,[payment]))+[clientamt] AS TotalPaid, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]) AS ClientAmt, IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)) AS [Procedure], tblInsurance.Title AS Insurance, IIf(Len([tblActivity].[ProcID])>5 And Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" Or [tblactivity].[ProcID]="90801","","AH")) AS Modifie
FROM tblProvider INNER JOIN (tblInsurance INNER JOIN (((tblClient INNER JOIN tblDiagnosis ON tblClient.DiagnosisID = tblDiagnosis.DiagnosisID) INNER JOIN (tblProcedure INNER JOIN ((tblActivity LEFT JOIN qryActivityAuth ON (tblActivity.Type = qryActivityAuth.AuthBy) AND (tblActivity.ActivityID = qryActivityAuth.ActivityID)) INNER JOIN tblClientIns ON tblActivity.InsuranceID = tblClientIns.insuranceID) ON tblProcedure.ProcID = tblActivity.ProcID) ON (tblClient.ClientID = tblClientIns.clientID) AND (tblClient.ClientID = tblActivity.ClientID)) LEFT JOIN tblPayments ON tblActivity.ActivityID = tblPayments.ActivityID) ON tblInsurance.InsuranceID = tblClientIns.insuranceID) ON tblProvider.ProviderID = tblActivity.ProviderI
WHERE (((tblActivity.InsuranceID)<>"BCBS" And (tblActivity.InsuranceID)<>"AAM" And (tblActivity.InsuranceID)<>"MD") AND ((tblActivity.ProcID) Not Like "M*" And (tblActivity.ProcID) Not Like "Pc*")
GROUP BY tblProcedure.ProcCode, tblProcedure.Cost, qryActivityAuth.AuthNo, tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge, tblActivity.ProcID, tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode, tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo, tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID, tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation, tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender, tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID, tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID, tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage, tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2, tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2, tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital, tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC, tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID, tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer, tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident, tblClient.CarState, [tblClient].[lastname] & " " & [tblClient].[firstname] & " " & Left([middle],1), [tblProvider].[Firstname] & " " & [tblProvider].[lastname], tblInsurance.Address, tblInsurance.Address2, [tblInsurance].[City] & ", " & [tblinsurance].[state] & " " & [tblinsurance].[zip], tblActivity.TimeIn, tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn, tblClientIns.insuranceID, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]), IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)), tblInsurance.Title, IIf(Len([tblActivity].[ProcID])>5 And Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" Or [tblactivity].[ProcID]="90801","","AH")
HAVING (((tblActivity.Type)="PP") AND ((tblActivity.PrintedOn) Is Null) AND ((tblClientIns.insuranceID)<>"BCBS" And (tblClientIns.insuranceID)<>"AAM" And (tblClientIns.insuranceID)<>"MD"));


----- Gary Walter wrote: -----


"Vel" wrote
I have an update query, but would like to limit the values to be updated to include
Click to expand...
only those values which appear in another query. Now, I can do this with an In
(Select ...) statement in criteria, but for some reason it runs EXTREMELY slowly that
way.
I want to update tblActivity.PrintedOn to Now()
I only want to do so if tblActivity.ActivityID = qryHCFA1500.ActivityID
UPDATE tblActivity SET tblActivity.PrintedOn = Now()
WHERE (((tblActivity.PrintedOn) Is Null) AND ((tblActivity.ActivityID) In (SELECT
Click to expand...
qry1500.ActivityID FROM qry1500)));qry1500.ActivityID=tblActivity.ActivityID) but that gave me a 'the field
tblActivity.ActivityID could refer to more than one field in your SQL Statement. I
assume that's because qry1500 includes tblActivity as a source table and so does my
update query. Anyway, I was wondering if there's a different bit of SQL I might use,
or if you have any other suggestions for workarounds, or if I'm just stuck with a
VERY slow running update.
Hi Vel,

It probably would help if you provided
the SQL for qryHCFA1500.

Does the following give you the same error?

UPDATE tblActivity INNER JOIN qryHCFA As q
ON tblActivity.ActivityID=q.ActivityID
SET tblActivity.PrintedOn = Now()
WHERE ((tblActivity.PrintedOn) Is Null);

or does qryHCFA1500 use aggregates/distinct
that makes unupdateable?

Knowing qryHCFA1500 would help if it
is not too much bother.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Hi Vel,

Whew!!

My only suggestion is to have a separate table
that you delete all records in, then append ActivityId
from qry1500 (when you want to run update),
then use this table in INNER JOIN of update
query.

I believe this will be "faster." I could be wrong.

IN is notoriously slow.

Good luck,

Gary Walter
qry1500, which I mistakenly referred to as qryHCFA1500 at one point in time,
Click to expand...
is unupdateable. In an effort to make my report based on a single query as opposed
to using subreports, etc., that particular query is very large and has many
relationships which, although they look sensible in the design grid, look rather
convoluted and confusing in SQL. I have attached the SQL of the query below, but I
assume many here will find it confusing and overcomplicated...
SELECT tblProcedure.ProcCode, tblProcedure.Cost, qryActivityAuth.AuthNo,
Click to expand...
tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge, tblActivity.ProcID,
tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode,
tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo,
tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID,
tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation,
tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender,
tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID,
tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID,
tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage,
tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2,
tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2,
tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital,
tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC,
tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID,
tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer,
tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident,
tblClient.CarState, [tblClient].[lastname] & " " & [tblClient].[firstname] & " "
& Left([middle],1) AS ClientName, [tblProvider].[Firstname] & " " &
[tblProvider].[lastname] AS Provider, tblInsurance.Address AS InsAdd,
tblInsurance.Address2 AS InsAdd2, [tblInsurance].[City] & ", " &
[tblinsurance].[state] & " " & [tblinsurance].[zip] AS insCityStateZip,
tblActivity.TimeIn, tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn,
tblClientIns.insuranceID, Sum(IIf(IsNull([payment]),0,[payment]))+[clientamt] AS
TotalPaid, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]) AS ClientAmt,
IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)) AS [Procedure],
tblInsurance.Title AS Insurance, IIf(Len([tblActivity].[ProcID])>5 And
Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" Or
[tblactivity].[ProcID]="90801","","AH")) AS Modifier
FROM tblProvider INNER JOIN (tblInsurance INNER JOIN (((tblClient INNER JOIN
Click to expand...
tblDiagnosis ON tblClient.DiagnosisID = tblDiagnosis.DiagnosisID) INNER JOIN
(tblProcedure INNER JOIN ((tblActivity LEFT JOIN qryActivityAuth ON (tblActivity.Type
= qryActivityAuth.AuthBy) AND (tblActivity.ActivityID = qryActivityAuth.ActivityID))
INNER JOIN tblClientIns ON tblActivity.InsuranceID = tblClientIns.insuranceID) ON
tblProcedure.ProcID = tblActivity.ProcID) ON (tblClient.ClientID =
tblClientIns.clientID) AND (tblClient.ClientID = tblActivity.ClientID)) LEFT JOIN
tblPayments ON tblActivity.ActivityID = tblPayments.ActivityID) ON
tblInsurance.InsuranceID = tblClientIns.insuranceID) ON tblProvider.ProviderID =
tblActivity.ProviderID
WHERE (((tblActivity.InsuranceID)<>"BCBS" And (tblActivity.InsuranceID)<>"AAM" And
Click to expand...
(tblActivity.InsuranceID) said:
GROUP BY tblProcedure.ProcCode, tblProcedure.Cost, qryActivityAuth.AuthNo,
Click to expand...
tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge, tblActivity.ProcID,
tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode,
tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo,
tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID,
tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation,
tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender,
tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID,
tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID,
tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage,
tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2,
tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2,
tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital,
tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC,
tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID,
tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer,
tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident,
tblClient.CarState, [tblClient].[lastname] & " " & [tblClient].[firstname] & " "
& Left([middle],1), [tblProvider].[Firstname] & " " & [tblProvider].[lastname],
tblInsurance.Address, tblInsurance.Address2, [tblInsurance].[City] & ", " &
[tblinsurance].[state] & " " & [tblinsurance].[zip], tblActivity.TimeIn,
tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn,
tblClientIns.insuranceID, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]),
IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)), tblInsurance.Title,
IIf(Len([tblActivity].[ProcID])>5 And
Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" Or
[tblactivity].[ProcID]="90801","","AH"))
 
or just delete/append qry1500 to a report table
and use this report table in INNER JOIN
of update query....

this may help performance of report as well...


"Gary Walter" wrote
My only suggestion is to have a separate table
that you delete all records in, then append ActivityId
from qry1500 (when you want to run update),
then use this table in INNER JOIN of update
query.

I believe this will be "faster." I could be wrong.

IN is notoriously slow.

qry1500, which I mistakenly referred to as qryHCFA1500 at one point in time,
Click to expand...
is unupdateable. In an effort to make my report based on a single query as opposed
to using subreports, etc., that particular query is very large and has many
relationships which, although they look sensible in the design grid, look rather
convoluted and confusing in SQL. I have attached the SQL of the query below, but I
assume many here will find it confusing and overcomplicated...
SELECT tblProcedure.ProcCode, tblProcedure.Cost, qryActivityAuth.AuthNo,
Click to expand...
tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge, tblActivity.ProcID,
tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode,
tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo,
tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID,
tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation,
tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender,
tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID,
tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID,
tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage,
tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2,
tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2,
tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital,
tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC,
tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID,
tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer,
tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident,
tblClient.CarState, [tblClient].[lastname] & " " & [tblClient].[firstname] & " "
& Left([middle],1) AS ClientName, [tblProvider].[Firstname] & " " &
[tblProvider].[lastname] AS Provider, tblInsurance.Address AS InsAdd,
tblInsurance.Address2 AS InsAdd2, [tblInsurance].[City] & ", " &
[tblinsurance].[state] & " " & [tblinsurance].[zip] AS insCityStateZip,
tblActivity.TimeIn, tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn,
tblClientIns.insuranceID, Sum(IIf(IsNull([payment]),0,[payment]))+[clientamt] AS
TotalPaid, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]) AS ClientAmt,
IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)) AS [Procedure],
tblInsurance.Title AS Insurance, IIf(Len([tblActivity].[ProcID])>5 And
Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" Or
[tblactivity].[ProcID]="90801","","AH")) AS Modifier
FROM tblProvider INNER JOIN (tblInsurance INNER JOIN (((tblClient INNER JOIN
Click to expand...
tblDiagnosis ON tblClient.DiagnosisID = tblDiagnosis.DiagnosisID) INNER JOIN
(tblProcedure INNER JOIN ((tblActivity LEFT JOIN qryActivityAuth ON (tblActivity.Type
= qryActivityAuth.AuthBy) AND (tblActivity.ActivityID = qryActivityAuth.ActivityID))
INNER JOIN tblClientIns ON tblActivity.InsuranceID = tblClientIns.insuranceID) ON
tblProcedure.ProcID = tblActivity.ProcID) ON (tblClient.ClientID =
tblClientIns.clientID) AND (tblClient.ClientID = tblActivity.ClientID)) LEFT JOIN
tblPayments ON tblActivity.ActivityID = tblPayments.ActivityID) ON
tblInsurance.InsuranceID = tblClientIns.insuranceID) ON tblProvider.ProviderID =
tblActivity.ProviderID
WHERE (((tblActivity.InsuranceID)<>"BCBS" And (tblActivity.InsuranceID)<>"AAM"
Click to expand...
And
(tblActivity.InsuranceID) said:
GROUP BY tblProcedure.ProcCode, tblProcedure.Cost, qryActivityAuth.AuthNo,
Click to expand...
tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge, tblActivity.ProcID,
tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode,
tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo,
tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID,
tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation,
tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender,
tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID,
tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID,
tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage,
tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2,
tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2,
tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital,
tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC,
tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID,
tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer,
tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident,
tblClient.CarState, [tblClient].[lastname] & " " & [tblClient].[firstname] & " "
& Left([middle],1), [tblProvider].[Firstname] & " " & [tblProvider].[lastname],
tblInsurance.Address, tblInsurance.Address2, [tblInsurance].[City] & ", " &
[tblinsurance].[state] & " " & [tblinsurance].[zip], tblActivity.TimeIn,
tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn,
tblClientIns.insuranceID, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]),
IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)),
Click to expand...
tblInsurance.Title,
IIf(Len([tblActivity].[ProcID])>5 And
Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" Or
[tblactivity].[ProcID]="90801","","AH"))
HAVING (((tblActivity.Type)="PP") AND ((tblActivity.PrintedOn) Is Null) AND
Click to expand...
((tblClientIns.insuranceID) said:
----- Gary Walter wrote: -----


"Vel" wrote
Click to expand...
to
include
only those values which appear in another query. Now, I can do this with an In
(Select ...) statement in criteria, but for some reason it runs EXTREMELY slowly that
way. (SELECT
qry1500.ActivityID FROM qry1500)));
qry1500.ActivityID=tblActivity.ActivityID) but that gave me a 'the field
tblActivity.ActivityID could refer to more than one field in your SQL Statement. I
assume that's because qry1500 includes tblActivity as a source table and so does my
update query. Anyway, I was wondering if there's a different bit of SQL I might use,
or if you have any other suggestions for workarounds, or if I'm just stuck with a
VERY slow running update.

Hi Vel,

It probably would help if you provided
the SQL for qryHCFA1500.

Does the following give you the same error?

UPDATE tblActivity INNER JOIN qryHCFA As q
ON tblActivity.ActivityID=q.ActivityID
SET tblActivity.PrintedOn = Now()
WHERE ((tblActivity.PrintedOn) Is Null);

or does qryHCFA1500 use aggregates/distinct
that makes unupdateable?

Knowing qryHCFA1500 would help if it
is not too much bother.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Gary

Thanks for your help. Using your previous idea, I've created a make-table query which holds the data from qry1500 and then used my update query with a simple inner join from the new table (called tempQry1500) with tblActivity. With a bit of code I turn off warnings (to avoid that pesky warning about deleting the old temporary table), run the two queries, and it's all completed in a second or two

Vel

----- Gary Walter wrote: ----

or just delete/append qry1500 to a report tabl
and use this report table in INNER JOI
of update query...

this may help performance of report as well..


"Gary Walter" wrot
My only suggestion is to have a separate tabl
Click to expand...
that you delete all records in, then append ActivityI
from qry1500 (when you want to run update)
then use this table in INNER JOIN of updat
query
I believe this will be "faster." I could be wrong
IN is notoriously slow
Click to expand...
is unupdateable. In an effort to make my report based on a single query as oppose
to using subreports, etc., that particular query is very large and has man
relationships which, although they look sensible in the design grid, look rathe
convoluted and confusing in SQL. I have attached the SQL of the query below, but
assume many here will find it confusing and overcomplicated..tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge tblActivity.ProcID
tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode
tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo
tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID
tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation
tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender
tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID
tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID
tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage
tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2
tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2
tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital
tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC
tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID
tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer
tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident
tblClient.CarState, [tblClient].[lastname] & " " & [tblClient].[firstname] &
Click to expand...
& Left([middle],1) AS ClientName, [tblProvider].[Firstname] & " " &> [tblProvider].[lastname] AS Provider, tblInsurance.Address AS InsAdd
tblInsurance.Address2 AS InsAdd2, [tblInsurance].[City] & ", " &> [tblinsurance].[state] & " " & [tblinsurance].[zip] AS insCityStateZip
tblActivity.TimeIn, tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn
tblClientIns.insuranceID, Sum(IIf(IsNull([payment]),0,[payment]))+[clientamt] A
TotalPaid, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]) AS ClientAmt
IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)) AS [Procedure]
tblInsurance.Title AS Insurance, IIf(Len([tblActivity].[ProcID])>5 An
Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" O
[tblactivity].[ProcID]="90801","","AH")) AS Modifie
FROM tblProvider INNER JOIN (tblInsurance INNER JOIN (((tblClient INNER JOI
Click to expand...
tblDiagnosis ON tblClient.DiagnosisID = tblDiagnosis.DiagnosisID) INNER JOI
(tblProcedure INNER JOIN ((tblActivity LEFT JOIN qryActivityAuth O (tblActivity.Type
= qryActivityAuth.AuthBy) AND (tblActivity.ActivityID = qryActivityAuth.ActivityID))
INNER JOIN tblClientIns ON tblActivity.InsuranceID = tblClientIns.insuranceID) ON
tblProcedure.ProcID = tblActivity.ProcID) ON (tblClient.ClientID =
tblClientIns.clientID) AND (tblClient.ClientID = tblActivity.ClientID)) LEFT JOIN
tblPayments ON tblActivity.ActivityID = tblPayments.ActivityID) ON
tblInsurance.InsuranceID = tblClientIns.insuranceID) ON tblProvider.ProviderID =
tblActivity.ProviderID
WHERE (((tblActivity.InsuranceID)<>"BCBS" And (tblActivity.InsuranceID)<>"AAM"
Click to expand...
And
(tblActivity.InsuranceID) said:
GROUP BY tblProcedure.ProcCode, tblProcedure.Cost, qryActivityAuth.AuthNo,
Click to expand...
tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge, tblActivity.ProcID,
tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode,
tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo,
tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID,
tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation,
tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender,
tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID,
tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID,
tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage,
tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2,
tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2,
tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital,
tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC,
tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID,
tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer,
tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident,
tblClient.CarState, [tblClient].[lastname] & " " & [tblClient].[firstname] & " "
& Left([middle],1), [tblProvider].[Firstname] & " " & [tblProvider].[lastname],
tblInsurance.Address, tblInsurance.Address2, [tblInsurance].[City] & ", " &> [tblinsurance].[state] & " " & [tblinsurance].[zip], tblActivity.TimeIn,
tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn,
tblClientIns.insuranceID, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]),
IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)),
Click to expand...
tblInsurance.Title,
IIf(Len([tblActivity].[ProcID])>5 And
Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" Or
[tblactivity].[ProcID]="90801","","AH"))
HAVING (((tblActivity.Type)="PP") AND ((tblActivity.PrintedOn) Is Null) AND
Click to expand...
to
include
only those values which appear in another query. Now, I can do this with an In
(Select ...) statement in criteria, but for some reason it runs EXTREMELY slowly that
way. (SELECT
qry1500.ActivityID FROM qry1500)));
qry1500.ActivityID=tblActivity.ActivityID) but that gave me a 'the field
tblActivity.ActivityID could refer to more than one field in your SQL Statement. I
assume that's because qry1500 includes tblActivity as a source table and so does my
update query. Anyway, I was wondering if there's a different bit of SQL I might use,
or if you have any other suggestions for workarounds, or if I'm just stuck with a
VERY slow running update.
ON tblActivity.ActivityID=q.ActivityID
SET tblActivity.PrintedOn = Now()
WHERE ((tblActivity.PrintedOn) Is Null);
Click to expand...
Click to expand...
 
Hi Vel,

Thank you for the update.

As a general rule, it is better to
use append than make table query.

You now already have a table with
the same number and type of fields
so you could use a simple bulk append.

(with a reference set to DAO) your code
might look like:

CurrentDb.Execute "DELETE * FROM tempQry1500",dbFailOnError
CurrentDb.Execute "INSERT INTO tempQry1500 " _
& "SELECT qry1500.* FROM qry1500;", dbFailOnError

Pretty simple. No worry about warnings, whether table exists, etc.

Gary Walter

Thanks for your help. Using your previous idea, I've created a make-table query
Click to expand...
which holds the data from qry1500 and then used my update query with a simple inner
join from the new table (called tempQry1500) with tblActivity. With a bit of code I
turn off warnings (to avoid that pesky warning about deleting the old temporary
table), run the two queries, and it's all completed in a second or two.
Vel.

----- Gary Walter wrote: -----

or just delete/append qry1500 to a report table
and use this report table in INNER JOIN
of update query....

this may help performance of report as well...


"Gary Walter" wrote
My only suggestion is to have a separate table
Click to expand...
that you delete all records in, then append ActivityId
from qry1500 (when you want to run update),
then use this table in INNER JOIN of update
query.
I believe this will be "faster." I could be wrong.
IN is notoriously slow.
qry1500, which I mistakenly referred to as qryHCFA1500 at one point
Click to expand...
in time,
is unupdateable. In an effort to make my report based on a single query as opposed
to using subreports, etc., that particular query is very large and has many
relationships which, although they look sensible in the design grid, look rather
convoluted and confusing in SQL. I have attached the SQL of the query below, but I
assume many here will find it confusing and overcomplicated...
SELECT tblProcedure.ProcCode, tblProcedure.Cost, qryActivityAuth.AuthNo,
Click to expand...
tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge, tblActivity.ProcID,
tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode,
tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo,
tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID,
tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation,
tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender,
tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID,
tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID,
tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage,
tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2,
tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2,
tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital,
tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC,
tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID,
tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer,
tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident,
tblClient.CarState, [tblClient].[lastname] & " " &
Click to expand...
[tblClient].[firstname] & "
"
& Left([middle],1) AS ClientName, [tblProvider].[Firstname] & " " &> [tblProvider].[lastname] AS Provider, tblInsurance.Address AS InsAdd,
tblInsurance.Address2 AS InsAdd2, [tblInsurance].[City] & ", " &> [tblinsurance].[state] & " " & [tblinsurance].[zip] AS insCityStateZip,
tblActivity.TimeIn, tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn,
tblClientIns.insuranceID, Sum(IIf(IsNull([payment]),0,[payment]))+[clientamt] AS
TotalPaid, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]) AS ClientAmt,
IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)) AS [Procedure],
tblInsurance.Title AS Insurance, IIf(Len([tblActivity].[ProcID])>5 And
Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" Or
[tblactivity].[ProcID]="90801","","AH")) AS Modifier
FROM tblProvider INNER JOIN (tblInsurance INNER JOIN (((tblClient INNER
Click to expand...
JOIN
tblDiagnosis ON tblClient.DiagnosisID = tblDiagnosis.DiagnosisID) INNER JOIN
(tblProcedure INNER JOIN ((tblActivity LEFT JOIN qryActivityAuth ON (tblActivity.Type
= qryActivityAuth.AuthBy) AND (tblActivity.ActivityID = qryActivityAuth.ActivityID))
INNER JOIN tblClientIns ON tblActivity.InsuranceID = tblClientIns.insuranceID) ON
tblProcedure.ProcID = tblActivity.ProcID) ON (tblClient.ClientID =
tblClientIns.clientID) AND (tblClient.ClientID = tblActivity.ClientID)) LEFT JOIN
tblPayments ON tblActivity.ActivityID = tblPayments.ActivityID) ON
tblInsurance.InsuranceID = tblClientIns.insuranceID) ON tblProvider.ProviderID =
tblActivity.ProviderID
WHERE (((tblActivity.InsuranceID)<>"BCBS" And
Click to expand...
Click to expand...
(tblActivity.InsuranceID) said:
And
(tblActivity.InsuranceID) said:
GROUP BY tblProcedure.ProcCode, tblProcedure.Cost, qryActivityAuth.AuthNo,
Click to expand...
tblClient.ClientID, tblActivity.Quantity, tblActivity.ProcCharge, tblActivity.ProcID,
tblActivity.DateOf, tblClientIns.GroupNo, tblDiagnosis.LegalCode,
tblClientIns.ClientNo, tblInsurance.Title, tblClientIns.CardNo,
tblActivity.ProviderID, tblClientIns.MiscNo, tblClientIns.CoverageID,
tblClientIns.FormType, tblClientIns.Primary, tblClientIns.Relation,
tblClientIns.InsuredName, tblClientIns.InsuredDOB, tblClientIns.InsuredGender,
tblClientIns.InsuredEmployer, tblClient.ReferralID, tblClient.CoverageID,
tblClient.RespPartyID, tblClient.ThirdID, tblClient.DiagnosisID,
tblClient.IntakeDate, tblClient.LastName, tblClient.FirstName, tblClient.Lineage,
tblActivity.ActivityID, tblClient.Middle, tblClient.Address, tblClient.Address2,
tblClient.City, tblClient.State, tblClient.ZIP, tblClient.Phone, tblClient.Phone2,
tblClient.SSN, tblClient.Race, tblClient.Gender, tblClient.Marital,
tblClient.Entered, tblClient.DOB, tblClient.Project, tblClient.MDOC,
tblClient.DomVio, tblClient.TriCapp, tblClient.DVR, tblClient.FacilityID,
tblClient.SCCC, tblClient.OldID, tblClient.Employed, tblClient.Employer,
tblClient.Occupation, tblClient.CarAccident, tblClient.Work, tblClient.Accident,
tblClient.CarState, [tblClient].[lastname] & " " &
Click to expand...
[tblClient].[firstname] & "
"
& Left([middle],1), [tblProvider].[Firstname] & " " & [tblProvider].[lastname],
tblInsurance.Address, tblInsurance.Address2, [tblInsurance].[City] & ", " &> [tblinsurance].[state] & " " & [tblinsurance].[zip], tblActivity.TimeIn,
tblActivity.Type, tblProvider.CredSign, tblActivity.PrintedOn,
tblClientIns.insuranceID, IIf([clientpay]<[clientcharge],[clientcharge],[clientpay]),
IIf([tblactivity].[ProcID]="908012","H0031",Left([procCode],5)),
Click to expand...
tblInsurance.Title,
IIf(Len([tblActivity].[ProcID])>5 And
Right([tblactivity].[ProcID],1)="2","AJ",IIf([tblactivity].[ProcID]="90862" Or
[tblactivity].[ProcID]="90801","","AH"))
HAVING (((tblActivity.Type)="PP") AND ((tblActivity.PrintedOn) Is Null) AND
Click to expand...
((tblClientIns.insuranceID)<>"BCBS" And (tblClientIns.insuranceID)<>"AAM" And
(tblClientIns.insuranceID) said:
----- Gary Walter wrote: -----
"Vel" wrote
I have an update query, but would like to limit the values to be updated
Click to expand...
to
include
only those values which appear in another query. Now, I can do this
Click to expand...
with an
In
(Select ...) statement in criteria, but for some reason it runs
Click to expand...
EXTREMELY
slowly that
way.
Here's the deal:
I want to update tblActivity.PrintedOn to Now()
I only want to do so if tblActivity.ActivityID = qryHCFA1500.ActivityID
Here's what I have
UPDATE tblActivity SET tblActivity.PrintedOn = Now()
WHERE (((tblActivity.PrintedOn) Is Null) AND ((tblActivity.ActivityID) In (SELECT
qry1500.ActivityID FROM qry1500)));
Now, originally I had In (SELECT qry1500.ActivityID FROM qry1500 WHERE
qry1500.ActivityID=tblActivity.ActivityID) but that gave me a 'the field
tblActivity.ActivityID could refer to more than one field in your SQL Statement. I
assume that's because qry1500 includes tblActivity as a source table
Click to expand...
and so
does my
update query. Anyway, I was wondering if there's a different bit of
Click to expand...
SQL I
might use,
or if you have any other suggestions for workarounds, or if I'm just
Click to expand...
stuck
with a
VERY slow running update.
Note: qry1500 runs fairly fast.
Hi Vel,
It probably would help if you provided
the SQL for qryHCFA1500.
Does the following give you the same error?
UPDATE tblActivity INNER JOIN qryHCFA As q
ON tblActivity.ActivityID=q.ActivityID
SET tblActivity.PrintedOn = Now()
WHERE ((tblActivity.PrintedOn) Is Null);
or does qryHCFA1500 use aggregates/distinct
that makes unupdateable?
Knowing qryHCFA1500 would help if it
is not too much bother.
Please respond back if I have misunderstood.
Good luck,
Gary Walter
Click to expand...
Click to expand...
Click to expand...
 
Thanks Again

Worked like a charm and is quicker than the MakeTable query anyway. Although they are both significantly faster than the In() function.

Vel

----- Gary Walter wrote: ----

Hi Vel

Thank you for the update

As a general rule, it is better t
use append than make table query

You now already have a table wit
the same number and type of field
so you could use a simple bulk append

(with a reference set to DAO) your cod
might look like

CurrentDb.Execute "DELETE * FROM tempQry1500",dbFailOnErro
CurrentDb.Execute "INSERT INTO tempQry1500 "
& "SELECT qry1500.* FROM qry1500;", dbFailOnErro

Pretty simple. No worry about warnings, whether table exists, etc

Gary Walte

"Vel." wrotwhich holds the data from qry1500 and then used my update query with a simple inne
join from the new table (called tempQry1500) with tblActivity. With a bit of code
turn off warnings (to avoid that pesky warning about deleting the old temporar
table), run the two queries, and it's all completed in a second or two
and use this report table in INNER JOI
of update query...
Click to expand...
rathe
..
 
Back
Top