Update of Existing Table Numeric Value Using DCount from Another T

[Terry]

I have a row in a table that was populated with the 1st query below. I'd
like to update the existing numeric value in that table with a 2nd query that
uses DCount from another table. Is that possible? If it is, what would the
query look like?
Thanks,
TerryoMSN

1st Query That Populates the Existing Numeric Value
UPDATE AuditRptTbl SET AuditRptTbl.AuditRptNoCount =
DCount("AuditDtlCnt","AuditDetailInitialEval","(((AuditDetailInitialEval.Medicare)=On)
AND ((AuditDetailInitialEval.DateofService) Between #12/31/2008# And
#1/1/2010#) AND ((AuditDetailInitialEval.ReasonableAndNecSvc)=Off))")
WHERE AuditRptTbl.AuditRptID=22;

2nd Query That DCounts Value From Second Table That Would be Added to
Existing Value
UPDATE AuditRptTbl SET AuditRptTbl.AuditRptNoCount =
DCount("AuditDtlCnt","AuditDetailTreatmentTbl","(((AuditDetailTreatmentTbl.Medicare)=On)
AND ((AuditDetailTreatmentTbl.DtOfSvc) Between #12/31/2008# And #1/1/2010#)
AND ((AuditDetailTreatmentTbl.SvcReasonableNecessary)=Off))")
WHERE AuditRptTbl.AuditRptID=22;

 

[John W. Vinson]

I have a row in a table that was populated with the 1st query below. I'd
like to update the existing numeric value in that table with a 2nd query that
uses DCount from another table. Is that possible? If it is, what would the
query look like?

Well, it would be a Very Bad Idea to do so. When you store a count in the
table, that value is redundant, and at great risk: it can be edited (and will
now be wrong); or you can add or delete a record in the other table, and it'll
again be wrong. Do you have some *GOOD* reason to store this value, rather
than just calculating it on the fly with a Totals query or a DCount()
function?

As for your update, do you want to replace the value with the new dcount, or
add the old dcount to the new dcount?

When you run the query next year, will you still want to be using the literal
2008 and 2010 dates?

You're really making it hard for yourself here...!

 

[Tom Wickerath]

Hi Terry,

Could you not determine the two values first, add them together, and then
run a single update query? Something like this, in a VBA subroutine:

Dim strSQL As String
Dim lngCount1 As Long
Dim lngCount2 As Long
Dim lngTotal As Long

lngCount1 =
DCount("AuditDtlCnt","AuditDetailInitialEval","(((AuditDetailInitialEval.Medicare)=On)
AND ((AuditDetailInitialEval.DateofService) Between #12/31/2008# And
#1/1/2010#) AND ((AuditDetailInitialEval.ReasonableAndNecSvc)=Off))")

lngCount2 = _
DCount("AuditDtlCnt", "AuditDetailTreatmentTbl",
"(((AuditDetailTreatmentTbl.Medicare)=On) AND
((AuditDetailTreatmentTbl.DtOfSvc) Between #12/31/2008# And #1/1/2010#) AND
((AuditDetailTreatmentTbl.SvcReasonableNecessary)=Off))")

lngTotal = lngCount1 + lngCount2

strSQL = "UPDATE AuditRptTbl " _
& "SET AuditRptTbl.AuditRptNoCount = " & lngTotal & " " _
& "WHERE AuditRptTbl.AuditRptID=22;"

'Note: You'll likely want to substitute a variable for the hardcoded 22,
above.

CurrentDB.Execute strSQL, dbFailOnError

'Add appropriate error handling code


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Terry]

Thanks for the great looking VBA code.
I've not done VBA yet, but it was in the list of To Learns. I think it just
moved up in priority. Where does a VBA subroutine like this get placed in
the application? Is it a control event?