Problems with COUNT in Query

[Guest]

hello,
I have a somewhat LONG sql query that's not working for me. Seems simple
enough. I have two tables. I just want to count the number of times the
record appears in the sub-table.

Main Table is Donors
Sub Table is Donations
Goal is to display donor info and the number of time the donor has made
Donations.

here is the big ol SQL. Can someone tell me what I'm doing wrong?

SELECT Donors.[Company Name], Donors.[First Name], Donors.[Last Name],
Donors.[Mailing Address 1], Donors.[Mailing Address 2], Donors.City,
Donors.State, Donors.[Zip Code], Format([Date Collected],"yyyy") AS [Year
Collected], IIf([Title]<>"",[Title] & " " & [First Name] & " " & [Last Name]
& " " & [Suffix],[First Name] & " " & [Last Name] & " " & [Suffix]) AS
Full_Name, [City] & ", " & [State] & " " & [Zip Code] AS City_St_Zip,
Donors.Title, Donors.Suffix, Donations.[Date Collected], Donors.[Donor ID
Number], Count(Donations.[Donor ID Number]) AS [CountOfDonor ID Number]
FROM Donors INNER JOIN Donations ON Donors.[Donor ID Number] =
Donations.[Donor ID Number]
GROUP BY Donors.[Company Name], Donors.[First Name], Donors.[Last Name],
Donors.[Mailing Address 1], Donors.[Mailing Address 2], Donors.City,
Donors.State, Donors.[Zip Code], Format([Date Collected],"yyyy"),
IIf([Title]<>"",[Title] & " " & [First Name] & " " & [Last Name] & " " &
[Suffix],[First Name] & " " & [Last Name] & " " & [Suffix]), [City] & ", " &
[State] & " " & [Zip Code], Donors.Title, Donors.Suffix, Donations.[Date
Collected], Donors.[Donor ID Number]
HAVING (((Donations.[Date Collected]) Between [Forms]![Filter Criteria Donor
Reports].[txtStartDate] And [Forms]![Filter Criteria Donor
Reports].[txtEndDate]));

 

[arthurjr07]

Try this..

SELECT Donors.*, A.CNT FROM Donors INNER JOIN
(SELECT Donations.[Donor ID Number] , Count(*) AS CNT
FROM Donations GROUP BY Donations.[Donor ID Number]) AS A
ON Donors.[Donor ID Number] = A.[Donor ID Number]

 

[Marshall Barton]

I have a somewhat LONG sql query that's not working for me. Seems simple
enough. I have two tables. I just want to count the number of times the
record appears in the sub-table.

Main Table is Donors
Sub Table is Donations
Goal is to display donor info and the number of time the donor has made
Donations.

here is the big ol SQL. Can someone tell me what I'm doing wrong?

SELECT Donors.[Company Name], Donors.[First Name], Donors.[Last Name],
Donors.[Mailing Address 1], Donors.[Mailing Address 2], Donors.City,
Donors.State, Donors.[Zip Code], Format([Date Collected],"yyyy") AS [Year
Collected], IIf([Title]<>"",[Title] & " " & [First Name] & " " & [Last Name]
& " " & [Suffix],[First Name] & " " & [Last Name] & " " & [Suffix]) AS
Full_Name, [City] & ", " & [State] & " " & [Zip Code] AS City_St_Zip,
Donors.Title, Donors.Suffix, Donations.[Date Collected], Donors.[Donor ID
Number], Count(Donations.[Donor ID Number]) AS [CountOfDonor ID Number]
FROM Donors INNER JOIN Donations ON Donors.[Donor ID Number] =
Donations.[Donor ID Number]
GROUP BY Donors.[Company Name], Donors.[First Name], Donors.[Last Name],
Donors.[Mailing Address 1], Donors.[Mailing Address 2], Donors.City,
Donors.State, Donors.[Zip Code], Format([Date Collected],"yyyy"),
IIf([Title]<>"",[Title] & " " & [First Name] & " " & [Last Name] & " " &
[Suffix],[First Name] & " " & [Last Name] & " " & [Suffix]), [City] & ", " &
[State] & " " & [Zip Code], Donors.Title, Donors.Suffix, Donations.[Date
Collected], Donors.[Donor ID Number]
HAVING (((Donations.[Date Collected]) Between [Forms]![Filter Criteria Donor
Reports].[txtStartDate] And [Forms]![Filter Criteria Donor
Reports].[txtEndDate]));

Two quick observations.

Get rid of the Donations.[Date Collected] fielld in the
SELECT clause.

Change the HAVING to WHERE.