Can I get a Total Count?

[Wayne Wengert]

I am using Access 2003 and running the following query:

SELECT SWP.Eqp1Voc, SWP.[Equip 1 Judge], SWP.Contest
FROM SWP
WHERE (((SWP.Eqp1Voc) In (SELECT [Eqp1Voc] FROM [SWP] As Tmp GROUP BY
[Eqp1Voc] HAVING Count(*)>1 )));

Which (correctly) returns 11 rows as follows::

-------------------------------------------------
8.30,"RIGOLINI R","SW Prelims"
8.50,"RIGOLINI R","SW Prelims"
8.30,"RIGOLINI R","SW Prelims"
8.50,"RIGOLINI R","SW Prelims"
8.30,"RIGOLINI R","SW Prelims"
9.30,"RIGOLINI R","SW Prelims"
8.90,"RIGOLINI R","SW Prelims"
8.90,"RIGOLINI R","SW Prelims"
9.20,"RIGOLINI R","SW Prelims"
9.30,"RIGOLINI R","SW Prelims"
9.20,"RIGOLINI R","SW Prelims"
-------------------------------------------------

What I really want is just the returned row count - e.g. 11

(I am trying to report the number of times a non-unique value of Eqp1Voc
occured)

Can this be accomplished, and if so, How?

TIA

Wayne

 

[Ofer]

SELECT SWP.[Equip 1 Judge], SWP.Contest, Count
(SWP.Contest) AS CountOfContest
FROM DateParm
GROUP BY SWP.[Equip 1 Judge], SWP.Contest

that will return one line
"RIGOLINI R","SW Prelims",11

Adding the feild SWP.Eqp1Voc wont do the trick, because it
unique for each line

 

[Wayne Wengert]

Thanks. I'll give that a try.

Wayne

SELECT SWP.[Equip 1 Judge], SWP.Contest, Count
(SWP.Contest) AS CountOfContest
FROM DateParm
GROUP BY SWP.[Equip 1 Judge], SWP.Contest

that will return one line
"RIGOLINI R","SW Prelims",11

Adding the feild SWP.Eqp1Voc wont do the trick, because it
unique for each line

-----Original Message-----
I am using Access 2003 and running the following query:

SELECT SWP.Eqp1Voc, SWP.[Equip 1 Judge], SWP.Contest
FROM SWP
WHERE (((SWP.Eqp1Voc) In (SELECT [Eqp1Voc] FROM [SWP] As Tmp GROUP BY
[Eqp1Voc] HAVING Count(*)>1 )));

Which (correctly) returns 11 rows as follows::

-------------------------------------------------
8.30,"RIGOLINI R","SW Prelims"
8.50,"RIGOLINI R","SW Prelims"
8.30,"RIGOLINI R","SW Prelims"
8.50,"RIGOLINI R","SW Prelims"
8.30,"RIGOLINI R","SW Prelims"
9.30,"RIGOLINI R","SW Prelims"
8.90,"RIGOLINI R","SW Prelims"
8.90,"RIGOLINI R","SW Prelims"
9.20,"RIGOLINI R","SW Prelims"
9.30,"RIGOLINI R","SW Prelims"
9.20,"RIGOLINI R","SW Prelims"
-------------------------------------------------

What I really want is just the returned row count - e.g. 11

(I am trying to report the number of times a non-unique value of Eqp1Voc
occured)

Can this be accomplished, and if so, How?

TIA

Wayne


.

 

[Wayne Wengert]

Actually, that doesn't work - it returns all rows. I want a count of only
those values in Eqp1Voc that occur more than once.

Wayne

SELECT SWP.[Equip 1 Judge], SWP.Contest, Count
(SWP.Contest) AS CountOfContest
FROM DateParm
GROUP BY SWP.[Equip 1 Judge], SWP.Contest

that will return one line
"RIGOLINI R","SW Prelims",11

Adding the feild SWP.Eqp1Voc wont do the trick, because it
unique for each line

-----Original Message-----
I am using Access 2003 and running the following query:

SELECT SWP.Eqp1Voc, SWP.[Equip 1 Judge], SWP.Contest
FROM SWP
WHERE (((SWP.Eqp1Voc) In (SELECT [Eqp1Voc] FROM [SWP] As Tmp GROUP BY
[Eqp1Voc] HAVING Count(*)>1 )));

Which (correctly) returns 11 rows as follows::

-------------------------------------------------
8.30,"RIGOLINI R","SW Prelims"
8.50,"RIGOLINI R","SW Prelims"
8.30,"RIGOLINI R","SW Prelims"
8.50,"RIGOLINI R","SW Prelims"
8.30,"RIGOLINI R","SW Prelims"
9.30,"RIGOLINI R","SW Prelims"
8.90,"RIGOLINI R","SW Prelims"
8.90,"RIGOLINI R","SW Prelims"
9.20,"RIGOLINI R","SW Prelims"
9.30,"RIGOLINI R","SW Prelims"
9.20,"RIGOLINI R","SW Prelims"
-------------------------------------------------

What I really want is just the returned row count - e.g. 11

(I am trying to report the number of times a non-unique value of Eqp1Voc
occured)

Can this be accomplished, and if so, How?

TIA

Wayne


.

 

[Wayne Wengert]

I finally got it to work. Here is what I am using:

=================================================
SELECT Count(ID) As Dups, GE4Judge, 'Voc' As SubCap, ShowName
FROM DCScores
WHERE (((GE4Prf) In (SELECT [GE4Prf] FROM [DCScores] As Tmp
Where CircuitID = 501 AND PorF = 'P' AND UnitClass = 'SW'
GROUP BY [GE4Prf] HAVING Count(*)>1 ))
AND CircuitID = 501 AND PorF = 'P' AND UnitClass = 'SW')
Group By GE4Judge, ShowName
=================================================

Wayne