Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2006
    Posts
    2

    "Count" and "Countif" in Reports


    I have a query based on my Contact Table and Gift Table. It shows the ContactID from the Contact Table, Brochure Request Date from the Contact Table (criteria between 1/1/1999 and 1/31/1999), Gift ID from the Gift Table, Date of Gift from the Gift Table ("First" in total line), Amount of Gift from the Gift Table ("First" from total line). Running the query gives me two contact ID's who do not have a Gift ID; therefore, gave no gift after requesting a brochure. It also shows the Contact ID of those who requested a brochure in that date range who gave a gift after the request.

    I want a report to count how many Contact ID's there were in January 1999, how many in February 1999, how many in March 1999, etc. I need no details or dollar amounts. Just to know there were 11 people who requested the brochure between 1/1/1999 and 1/31/1999 who gave first gifts in January of 1999, 6 who gave first gifts in February of 1999, etc.

    Is there an easier way than creating separate queries from the main query to count each month - then create the report to show the numbers?

    Any suggestions are greatly appreciated.

    Thanks for your time.

    Julie

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Julie,

    Try basing your report on a query like this:

    SELECT Count(*) AS People, Format(c.BrochureDate, "yyyy-mm") AS BrochureMonth, IIf(IsNull(g.[GiftDate]), "No gift", Format(g.[GiftDate], "yyyy-mm")) AS GiftMonth
    FROM Contact AS c LEFT JOIN Gift AS g ON c.ContactID = g.ContactID
    ORDER BY Format(c.BrochureDate, "yyyy-mm"), IIf(IsNull(g.[GiftDate]), "No gift", Format(g.[GiftDate], "yyyy-mm"))

    If you use the Report Wizard to build the report, group the report on BrochureMonth, and in the summary options sum the People.

    That should be a start; without knowing more about what you're trying to do, I am not sure what other advice I could give.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2003 Date() Function returns "#Name?"
    By smartel@soprema.ca in forum Programming
    Replies: 5
    Last Post: 01-18-2012, 05:52 AM
  2. Replies: 0
    Last Post: 09-25-2008, 12:19 PM
  3. Relocating objects via "Left" properties value
    By Ripcut in forum Programming
    Replies: 0
    Last Post: 08-01-2008, 06:40 PM
  4. Export to .rtf of "104-" converts to "-655&qu
    By Sherri726 in forum Import/Export Data
    Replies: 0
    Last Post: 12-19-2006, 03:16 PM
  5. Replies: 2
    Last Post: 08-31-2006, 12:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums