Use Max() ?

[sbooth]

I am creating a database for a small horseshow circuit. Here is a sample of the structure:

tblShow
ShowNum_pk
ShowDescription
ShowDate
ShowLocation

tblClass
ClassNum_pk
ClassNumber (this is the number that will print on flyers, etc.)
ClassDescription
DivisionNum_fk
Jumping (This is a yes/no field)

tblShowClass (This is a junction table between tblShow and tblClass)
ShowNum_fk
ClassNum_fk

tblDivision
DivisionNum_pk
DivisionDescription

tblPlacement
PlacementNum_pk
PlacementNumber
PlacmentDescription

tblEntry
EntryNum_pk
HorseNum_fk
RiderNum_fk
OwnerNum_fk
TrainerNum_fk
(RiderNum, OwnerNum, and TrainerNum all are fk's of tblPeople. )

tblShowClassEntry
EntryNum_fk
ShowClassNum_fk
EntryFee
PlacementNum_fk

tblPointsChart
PointsNum_pk
EntryCountMin
EntryCountMax
PlacementNum_fk
Points

If tblClass.DivisionNum_fk<>1 then I need to calculate points by class for each entry. Then a sum of these by division by entry. The entries with the 2 highest points by division are champion and reserve champion. Each division has a champion and reserve champion. I have the following queries:

qryEntriesPerClass - counts entries per class to be compared against EntryCountMin and EntryCountMax
qryPointsCalc - returns calculated points by class by entry
qryPointsByDivision - returns the sum of qryPointsCalc by division
qryPointsCalc_Jump - returns the sum of qryPointsCalc by division in jumping classes only

The results for qryPointsByDivision with my test data are:

3 13 85 85
3 15 85 70
3 16 85 40
3 18 75 35
3 17 69 34
3 26 67 33
3 14 50 0
3 21 30 30
3 52 25 25

Column1=DivisionNum_pk
Column2=EntryNum_fk
Column3=qryPointsByDivision.SumOfPoints
Column4=qryPointsCalc_Jump.SumOfPoints

Normally, the entry with the highest points in a division would be champion. However, the first 3 enties all have the same number of points. The tie breaker is the entry with the highest number of points in jumping classes. The Max function will return the highest points, but it doesn't deal with ties. Also, I need the two highest values.

I know this is confusing, but does anyone have any suggestions?

 

[twoplustwo]

This may sound simple...

Couldn't you base a query on those results that MAX the jumping class in the event of a tie?

 

[Rabbie]

Just use the TOP predicate

SELECT TOP 2 Col1,Col2,Col3,Col4 FROM qryPointsByDivision ORDER By Col3,Col4;

You may need to change field and source names to match your DB

TOP will return all tieing records so you may get more than two records returned.

More info in Access Help

 

[sbooth]

syntax

Rabbie,
Thank you for the post. I am sure that is the correct direction. However, I need the top 2 for each division. I have entered the following in criteria:

In (Select Top 3 [SumofPoints] From qryPointsByDivision  Order By [SumofPoints] Desc)

The select statement is:

SELECT qryPointsByDivision.DivisionNum_fk, qryPointsByDivision.EntryNum_fk, 
qryPointsByDivision.SumOfPointsFROM qryPointsByDivisionWHERE 
(((qryPointsByDivision.SumOfPoints) In (Select Top 3 [SumofPoints] From 
qryPointsByDivision  Order By [SumofPoints] Desc)));

How can I group this?