Prev: select records where first letter of name begins with an a through
Next: Subtracting Value from Field if Yes/No = Yes
From: Terry on 31 Mar 2010 16:12
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));

From: John Spencer on 31 Mar 2010 17:46
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

Terry wrote:
> 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));
>
 | 
Pages: 1
Prev: select records where first letter of name begins with an a through
Next: Subtracting Value from Field if Yes/No = Yes