UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dcount With Like Criteria, Access 2016    
 
   
ecurns
post May 28 2019, 11:25 AM
Post#1



Posts: 226
Joined: 28-March 05



I'm having an issue getting this function to work correctly in a query.

This works:
CODE
ASRSentCount: DCount("LetterType","FollowUpResponses","[LetterType] like '*ASR*'"


However I need it to count correctly for each Record ID in my query. Right now it's returning the count for the entire table on each record in the query.
I tried changing it to:
CODE
ASRSentCount: DCount("LetterType","FollowUpResponses","[LetterType] like '*ASR*' AND [FollowUpResponses].[RecordID]=[CaseInfo].[RecordID]")

This errors with "Unknown"

The entire query code (works fine without this field) is:
CODE
SELECT CaseInfo.SubscriberLastName, CaseInfo.SubscriberFirstName, CaseInfo.ClaimantFirstName, CaseInfo.SubscriberNumber, CaseInfo.ClaimantBirthDate, CaseInfo.DateofAccident, dbo_Memberdemographic.MemberAddress1, dbo_Memberdemographic.MemberCity, dbo_Memberdemographic.MemberState, dbo_Memberdemographic.MemberZipCode, CaseInfo.CaseType, CaseInfo.ClaimantRelation, CaseInfo.ClaimantKey, CaseInfo.RecordID, AttorneyInfo.AttorneyName, AttorneyInfo.AttorneyFax, CaseInfo.ClaimantLastName, CaseInfo.ASRSentDate, Now()-[ASRSentDate] AS DaysFromLastSend, CaseInfo.ASRReceived, CaseInfo.CurrentStatus, DCount("LetterType","FollowUpResponses","[LetterType] like '*ASR*' AND [FollowUpResponses].[RecordID]=[CaseInfo].[RecordID]") AS ASRSentCount INTO tblmailMergeASR_NextAuto
FROM ((CaseInfo LEFT JOIN dbo_Memberdemographic ON CaseInfo.SubscriberNumber = dbo_Memberdemographic.MemberSSN) LEFT JOIN AttorneyInfo ON CaseInfo.RecordID = AttorneyInfo.AttnyRecordID) LEFT JOIN FollowUpResponses ON CaseInfo.RecordID = FollowUpResponses.RecordID
GROUP BY CaseInfo.SubscriberLastName, CaseInfo.SubscriberFirstName, CaseInfo.ClaimantFirstName, CaseInfo.SubscriberNumber, CaseInfo.ClaimantBirthDate, CaseInfo.DateofAccident, dbo_Memberdemographic.MemberAddress1, dbo_Memberdemographic.MemberCity, dbo_Memberdemographic.MemberState, dbo_Memberdemographic.MemberZipCode, CaseInfo.CaseType, CaseInfo.ClaimantRelation, CaseInfo.ClaimantKey, CaseInfo.RecordID, AttorneyInfo.AttorneyName, AttorneyInfo.AttorneyFax, CaseInfo.ClaimantLastName, CaseInfo.ASRSentDate, Now()-[ASRSentDate], CaseInfo.ASRReceived, CaseInfo.CurrentStatus
HAVING (((CaseInfo.CaseType)=1) AND ((Now()-[ASRSentDate])>=10) AND ((CaseInfo.ASRReceived)=False) AND ((CaseInfo.CurrentStatus)="Open"));
Go to the top of the page
 
theDBguy
post May 28 2019, 12:07 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,520
Joined: 19-June 07
From: SunnySandyEggo


Hi. Try changing it like this:
CODE
ASRSentCount: DCount("LetterType","FollowUpResponses","[LetterType] like '*ASR*' AND [FollowUpResponses].[RecordID]=" & [CaseInfo].[RecordID])

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ecurns
post May 28 2019, 12:10 PM
Post#3



Posts: 226
Joined: 28-March 05



Ahhhh! That worked. Thank you so much!
Go to the top of the page
 
cheekybuddha
post May 28 2019, 12:25 PM
Post#4


UtterAccess VIP
Posts: 11,675
Joined: 6-December 03
From: Telegraph Hill


Hi,

Although you have a solution from DBG, I'm not sure you need a GROUP BY query at all.

Try the following SQL:
CODE
SELECT
  c.SubscriberLastName,
  c.SubscriberFirstName,
  c.ClaimantFirstName,
  c.SubscriberNumber,
  c.ClaimantBirthDate,
  c.DateofAccident,
  m.MemberAddress1,
  m.MemberCity,
  m.MemberState,
  m.MemberZipCode,
  c.CaseType,
  c.ClaimantRelation,
  c.ClaimantKey,
  c.RecordID,
  a.AttorneyName,
  a.AttorneyFax,
  c.ClaimantLastName,
  c.ASRSentDate,
  Now() - c.ASRSentDate AS DaysFromLastSend,
  c.ASRReceived,
  c.CurrentStatus,
  r.ASRSentCount
INTO tblmailMergeASR_NextAuto
FROM (
  (
    CaseInfo c
    LEFT JOIN dbo_Memberdemographic m
           ON c.SubscriberNumber = m.MemberSSN
  )
  LEFT JOIN AttorneyInfo a
         ON c.RecordID = a.AttnyRecordID
)
LEFT JOIN (
  SELECT
    RecordID,
    SUM(LetterType) AS ASRSentCount
  FROM FollowUpResponses
  WHERE LetterType like '*ASR*'
  GROUP BY RecordID
) r
       ON c.RecordID = r.RecordID
WHERE c.CaseType = 1
   AND (Now() - c.ASRSentDate) >= 10
   AND c.ASRReceived = False
   AND c.CurrentStatus = 'Open'
GROUP BY
  c.SubscriberLastName,
  c.SubscriberFirstName,
  c.ClaimantFirstName,
  c.SubscriberNumber,
  c.ClaimantBirthDate,
  c.DateofAccident,
  m.MemberAddress1,
  m.MemberCity,
  m.MemberState,
  m.MemberZipCode,
  c.CaseType,
  c.ClaimantRelation,
  c.ClaimantKey,
  c.RecordID,
  a.AttorneyName,
  a.AttorneyFax,
  c.ClaimantLastName,
  c.ASRSentDate,
  Now() - c.ASRSentDate,
  c.ASRReceived,
  c.CurrentStatus
;


If it works, you can then probably remove the entire GROUP BY clause.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 02:02 AM