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.

O.K. I will check and see if it arrived. I don't check that account
very often.

I have no idea what the software on this site does when someone
tries to upload too large a file. I doubt that you would be banned
automatically for one mistake.

See if this is what you're looking for.

1st Query is qry_CountOfUniqueDonors
2ns is the one you sent, right?

The calculation is doin it's thing, but the answer comes up 7 when I count 6 different unique donor Ids

We are close! Or should I say YOU are close!

DonorsID SumOfDonationAmount DonationPaidDate StudentId STFullName CountOfNullScholarshipPaidDateDonors CountOfDonors
23657 $825.00 12/5 /2005 21866 DonorName 7 7
23657 $1,000.00 7 /22/2006 21866 DonorName 7 7
23672 $1,000.00 7 /17/2006 21866 DonorName 7 7
23673 $1,000.00 6 /5 /2006 21866 DonorName 7 7
23681 $1,000.00 5 /25/2006 21866 DonorName 7 7
23861 $500.00 7 /23/2006 21866 DonorName 7 7
24572 $825.00 12/19/2005 21866 DonorName 7 7
25055 $500.00 11/30/2005 21866 DonorName 7 7

You are counting wrong.

Ha ha ha ha. So I am, so I am. Maybe I should get checked for dyslexia (681, 861, same thing!)

I've got to learn more SQL stuff, and I certainly appreciate this help. Sorry I sucked up so much time.

Have a great weekend, Ace.

Just so you are clear, the last query I posted isn't meant to
be combined with anything else. Just copy it as it is, paste it into
the SQL view of a new query and save it as whatever name you
want to. I mention this because it should not be returning the last field
shown (7) twice. You too and I hope your Mom is having a blast at the U.S. Open.

Yes, I already got rid of that last field. I will test your variations when I get some time.

I'm packing coolers with food and alcohol, going to a HUGE bachelor party in the cool pines of AZ this weekend, but when I get back I will analyze this thread and get my head around it.

I've learned a lot here and I thank you immensely for it.

Sebides my Lydexia on the gountinc of the NodorID munbers, I should be doog to og. hee hee hee

Thanks again, very much.