Update Queries using queries

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.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 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
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
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.

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 Modifier
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.ProviderID
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
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
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.

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,
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 Modifier
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.ProviderID
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
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
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.

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

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,
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 Modifier
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.ProviderID
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
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
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.

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

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 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,
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 Modifier
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.ProviderID
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
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
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.
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,

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

"Vel." wrote

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 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,
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 Modifier
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.ProviderID
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
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
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.
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

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 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

"Vel." wrote
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 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,
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
...