Results 1 to 14 of 14
  1. #1
    hlf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    9

    Please Help with Query


    I'm a volunteer working for a nonprofit and a REAL novice with databases. I've been trying to move the organization from spreadsheets to an Access database. I want to create a query to show how many members we have, how much they've paid for their membership and then any additional donations they may have made to the organization. We've also got some people who donate but aren't members. The query I'm thinking about would be for the period August 1, 2017 - July 31, 2018 (our operating year) have Last Name, First Name, membership date, Membership Amount, Donation Date, Donation Amount and then I would total the membership amount and donation amount at the bottom of the query. Any help or advice would be greatly appreciated. I've attached a screenshot of the query. Thanks!
    Attached Thumbnails Attached Thumbnails Screen Shot 2018-07-14 at 11.14.43 AM.jpg  

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    I couldn't test the query, but on fly to get all payments for every member made in operating year 2017-2018 it will be something like
    Code:
    SELECT ci.[Last Name], ci.[First Name], mt.[Membership Date], SUM(mt.[Membership Amount]) AS [Membership Amount], dt.[Donation Date], SUM(dt.[Donation Amount]) AS [Donation Amount]
    FROM (([Contacts Information] ci LEFT JOIN [Membership Table] mt ON mt.ContactID = ci.ContactID) LEFT JOIN [Donations Table] dt ON dt.ContactID = ci.ContactID)
    WHERE
        (mt.[Membership Date] > DateSerial(2017,31,7) AND mt.[Membership Date] < DateSerial(2018,1,8)) AND
        (mt.[Donation Date] > DateSerial(2017,31,7) AND mt.[Donation Date] < DateSerial(2018,1,8))
    GROUP BY ci.[Last Name], ci.[First Name], mt.[Membership Date], dt.[Donation Date]
    To get the total of all payments in operating year 2017-2018 the query maybe will be something like
    Code:
    SELECT SUM(mt.[Membership Amount]) AS [Membership Amount], SUM(dt.[Donation Amount]) AS [Donation Amount]
    FROM (([Contacts Information] ci LEFT JOIN [Membership Table] mt ON mt.ContactID = ci.ContactID) LEFT JOIN [Donations Table] dt ON dt.ContactID = ci.ContactID)
    WHERE
        (mt.[Membership Date] > DateSerial(2017,31,7) AND mt.[Membership Date] < DateSerial(2018,1,8)) AND
        (mt.[Donation Date] > DateSerial(2017,31,7) AND mt.[Donation Date] < DateSerial(2018,1,8))
    The query returns a query table, which you can dispaly, or to use as data source for form or report. You can't have anything at bottom of query itself.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in this link on Database Planning, Design etc.
    You will learn a process that can be used with any database.
    Good luck and welcome to the forum.

  4. #4
    hlf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    9
    Thanks for your help. I've spent some time reviewing many of the YouTube tutorials you mentioned (I must be a slow learner) and think I have a little better understanding now. I totally scrapped my initial attempt and revamped it with the attached diagram of the relationships. I seem to be able to get all the queries I wanted, except one. I would like to be able to group by last name/first name and have the donations by cause (membership, General Fund, Cemetery Fund, and/or Lyle-Simpson Fund) show up on one row for each donor with a grand total for each donor at the end of the row (I did get the total amount for each donor but not broken out by cause). Is that possible with my current setup?

    Thanks again.

    Click image for larger version. 

Name:	Screen Shot 2018-07-24 at 12.50.24 PM.png 
Views:	30 
Size:	22.2 KB 
ID:	34838

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you try a CROSSTAB query? Review http://allenbrowne.com/ser-67.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    hlf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    9
    Thank you. I did not try that. I'll give it a shot.

  7. #7
    hlf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    9
    June7,
    Crosstab worked perfectly ... just what I was looking for! Thank you again.

  8. #8
    hlf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    9
    Quote Originally Posted by hlf View Post
    June7,
    Crosstab worked perfectly ... just what I was looking for! Thank you again.
    One last question (hopefully): I'm trying to run a query to get each donor's total donations for the period August 1, 2017 - July 31, 2018. I want each donor to be one row with the total donation amount displayed. This query doesn't seem to be working:

    SELECT Contacts.LastName, Contacts.FirstName, Sum(Donations.DonationAmount) AS [Total Donation]
    FROM Donations INNER JOIN (Contacts INNER JOIN Contacts_Donations ON Contacts.ContactID = Contacts_Donations.ContactID) ON Donations.DonationsID = Contacts_Donations.DonationsID
    GROUP BY Contacts.LastName, Contacts.FirstName, Donations.DonationAmount, Donations.DonationDate
    HAVING (((Donations.DonationDate)>#8/1/2017#));

    I thought the Group By function would do that, but it doesn't seem to be working. The results give me separate rows for donors who made multiple donations during that time period rather than one row with the total sum of his/her donations. Could someone advise on how to create a query or report for what I want?

    Thanks.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't include DonationAmount and DonationDate in the GROUP BY. If you want to show raw details as well as summary data, don't use aggregate query - build report using its Sorting & Grouping features with aggregate calcs in group header textboxes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    hlf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    9
    Quote Originally Posted by June7 View Post
    Don't include DonationAmount and DonationDate in the GROUP BY. If you want to show raw details as well as summary data, don't use aggregate query - build report using its Sorting & Grouping features with aggregate calcs in group header textboxes.
    Once again ... exactly what I was trying to do for the query! Thank you so much. Now, I'm going to start working on the reporting capabilities. I've been exporting the queries to Excel and doing final formatting, etc.

  11. #11
    hlf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    9
    Quote Originally Posted by hlf View Post
    Once again ... exactly what I was trying to do for the query! Thank you so much. Now, I'm going to start working on the reporting capabilities. I've been exporting the queries to Excel and doing final formatting, etc.
    My final step is to try and build a form so an administrative assistant can enter new donations as they are received. Right now, I have been entering the donations manually in the tables, using the following process: I create a new donation on the donations table. I take the new donation number (donationsID) and manually enter it in the Contacts_Donors Table, along with the ContactID (from the Contacts Table). This creates a new Contacts_Donations ID.

    I tried developing a compound form with a sub-form that captures the ContactID, Last Name and First Name on the main form and donation data (since each contact normally has multiple donations) on the sub-form. However, I don't understand how to get the Contacts_Donations table to update with the ContactID and DonationsID (which shows that contact XX donated YY). Am I over-complicating things or am I totally missing the boat? I've attached a screen shot of the form design view if that helps.

    Thanks for your patience ...
    Click image for larger version. 

Name:	Screen Shot 2018-08-03 at 2.55.48 PM.png 
Views:	17 
Size:	47.1 KB 
ID:	34957

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Does Contacts_Donations table have a field for DonorID in which the ContactID is saved as foreign key?

    Set the subform container control Master/Child Links properties to these two fields. This will synchronize the two forms and automatically save ContactID into DonorID.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    hlf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    9
    Quote Originally Posted by June7 View Post
    Does Contacts_Donations table have a field for DonorID in which the ContactID is saved as foreign key?

    Set the subform container control Master/Child Links properties to these two fields. This will synchronize the two forms and automatically save ContactID into DonorID.
    I do have both the DonorID and ContactID in the Contacts_Donations Table and both are saved as foreign keys (the DonorID to the Donations Table and the ContactID to the Contacts Table). I'm taking some time to learn about forms and subforms. I appreciate your help and will keep you posted on my progress.

    Thanks again.

  14. #14
    hlf is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2018
    Posts
    9
    Quote Originally Posted by hlf View Post
    I do have both the DonorID and ContactID in the Contacts_Donations Table and both are saved as foreign keys (the DonorID to the Donations Table and the ContactID to the Contacts Table). I'm taking some time to learn about forms and subforms. I appreciate your help and will keep you posted on my progress.

    Thanks again.
    Got it working and all is great! Thanks so much for all the help!!

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

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