Hello,

I have a need to count unique records and I'm getting low on brain cells today.

In tblDonations, there is information about each donation as well as a fkDonorID.

Over a user specified time frame, I need to return the count of Donors that made a donation, but only count each unique Donor ONE time.

How many Donors did we have in June? Not donations, but unique Donors. Any help would be appreciated.

Put that as SQL in a new query?

Yes. That is SQL that will return a single row with
the number of distinct donors as it's only column.

You need to replace [Your Criteria] with whatever date
test you want to make. You should start by getting the
bracketed Select query to work correctly.

Thank you very much, however I think I have to get a better handle on this....

I can create a query that has all donations and the critera from a loaded form to narrow it down to the donations collection I need.

Then, do I create your query and link it to that one using fkDonorID? Or do I just put the statement below the SQL that has already been created in the query I created?

I'm sorry to bug ya, but this is the first reqest I've had of this nature.

DonorsID SumOfDonationAmount DonationPaidDate StudentId STFullName
23657 $1,000.00 7 /22/2006 21866 j.Student
23672 $1,000.00 7 /17/2006 21866 j.Student
23673 $1,000.00 6 /5 /2006 21866 j.Student
23681 $1,000.00 5 /25/2006 21866 j.Student
23861 $500.00 7 /23/2006 21866 j.Student

This the a sample dataset that my query is returning. I need to get a count (in this case the result would be 4) of unique donors that donated in 2006 to that student. I will be displaying that on both a pop up form for a quick answer as well as a printed report to mail to the student's parents.

Does that help?

Show the Where clause from the query that returns that result.

WHERE (((tblDonations.StudentID)=[Forms]![frm_Students]![StudentId]) AND ((tblDonations.[Scholarship Paid Date]) Is Null))

Donations don't become Scholarships until they are Paid, so we want to show all Donations for a Student that are not yet paid Scholarships, and we want to know the number of unique Donors involved.

Paste the following SQL statment into the SQL view of the
query builder and run it when the frm_Students form
is open. It should return the count of distinct donors
for the current studentID value on the form.

If that works we can figure out where you need to
use it.

I'm with you, but I'm lacking SQL launguage expertise. Here's the whole query statement:

SELECT tblDonations.DonorsID, Sum(tblDonations.DonationAmount) AS SumOfDonationAmount, tblDonations.DonationPaidDate, tblStudents.StudentId, [StLastName] & ", " & [StFirstName] AS STFullName
FROM (tblDonations INNER JOIN tblStudents ON tblDonations.StudentID = tblStudents.StudentId) INNER JOIN tblSchools ON tblDonations.SchoolID = tblSchools.SchoolID
WHERE (((tblDonations.StudentID)=[Forms]![frm_Students]![StudentId]) AND ((tblDonations.[Scholarship Paid Date]) Is Null))
GROUP BY tblDonations.DonorsID, tblDonations.DonationPaidDate, tblStudents.StudentId, [StLastName] & ", " & [StFirstName], tblStudents.StLastName, tblStudents.StFirstName
Select Count(*) as CountOfDonors FROM (Select Distinct fkDonorID From tblDonations Where tblDonations.StudentID=[Forms]![frm_Students]![StudentId] AND tblDonations.[Scholarship Paid Date] Is Null)

Your addition is at the bottom and I'm getting an error - brobably ybecause it is in the wrong place.

Paste just what I posted into the SQL view of a new query.
It does not get added to one you already have.

Got it. qry_CountofUniqueDonors

Next?

Awesome.

Now where do you want the number to show up?

I would like it to be a field in the above mentioned query that showed Donation amount, StudentID, Donation Date, etc....

That one is called qry_frmNewDonationsByStudentPopUp

Then I'll place the query behind the pop-up form and the report.

Also, when we get this, I can use all the same stuff in other places - I really appreciate this.

Sorry. I was off watching Agassi & Baghdatis at the US Open.
Freaking amazing tennis.

Hmmm... I'm gonna have to think about that.
I'm not sure it can even be done. You may have to settle for it
showing up in it's own control somewhere.

Is it possible for you do post the .mdb file with the relevant tables
and some sample data?

We (the kids of the family) sent my Mom to the Open. She's there right now with All Access Passes for the whole week. She's a big fan.

Might be hard to send, back end is 34 MB, front end 2MB. Paring down data could take some time.

We can pick it up tomorrow, it's late.

I can certainly create a control to display the data as long as the Count query is getting the same Student ID from the Loaded Form.

Sound Like a plan? Here's the way your statement looks in the Count Query:

SELECT Count(*) AS CountOfDonors
FROM [Select Distinct fkDonorID From tblDonations Where tblDonations.StudentID=[Forms]![frm_Students]![StudentId] AND tblDonations.[Scholarship Paid Date] Is Null]. AS [%$##@_Alias];

It looks like it will get the same Student ID if the form is loaded.

Thanks again, Ace, I know it's late, but then again.... That's what we do!

Your Mom's a lucky woman.

I only need the two tables involved. You can create a
new database, import the two tables and delete all
but the data for a couple of students in both tables.

Compact and repair the .mdb file and then Zip it up
before you send it.

OK here you go.

Woops - too big - I added a zero when reading the limit. Let me get back on it tomorrow.

Ace,

Sent an email with attachment - did you get it? I think I've been banned from attachments since I tried to send one that was too big last night. Now I can't attach at all on UtterAcces.