sum of count based on criteria 1

[topwaya]

The following query gives me a list of users who have signed in between parameter dates. I would like to only get a list of users who have signed in 8 or more times. The user is defined by tbleFCUtil.PID. I've tried different HAVING statements, but I can't get any of them to work, I think I have to SUM the counts for each PID and I can't figure out how to write that. Any ideas?

SELECT Month([WOD]) AS [Month], Count(tblFCUtil.PID) AS CountOfPID, qryFCMembers.LocW, qryFCMembers.FullName, tblFCUtil.WOLoc, tblFCUtil.WOTme, tblFCUtil.PID, qryFCMembers.Mtype, tblPersonal.Dept, tblPersonal.LocFC1, tblPersonal.LocFC2, tblPersonal.Email, tblAddr.PhoneW, tblAddr.PhoneH, tblPersonal.Shirt, tblFCUtil.WOD
FROM tblPersonal INNER JOIN ((tblFCUtil INNER JOIN qryFCMembers ON tblFCUtil.PID = qryFCMembers.PID) INNER JOIN tblAddr ON tblFCUtil.PID = tblAddr.PID) ON (tblPersonal.PID = tblFCUtil.PID) AND (tblPersonal.PID = tblAddr.PID) AND (tblPersonal.PID = qryFCMembers.PID)
WHERE (((tblFCUtil.WOD) Between [Forms]![fdlgMOM]![txtStart] And [Forms]![fdlgMOM]![txtEnd]))
GROUP BY Month([WOD]), qryFCMembers.LocW, qryFCMembers.FullName, tblFCUtil.WOLoc, tblFCUtil.WOTme, tblFCUtil.PID, qryFCMembers.Mtype, tblPersonal.Dept, tblPersonal.LocFC1, tblPersonal.LocFC2, tblPersonal.Email, tblAddr.PhoneW, tblAddr.PhoneH, tblPersonal.Shirt, tblFCUtil.WOD;

Thanks for any help!

 

[lespaul]

this doesn't work?

SELECT Month([WOD]) AS [Month], Count(tblFCUtil.PID) AS CountOfPID, qryFCMembers.LocW, qryFCMembers.FullName, tblFCUtil.WOLoc, tblFCUtil.WOTme, tblFCUtil.PID, qryFCMembers.Mtype, tblPersonal.Dept, tblPersonal.LocFC1, tblPersonal.LocFC2, tblPersonal.Email, tblAddr.PhoneW, tblAddr.PhoneH, tblPersonal.Shirt, tblFCUtil.WOD

FROM tblPersonal 
INNER JOIN ((tblFCUtil INNER JOIN qryFCMembers ON tblFCUtil.PID = qryFCMembers.PID) 
INNER JOIN tblAddr ON tblFCUtil.PID = tblAddr.PID) ON (tblPersonal.PID = tblFCUtil.PID) AND (tblPersonal.PID = tblAddr.PID) AND (tblPersonal.PID = qryFCMembers.PID)

WHERE (((tblFCUtil.WOD) Between [Forms]![fdlgMOM]![txtStart] And [Forms]![fdlgMOM]![txtEnd]))

GROUP BY Month([WOD]), qryFCMembers.LocW, qryFCMembers.FullName, tblFCUtil.WOLoc, tblFCUtil.WOTme, tblFCUtil.PID, qryFCMembers.Mtype, tblPersonal.Dept, tblPersonal.LocFC1, tblPersonal.LocFC2, tblPersonal.Email, tblAddr.PhoneW, tblAddr.PhoneH, tblPersonal.Shirt, tblFCUtil.WOD

HAVING Count(tblFCUtil.PID) >= 8;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[topwaya]

no

I tried

HAVING Count(tblFCUtil.PID) > 7; as well as what you suggested.

But it doesn't work, I think because the results that I get, without the HAVING clause are listed like this:

Month CountofPID ....... Personal ID........
1 1 101868
1 1 101868
1 1 101868
1 1 101869
1 1 101869
1 1 101869

Etc. I have 3 test people in there that have signed in 8 or more times, but the CountofPID doesn't show the total number of times, if I change the HAVING clause to
HAVING Count(tblFCUtil.PID)=1;

Then it shows everyone because everyone's CountofPID =1.

Any idea on how to sum the CountofPID based on the PID (Personal ID) so the HAVING statement will work, or another way to do it?

Thanks so much for any help.

 

[lespaul]

thanks for the query results, that helps tremendously. This may do it:

SELECT Month([WOD]) AS [Month], SUM(Count(tblFCUtil.PID)) AS CountOfPID, qryFCMembers.LocW, qryFCMembers.FullName, tblFCUtil.WOLoc, tblFCUtil.WOTme, tblFCUtil.PID, qryFCMembers.Mtype, tblPersonal.Dept, tblPersonal.LocFC1, tblPersonal.LocFC2, tblPersonal.Email, tblAddr.PhoneW, tblAddr.PhoneH, tblPersonal.Shirt, tblFCUtil.WOD

FROM tblPersonal 
INNER JOIN ((tblFCUtil INNER JOIN qryFCMembers ON tblFCUtil.PID = qryFCMembers.PID) 
INNER JOIN tblAddr ON tblFCUtil.PID = tblAddr.PID) ON (tblPersonal.PID = tblFCUtil.PID) AND (tblPersonal.PID = tblAddr.PID) AND (tblPersonal.PID = qryFCMembers.PID)

WHERE (((tblFCUtil.WOD) Between [Forms]![fdlgMOM]![txtStart] And [Forms]![fdlgMOM]![txtEnd]))

GROUP BY Month([WOD]), qryFCMembers.LocW, qryFCMembers.FullName, tblFCUtil.WOLoc, tblFCUtil.WOTme, tblFCUtil.PID, qryFCMembers.Mtype, tblPersonal.Dept, tblPersonal.LocFC1, tblPersonal.LocFC2, tblPersonal.Email, tblAddr.PhoneW, tblAddr.PhoneH, tblPersonal.Shirt, tblFCUtil.WOD

HAVING Sum(Count(tblFCUtil.PID)) >= 8;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

And what about something like this ?
SELECT Month([WOD]) AS [Month], Count(U.PID) AS CountOfPID, M.LocW, M.FullName, U.PID, M.Mtype, P.Dept, P.LocFC1, P.LocFC2, P.Email, A.PhoneW, A.PhoneH, P.Shirt
FROM tblPersonal P
INNER JOIN ((tblFCUtil U INNER JOIN qryFCMembers M ON U.PID = M.PID)
INNER JOIN tblAddr A ON U.PID = A.PID) ON P.PID = U.PID
WHERE (U.WOD Between [Forms]![fdlgMOM]![txtStart] And [Forms]![fdlgMOM]![txtEnd])
AND (SELECT Count(*) FROM tblFCUtil WHERE PID = U.PID
AND WOD Between [Forms]![fdlgMOM]![txtStart] And [Forms]![fdlgMOM]![txtEnd]) >= 8
GROUP BY Month([WOD]), M.LocW, M.FullName, U.PID, M.Mtype, P.Dept, P.LocFC1, P.LocFC2, P.Email, A.PhoneW, A.PhoneH, P.Shirt;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

I suspect that your problem is that you have differences in some of your GROUP BY fields and, because of that, different groups of records are being created whenever a value changes. In aggregate the count for a particular PID may be 8 or more but it is not within any sub-group. You may try the following.

SELECT Month([WOD]) AS [Month], Count(tblFCUtil.PID) AS CountOfPID, qryFCMembers.LocW, qryFCMembers.FullName, tblFCUtil.WOLoc, tblFCUtil.WOTme, tblFCUtil.PID, qryFCMembers.Mtype, tblPersonal.Dept, tblPersonal.LocFC1, tblPersonal.LocFC2, tblPersonal.Email, tblAddr.PhoneW, tblAddr.PhoneH, tblPersonal.Shirt, tblFCUtil.WOD

FROM tblPersonal 
INNER JOIN ((tblFCUtil INNER JOIN qryFCMembers ON tblFCUtil.PID = qryFCMembers.PID) 
INNER JOIN tblAddr ON tblFCUtil.PID = tblAddr.PID) ON (tblPersonal.PID = tblFCUtil.PID) AND (tblPersonal.PID = tblAddr.PID) AND (tblPersonal.PID = qryFCMembers.PID)

WHERE (((tblFCUtil.WOD) Between [Forms]![fdlgMOM]![txtStart] And [Forms]![fdlgMOM]![txtEnd]))
      [b]AND (Select Count(*) As CC From tbleFCUtil As X
              Where X.PID = tbleFCUtil.PID) >= 8[/b]

GROUP BY Month([WOD]), qryFCMembers.LocW, qryFCMembers.FullName, tblFCUtil.WOLoc, tblFCUtil.WOTme, tblFCUtil.PID, qryFCMembers.Mtype, tblPersonal.Dept, tblPersonal.LocFC1, tblPersonal.LocFC2, tblPersonal.Email, tblAddr.PhoneW, tblAddr.PhoneH, tblPersonal.Shirt, tblFCUtil.WOD

Alternately

SELECT FIRST(Month([WOD])) AS [Month], 
	Count(tblFCUtil.PID) AS CountOfPID, 
	FIRST(qryFCMembers.LocW) As Location, 
	FIRST(qryFCMembers.FullName) As FName, 
	FIRST(tblFCUtil.WOLoc) As WOLocation, 
	FIRST(tblFCUtil.WOTme) As WOT, 
	tblFCUtil.PID, 
	FIRST(qryFCMembers.Mtype) As MemberType, 
	FIRST(tblPersonal.Dept) As Department, 
	FIRST(tblPersonal.LocFC1) As LocationFC1, 
	FIRST(tblPersonal.LocFC2) As LocationFC2, 
	FIRST(tblPersonal.Email) As EMailAddress, 
	FIRST(tblAddr.PhoneW) As WorkPhone, 
	FIRST(tblAddr.PhoneH) As HomePhone, 
	FIRST(tblPersonal.Shirt) As SS, 
	FIRST(tblFCUtil.WOD) As WODField

FROM tblPersonal 
INNER JOIN ((tblFCUtil INNER JOIN qryFCMembers ON tblFCUtil.PID = qryFCMembers.PID) 
INNER JOIN tblAddr ON tblFCUtil.PID = tblAddr.PID) ON (tblPersonal.PID = tblFCUtil.PID) AND (tblPersonal.PID = tblAddr.PID) AND (tblPersonal.PID = qryFCMembers.PID)

WHERE (((tblFCUtil.WOD) Between [Forms]![fdlgMOM]![txtStart] And [Forms]![fdlgMOM]![txtEnd]))

GROUP BY  tblFCUtil.PID

HAVING Count(tblFCUtil.PID) >= 8

 

[topwaya]

Yippee!!!! Thank you so much!!!

Both PHV and Golom's second code work.

Lespaul - I got an aggregate error
Golom's first code had "1" again as the CountofPID

The code is a bit too complicated for my poor brain to grasp - but I'm sure I will learn a lot from it in future reference as I learn more.

Thank you so much!!!