One more question about this report you folks built me :-)

[herbal]

I need to just add a count of records by group. To be more specific I need a count of added records, and a count of expired records.

Here's the SQL statement:

SELECT 'Expired' As [Type], ExpiredMembers.FirstName, ExpiredMembers.MiddleName, ExpiredMembers.LastName, ExpiredMembers.DateUpdated, ExpiredMembers.MemberID, #1/1/1900# As DateJoined, #1/1/1900# AS DateExpired, ExpiredMembers.DL AS [TDL\DL]
FROM ExpiredMembers
WHERE (((ExpiredMembers.DateUpdated)>=[Retrieve deleted members since what date?] And (ExpiredMembers.DateUpdated)<=[date?]))
UNION SELECT 'Added' AS [Type], Members.FirstName, Members.MiddleName, Members.LastName, NULL As DateUpdate, Members.MemberID, Members.DateJoined, Members.DateExpired, Members.TDL AS [TDL\DL]
FROM Members
WHERE (((Members.DateJoined)>=[Retrieve new members since what date?] And (Members.DateJoined)<=[date?]))
ORDER BY Type DESC , MemberID;

Thanks
Herbal

 

[dhookom]

To count the Expired values:
=Sum(Abs([Type]="Expired"))
Do something similar for Added.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.