Can a Table be Updated From a Subquery with Aggregate Count Value?

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I have two tables, one of which has a field (AuditRptTbl.AuditRptYesCount)
that I want to update with the Count of "Yes" values in another table
(AuditDetailTreatmentTbl.AuditDtlCnt). I've constructed two queries, one of
which counts the values and works ok, and the other query will update a
specific column ok. Is this possible or is there another way to provide the
same functionality?
However, I've tried several different ways to combine the two into a
subquery to update my result field with the Count but receive different
errors depending on what I try. The queries are below.
Thanks for any help or direction that you can provide.
Terryomsn

Query 1
SELECT Count(AuditDetailTreatmentTbl.AuditDtlCnt) AS [Number of Counts],
AuditDetailTreatmentTbl.Medicare, AuditDetailTreatmentTbl.PhysicianOrdPres
FROM AuditDetailTreatmentTbl
GROUP BY AuditDetailTreatmentTbl.Medicare,
AuditDetailTreatmentTbl.PhysicianOrdPres
HAVING (((AuditDetailTreatmentTbl.Medicare)=On) AND
((AuditDetailTreatmentTbl.PhysicianOrdPres)="Yes"));

Query 2
UPDATE AuditRptTbl SET AuditRptTbl.AuditRptYesCount = null
WHERE (((AuditRptTbl.AuditRptID)=1));
 
Cannot be done that way in Access.
One solution is to use the DCOUNT function.

Beyond that it is difficult to give you further advice since I don't know HOW
AuditRptTbl records are related to AuditDetailTreatmentTbl and the types of
fields involved. GUESSING that AudRptID is a number and exists in both
tables, I would say you need something like the following.

UPDATE AuditRptTbl
SET AuditRptTbl.AuditRptYesCount =
DCOUNT("*","AuditDetailTreatmentTbl","Medicare=On AND
PhysicianOrdPres='Yes' AND [SomeFIeld in AuditDetailTreatmentTbl]=" & AudrptID)
WHERE AuditRptTbl.AuditRptID=1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
AuditRptTbl is a reporting table only and currently has no relationships with
any other tables. Its purpose in life is to hold a count for a specific
report total line. For example,
AuditRptID 1, AuditQuestion "Number of Failed Operations", Count, (count of
a number of failure rows in a separate table)
AuditRptID 2, etc.

John Spencer said:
Cannot be done that way in Access.
One solution is to use the DCOUNT function.

Beyond that it is difficult to give you further advice since I don't know HOW
AuditRptTbl records are related to AuditDetailTreatmentTbl and the types of
fields involved. GUESSING that AudRptID is a number and exists in both
tables, I would say you need something like the following.

UPDATE AuditRptTbl
SET AuditRptTbl.AuditRptYesCount =
DCOUNT("*","AuditDetailTreatmentTbl","Medicare=On AND
PhysicianOrdPres='Yes' AND [SomeFIeld in AuditDetailTreatmentTbl]=" & AudrptID)
WHERE AuditRptTbl.AuditRptID=1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have two tables, one of which has a field (AuditRptTbl.AuditRptYesCount)
that I want to update with the Count of "Yes" values in another table
(AuditDetailTreatmentTbl.AuditDtlCnt). I've constructed two queries, one of
which counts the values and works ok, and the other query will update a
specific column ok. Is this possible or is there another way to provide the
same functionality?
However, I've tried several different ways to combine the two into a
subquery to update my result field with the Count but receive different
errors depending on what I try. The queries are below.
Thanks for any help or direction that you can provide.
Terryomsn

Query 1
SELECT Count(AuditDetailTreatmentTbl.AuditDtlCnt) AS [Number of Counts],
AuditDetailTreatmentTbl.Medicare, AuditDetailTreatmentTbl.PhysicianOrdPres
FROM AuditDetailTreatmentTbl
GROUP BY AuditDetailTreatmentTbl.Medicare,
AuditDetailTreatmentTbl.PhysicianOrdPres
HAVING (((AuditDetailTreatmentTbl.Medicare)=On) AND
((AuditDetailTreatmentTbl.PhysicianOrdPres)="Yes"));

Query 2
UPDATE AuditRptTbl SET AuditRptTbl.AuditRptYesCount = null
WHERE (((AuditRptTbl.AuditRptID)=1));
Click to expand...
.
Click to expand...
 
Well, if you are updating the table then you are going to need to use DCOUNT
(and possibly its sister functions DAvg, DSum, DMin, and DMax).

If you choose to INSERT NEW RECORDS then you can use an insert query with
subqueries.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
AuditRptTbl is a reporting table only and currently has no relationships with
any other tables. Its purpose in life is to hold a count for a specific
report total line. For example,
AuditRptID 1, AuditQuestion "Number of Failed Operations", Count, (count of
a number of failure rows in a separate table)
AuditRptID 2, etc.

John Spencer said:
Cannot be done that way in Access.
One solution is to use the DCOUNT function.

Beyond that it is difficult to give you further advice since I don't know HOW
AuditRptTbl records are related to AuditDetailTreatmentTbl and the types of
fields involved. GUESSING that AudRptID is a number and exists in both
tables, I would say you need something like the following.

UPDATE AuditRptTbl
SET AuditRptTbl.AuditRptYesCount =
DCOUNT("*","AuditDetailTreatmentTbl","Medicare=On AND
PhysicianOrdPres='Yes' AND [SomeFIeld in AuditDetailTreatmentTbl]=" & AudrptID)
WHERE AuditRptTbl.AuditRptID=1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have two tables, one of which has a field (AuditRptTbl.AuditRptYesCount)
that I want to update with the Count of "Yes" values in another table
(AuditDetailTreatmentTbl.AuditDtlCnt). I've constructed two queries, one of
which counts the values and works ok, and the other query will update a
specific column ok. Is this possible or is there another way to provide the
same functionality?
However, I've tried several different ways to combine the two into a
subquery to update my result field with the Count but receive different
errors depending on what I try. The queries are below.
Thanks for any help or direction that you can provide.
Terryomsn

Query 1
SELECT Count(AuditDetailTreatmentTbl.AuditDtlCnt) AS [Number of Counts],
AuditDetailTreatmentTbl.Medicare, AuditDetailTreatmentTbl.PhysicianOrdPres
FROM AuditDetailTreatmentTbl
GROUP BY AuditDetailTreatmentTbl.Medicare,
AuditDetailTreatmentTbl.PhysicianOrdPres
HAVING (((AuditDetailTreatmentTbl.Medicare)=On) AND
((AuditDetailTreatmentTbl.PhysicianOrdPres)="Yes"));

Query 2
UPDATE AuditRptTbl SET AuditRptTbl.AuditRptYesCount = null
WHERE (((AuditRptTbl.AuditRptID)=1));
Click to expand...
.
Click to expand...
Click to expand...
 
Using the following query and it worked great. Thanks very much for your help.

UPDATE AuditRptTbl
SET AuditRptTbl.AuditRptYesCount =
DCount("AuditDtlCnt","AuditDetailInitialEval","Medicare=On AND
AuditDetailInitialEval.PhysicianOrderPresent='Yes'")
WHERE AuditRptTbl.AuditRptID=1

Terryomsn

John Spencer said:
Cannot be done that way in Access.
One solution is to use the DCOUNT function.

Beyond that it is difficult to give you further advice since I don't know HOW
AuditRptTbl records are related to AuditDetailTreatmentTbl and the types of
fields involved. GUESSING that AudRptID is a number and exists in both
tables, I would say you need something like the following.

UPDATE AuditRptTbl
SET AuditRptTbl.AuditRptYesCount =
DCOUNT("*","AuditDetailTreatmentTbl","Medicare=On AND
PhysicianOrdPres='Yes' AND [SomeFIeld in AuditDetailTreatmentTbl]=" & AudrptID)
WHERE AuditRptTbl.AuditRptID=1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have two tables, one of which has a field (AuditRptTbl.AuditRptYesCount)
that I want to update with the Count of "Yes" values in another table
(AuditDetailTreatmentTbl.AuditDtlCnt). I've constructed two queries, one of
which counts the values and works ok, and the other query will update a
specific column ok. Is this possible or is there another way to provide the
same functionality?
However, I've tried several different ways to combine the two into a
subquery to update my result field with the Count but receive different
errors depending on what I try. The queries are below.
Thanks for any help or direction that you can provide.
Terryomsn

Query 1
SELECT Count(AuditDetailTreatmentTbl.AuditDtlCnt) AS [Number of Counts],
AuditDetailTreatmentTbl.Medicare, AuditDetailTreatmentTbl.PhysicianOrdPres
FROM AuditDetailTreatmentTbl
GROUP BY AuditDetailTreatmentTbl.Medicare,
AuditDetailTreatmentTbl.PhysicianOrdPres
HAVING (((AuditDetailTreatmentTbl.Medicare)=On) AND
((AuditDetailTreatmentTbl.PhysicianOrdPres)="Yes"));

Query 2
UPDATE AuditRptTbl SET AuditRptTbl.AuditRptYesCount = null
WHERE (((AuditRptTbl.AuditRptID)=1));
Click to expand...
.
Click to expand...
 
I'm not sure how to Rate replies using the discussion group tool. Your
information was timely, right on the mark, and very much appreciated. Thank
you very much for your help.

John Spencer said:
Cannot be done that way in Access.
One solution is to use the DCOUNT function.

Beyond that it is difficult to give you further advice since I don't know HOW
AuditRptTbl records are related to AuditDetailTreatmentTbl and the types of
fields involved. GUESSING that AudRptID is a number and exists in both
tables, I would say you need something like the following.

UPDATE AuditRptTbl
SET AuditRptTbl.AuditRptYesCount =
DCOUNT("*","AuditDetailTreatmentTbl","Medicare=On AND
PhysicianOrdPres='Yes' AND [SomeFIeld in AuditDetailTreatmentTbl]=" & AudrptID)
WHERE AuditRptTbl.AuditRptID=1

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have two tables, one of which has a field (AuditRptTbl.AuditRptYesCount)
that I want to update with the Count of "Yes" values in another table
(AuditDetailTreatmentTbl.AuditDtlCnt). I've constructed two queries, one of
which counts the values and works ok, and the other query will update a
specific column ok. Is this possible or is there another way to provide the
same functionality?
However, I've tried several different ways to combine the two into a
subquery to update my result field with the Count but receive different
errors depending on what I try. The queries are below.
Thanks for any help or direction that you can provide.
Terryomsn

Query 1
SELECT Count(AuditDetailTreatmentTbl.AuditDtlCnt) AS [Number of Counts],
AuditDetailTreatmentTbl.Medicare, AuditDetailTreatmentTbl.PhysicianOrdPres
FROM AuditDetailTreatmentTbl
GROUP BY AuditDetailTreatmentTbl.Medicare,
AuditDetailTreatmentTbl.PhysicianOrdPres
HAVING (((AuditDetailTreatmentTbl.Medicare)=On) AND
((AuditDetailTreatmentTbl.PhysicianOrdPres)="Yes"));

Query 2
UPDATE AuditRptTbl SET AuditRptTbl.AuditRptYesCount = null
WHERE (((AuditRptTbl.AuditRptID)=1));
Click to expand...
.
Click to expand...
 
Great and thanks for the feedback. We don't always get that and are left to
guess if our advice was correct or not.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top