counting aged accounts in report

[PerfectCircle]

I have a table that has a submitted claim date as well as a paid claim date for each account. I would like to get the difference between these two dates and group them in a report as over 30 days ,over 60 days ect. I would also like to have a count in each group. Any ideas.
Thanks
Perfectcircle

 

[sko]

Check Ginger's response in this thread [Thread181-163638]

To adapt to your situation you might do this:

SELECT tblAccounts.Account, tblAccounts.Name, IIf([PaidDate]-[ClaimDate]<30,&quot;1-29 Days&quot;,IIf([paiddate]-[claimdate] Between 30 And 60,&quot;30-60 Days&quot;,&quot;Over 60&quot) AS Status
FROM tblClaims INNER JOIN tblAccounts ON tblClaims.Account = tblAccounts.Account
WHERE (((tblClaims.PaidDate) Is Not Null));

If you want to include unpaid claims, do this:

SELECT tblAccounts.Account, tblAccounts.Name, IIf(IsNull([paiddate]),&quot;Not Paid&quot;,IIf([PaidDate]-[ClaimDate]<30,&quot;1-29 Days&quot;,IIf([paiddate]-[claimdate] Between 30 And 60,&quot;30-60 Days&quot;,&quot;Over 60&quot;))) AS Status
FROM tblClaims INNER JOIN tblAccounts ON tblClaims.Account = tblAccounts.Account;


Then create a report grouping on Status. Show a group footer for Status, add an unbound text box, make the control source =Count([account])


Hope this helps