Select highest value in each year

[kitandy]

I have a Sum query analyzing show entries that reports as follows

show year (grouped by)
Entrant (grouped by)
Number of entries (sum) and sorted descending
​

I need to select from this the "Entrant" and "SumOFnumber of entries" and "year" only the entrant with the highest number of entries in each year

 

[jdraw]

You might get more focused responses if you post the SQL of your query.

There are SQL tutorials/examples and even areas to test/try some queries at
http://www.w3schools.com/sql/
Good luck.

 

[arnelgp]

air code:

SELECT DISTINCT T1.[year] , (select top 1 q1.[entrant] from yourQuery As q1 where q1.[show year] = T1.[year]) As [Entrant], (select top 1 q2.[number of entries] from yourQuery As q2 where q2.[show year] = T1.[year]) As [number of entries] FROM [yourtable] AS T1;

 

[kitandy]

Slight change of plan
Below is the SQL of the query I would now like to select the highest scoring exhibitor for each year

SELECT Q_Results_All_this_year_2_for_stats.[Sort Year], Q_Results_All_this_year_2_for_stats.EntrantName, Sum(Q_Results_All_this_year_2_for_stats.Prize) AS SumOfPrize
FROM Q_Results_All_this_year_2_for_stats
GROUP BY Q_Results_All_this_year_2_for_stats.[Sort Year], Q_Results_All_this_year_2_for_stats.EntrantName
ORDER BY Sum(Q_Results_All_this_year_2_for_stats.Prize) DESC;

 

[arnelgp]

create a query based on your last post say Q_Result_Total:

SELECT DISTINCT T1.[Sort Year],
(SELECT TOP 1 Q1.[EntrantName] FROM Q_Result_Total AS Q1 WHERE Q1.[Sort Year] = T1.[Sort Year]) AS [EntrantName],
(SELECT TOP 1 Q2.[SumOfPrice] FROM Q_Result_Total AS Q2 WHERE Q2.[Sort Year] = T1.[Sort Year]) AS [EntrantName]
FROM Q_Result AS T1;

 

[kitandy]

Just another little problem
I have a form for entering results which contains a combo box for selecting the entry class from a drop down list. This list consists of two fields. both from a table called "T_Classes"
"Class ID" is the unique number of the individual class and is the one which is stored when selected.
Class is a text description of the class.
I would like to add "Class" (the text description) to be shown in the box in addition to the number when a "Class ID" is selected

This is the sql which allows selection and storage of the Class ID

SELECT T_Classes.[Class ID], T_Classes.Class
FROM T_Classes
ORDER BY T_Classes.[Class ID];

 

[arnelgp]

on the after update event of your combo:

private sub yourCombo_AfterUpdate()
me.yourTextBox.Value = Me.yourCombo.Column(1)
end sub

replace the blue ones with correct control names.

 

[kitandy]

Thanks but being a bit slow
My combo box (containing the list) is named txtClass
not sure what yourTextBox rehers to

 

[arnelgp]

private sub txtClass_AfterUpdate()
me.yourTextBox.Value = Me.txtClass.Column(1)
end sub

yourTextbox refers to the textbox control (whaterver you named it) which holds Class description you want to retrieve. just replace it with the correct textbox control you have.

 

[kitandy]

Hi
I have entered the following (it auto entered the =
but get the error message
"The expression you have entered contains an invalid syntax"

= private sub txtClass_AfterUpdate() me.Class.Value = Me.txtClass.Column(1)

I have tried removing the = to no effect.
I have also removed the macro (on the after update) which auto updated a date field on the form again to no effect

 

[arnelgp]

you put that code on the After After event Property of your combobox.
while on form design, click on your combobox.
on the right pane (property window), click event tab, locate after update. choose Code Builder.

 

[kitandy]

Hi Thanks
Now getting error message
"Runtime 2113
The Value you enterted isn't valid for this field "

 

[arnelgp]

what is the control source of your textbox? if its not a field name, just an expression, ie:
=something..., just remove it.

 

[kitandy]

I have attached a couple of screen shots
Capture 1 shows the control source
Capture 2 shows the after update, the proceedure is the code you sent

 

[arnelgp]

ok now where is the textbox? is it in the main form or on the subform?

 

[kitandy]

Its on the main form

 

[kitandy]

Quite happy to upload whole database if that would be easlier

 

[arnelgp]

yes i think that would be the best.

 

[kitandy]

Would you like the current copy (with the changes you advised) or a backup made prior to these

 

[arnelgp]

the current one please.