Display highest rate of record

[ghostman via AccessMonster.com]

on my query based on two tables [Trainees] & [Training Records], how can i
display the Contractor (or Company) with the highest number of NoShows?

ContractorName = Text Field
NoShow = Yes/No Field

So far i got 2 Contractors in my record.

CONTRACTORNAME TOTAL NOSHOW
Contractor 1 2
Contractor 2 5

query should display:

CONTRACTORNAME TOTAL NOSHOW
Contractor 2 5

i have this SQL:

SELECT Trainees.ContractorName, Count([Training Records].NoShow) AS
CountOfNoShow
FROM Trainees INNER JOIN [Training Records] ON Trainees.TraineeID = [Training
Records].TraineeID
GROUP BY Trainees.ContractorName;

Thanks again!

 

[Jeanette Cunningham]

Hi ghostman,
make it a Top 1 query.
In the property sheet for the query, find the property for Top Values and
put 1 for the top value.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[ghostman via AccessMonster.com]

thanks for the response

i tried what you have said but it shows the other one:

CONTRACTORNAME TOTAL NOSHOW
Contractor 1 2


SELECT TOP 1 Trainees.ContractorName, Count([Training Records].NoShow) AS
CountOfNoShow
FROM Trainees INNER JOIN [Training Records] ON Trainees.TraineeID = [Training
Records].TraineeID
GROUP BY Trainees.ContractorName;

Hi ghostman,
make it a Top 1 query.
In the property sheet for the query, find the property for Top Values and
put 1 for the top value.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

on my query based on two tables [Trainees] & [Training Records], how can i
display the Contractor (or Company) with the highest number of NoShows?

[quoted text clipped - 23 lines]

Thanks again!

 

[John Spencer]

In a top query you must sort by the value that determines the top.

SELECT TOP 1 Trainees.ContractorName
, Count([Training Records].NoShow) AS CountOfNoShow
FROM Trainees INNER JOIN [Training Records]
ON Trainees.TraineeID = [Training Records].TraineeID
GROUP BY Trainees.ContractorName
ORDER BY Count([Training Records].NoShow) DESC

The problem here is that counting a boolean field (no show) would be the same
as counting the number of records. Count counts the presence of a value and
the field NoShow will always have a value (either True or False).

You can fix that in a variety of ways. The simplest way to fix it with this
query is to add a where clause.

SELECT TOP 1 Trainees.ContractorName
, Count([Training Records].NoShow) AS CountOfNoShow
FROM Trainees INNER JOIN [Training Records]
ON Trainees.TraineeID = [Training Records].TraineeID
WHERE NoShow = True
GROUP BY Trainees.ContractorName
ORDER BY Count([Training Records].NoShow) DESC

If you have more than one contractor with the highest number of NoShows then
you will get multiple records returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

thanks for the response

i tried what you have said but it shows the other one:

CONTRACTORNAME TOTAL NOSHOW
Contractor 1 2


SELECT TOP 1 Trainees.ContractorName, Count([Training Records].NoShow) AS
CountOfNoShow
FROM Trainees INNER JOIN [Training Records] ON Trainees.TraineeID = [Training
Records].TraineeID
GROUP BY Trainees.ContractorName;

Hi ghostman,
make it a Top 1 query.
In the property sheet for the query, find the property for Top Values and
put 1 for the top value.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

on my query based on two tables [Trainees] & [Training Records], how can i
display the Contractor (or Company) with the highest number of NoShows?

[quoted text clipped - 23 lines]

Thanks again!

 

[ghostman via AccessMonster.com]

Thanks John!

In a top query you must sort by the value that determines the top.

SELECT TOP 1 Trainees.ContractorName
, Count([Training Records].NoShow) AS CountOfNoShow
FROM Trainees INNER JOIN [Training Records]
ON Trainees.TraineeID = [Training Records].TraineeID
GROUP BY Trainees.ContractorName
ORDER BY Count([Training Records].NoShow) DESC

The problem here is that counting a boolean field (no show) would be the same
as counting the number of records. Count counts the presence of a value and
the field NoShow will always have a value (either True or False).

You can fix that in a variety of ways. The simplest way to fix it with this
query is to add a where clause.

SELECT TOP 1 Trainees.ContractorName
, Count([Training Records].NoShow) AS CountOfNoShow
FROM Trainees INNER JOIN [Training Records]
ON Trainees.TraineeID = [Training Records].TraineeID
WHERE NoShow = True
GROUP BY Trainees.ContractorName
ORDER BY Count([Training Records].NoShow) DESC

If you have more than one contractor with the highest number of NoShows then
you will get multiple records returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

thanks for the response

[quoted text clipped - 20 lines]

[quoted text clipped - 23 lines]
Thanks again!