Problem Sorting info

[horstmt]

I am making a db to handle my bowling scores. I can figure out how to set up a query to track my highest game and highest series. Any help would be appreciated, If you need to see the database I can send it to you.

 

[boblarson]

why not just zip it and post it here?

 

[pdx_man]

And your question is ...

 

[horstmt]

File

My question was on how to make it sort the scores highest to lowest and just display the highest. It says that the file is to big even when it is zipped. Maybe I can e-mail it to you?

 

[boblarson]

If it is too big to be zipped, from your description of what it does, I'd say you either have a bunch of photos included, or you need to compact the database prior to zipping.

If it is too big to post here, it would be too big for my email address to accept.

 

[pdx_man]

Couple of solutions:

1) Create a query and add the fields you want displayed. Look at the 3rd row in the grid below. It reads Sort. Select Descending. Run the query. Save the query. It will now be available to Forms or Reports to do with whatever you want, sorted in that order.

2) For quicky reference, you can open up the table, click anywhere in the field where your scores are, and click the Sort Descending button up in the toolbar. It looks just like it does in Excel ... Z over A with an arrow pointing down.

HTH

 

[jostrep]

Tip

I'm building a bowling db too. Try sorting -descending and in the number of values type 3 that should do it it . You can include these results as a sub-report. If you know a way to calculate the peterson points feel free to let me know

Hpo this help you

 

[horstmt]

Here is the file.

Please take a look at it and let me know what you think. It doesn't have any pictures in it unless you are counting things like text boxes and labels.

 

[Jon K]

Assuming you want to return the HighGame and HighSeries for each BowlerID, try this query (it would be easier to directly type/paste it in the SQL View of a new query):-

SELECT BowlerID, IIf(max(Score3)>IIf(max(Score1)>max(Score2), max(Score1), max(Score2)), max(score3), IIf(max(Score1)>max(Score2), max(Score1), max(Score2))) AS HighGame, max(Series) AS HighSeries
FROM [Scores Query]
GROUP BY BowlerID;


The query is based on your [Scores Query]. It should return HighGame 231, HighSeries 633 for BowlerID 3 as shown in the Bowlers table.

Hope it helps.

 

[horstmt]

The SQL statement works fine for returning the stored value but is there a way that I can get it to change the value so that when I bowl a higher game or series it will update the stored value? Also would I do the same thing to return the value for the high for each game? Thanks for all of the help.

 

[Jon K]

The posted query does not return the stored values from the Bowlers table. It just pulls the most recent results from your Scores Query. In fact, you don't need to have the HighGame and HighSeries fields in the Bowlers table, as their up-to-date data can be obtained by just running the query.


If you need to show also the Bowlers' names and sexes in the query results, you can save the posted query as qryHighestGame and then join it with the Bowlers table in another query e.g.

qryHighestGame_WithNames:-
SELECT [Bowlers].[ID], [Bowlers].[Name], [Bowlers].[Sex], [qryHighestGame].[HighGame], [qryHighestGame].[HighSeries]
FROM Bowlers LEFT JOIN qryHighestGame ON [Bowlers].[ID]=[qryHighestGame].[BowlerID];


Note.
(1) On further study, I find that in your Scores Query, the tables are joined by Scores.ID = Bowlers.ID. They should be joined by Scores.BowlerID = Bowlers.ID.

(2) A text field, instead of a date field, is used to hold the dates in the Scores table. The text field will present a problem when you want to sort or retrieve data by dates.

 

[horstmt]

The returned value won't change. If I enter a higher score it still says the same thing. I can't get it to update the new high score or series. Also will the same SQL statement work to calculate high scores for game 1, 2, and 3?

 

[Jon K]

In the attached DB, I have modified the query qryHighestGame to include the calculation of high score 1,2, and 3. So now HighGame will be shown even if only one score is entered in the Scores table.

As Series is set as [Score1]+[Score2]+[Score3] in the query "Scores Query", HighSeries will be shown only when score 1,2 and 3 are entered.

However, if you want to show HighSeries even if only one score is entered, you can simply change it to nz([Score1],0)+nz([Score2],0)+nz([Score3],0) in the query "Scores Query", as query "qryHighestGame" is built on "Scores Query".

Hope it helps.


As I am not familiar with the terms Game, Score1,2,3, Series, HighGame, HighSeires as used in bowling, I built the queries by studying the connections of the figures HighGame 231 and HighSeries 633 in the Bowlers table with the figures in the Scores table and the query results of the "Scores Query". I hope I haven't messed things up.