[Solved] Add a value to results of a query

[Babayaga]

Still working my golf score tracker db. I have another query ?.
With some help, I was able to solve my subtraction query ? from earlier in the week.
New problem;

I now have "Hole 1 Actual - Hole 1 Par" working with test data. In my current test case I have the result of the equation = 0 which means net result was a par.

What's the best way to tie results as follows;
When the net = 0, I want a new field called Hole 1 Pts to get calculated based on a lookup table (or an easier method like a query).

The table has following fields and values where based on how far your net is from par determines how many points you get, if any.
Example - a birdie will result in a net -1, which equals 4 points, a bogie will result in +1 which is bogie and will be worth 1 point.
1 row
Birdie 4, Par 2, Bogie 1 Eagle 6 and GT Bogie 0

In my test case above I had a par and want to have the Hole 1 Pts field for this to get updated with 2
Any suggestions on best way to attack this?

[UnklDonald418]

To a query of your table add:

Code: Select all

 (CASE WHEN "NetScore" = 1 THEN 1 WHEN "NetScore" = 0 THEN 2  WHEN "NetScore" = -1 THEN 4 WHEN "NetScore" = -2 THEN 6 ELSE 0  END) as "Points"

to display the Points according to the plan you described.

According to the rules of good relational database design (called Normalization), storing the results of a calculation in a table field may not be a good idea. It is usually better to do the calculations at run time.
Storing the results in a table field is accomplished by executing an SQL UPDATE which in turn requires a coded Macro, something to be avoided whenever possible.

[Babayaga]

Thanks for helping with this. I sort of understand what's going on but I'm confused with where to place this query?

I created a table view and I have the net scores for 9 test holes in a table called Points Calc with the following fields;
Date, Course Name, "Hole 1 Actual" - "Hole 1 Par" and repeated for each of the 9 test holes.

When I open the table and run the query, it generates the
Hole 1 Delta result, repeated for each of the other 8 holes using this "Hole 1 Actual" - "Hole 1 Par" formula to obtain a net for each hole.

Is this where I place the code you provided? Also, where (what field) gets populated wilh the results of the query? Do I need to make another field or will the query take care of it. That's the part I'm unsure about.

I tried pasting some stuff in here, but it will not let me. Not sure if it's because i'm a new user or operator error.

[chrisb]

@Babayaga,

download the attachment and take a look at the query 'qScorecard.sql' which shows how to utilise the CASE statement.
note that i only cater for a max of three shots over/under par, if you are a rubbish golfer then you may wish to add additional Bogeys.

i am not a golfer but looked online & added two courses Augusta National & Valhalla Golf Club, the value of par is accurate for all holes.

i suggest that you take a look at the structure i.e menu:>Tools>Relationships.
this demo is normalised, my field names do not contain spaces.

[Babayaga]

This is very impressive.
I will have to dig into it to see if I can decipher what you have created. Thanks so much. I'm sure I'll have more ? as I get into it a bit more.

[Babayaga]

I have a copy of what I'm playing with attached. I already have data from 6 or 7 previous years which is not in this db I posted.

I was already tracking my scores, pars, birdies, etc via the entry form. But all the entry was hand counted from my scoring app on my phone. The tweaks I have been working on for this year include the PAR value for each course so I can automate my score to PAR and my point total. If you could possibly take a look at what I have and insert the CASE statement into a query so I can see an example using my data that would get me nudged in the right direction. Thanks for your help, and if you notice any opportunity for improvements, please let me know, I know enough to make most of the stuff work but that's why I'm on this forum, to help with my lack of knowledge in certain aspects of the tool.

[Babayaga]

I do have another ? regarding the db you posted. This is a pretty slick setup you provided and if I can accomplish the following, I will adapt this db to suit my requirements.

Since I have 6-7 years worth of data & scores, is there an easy way to import my history into the existing tables? The data for the course names is in a table. I know I'll have to fat finger the Course PAR info in because I don't have it in my db presently. That's what I was trying to fix for this year.

How would I go about adding info to the setup form and be able to track the following;
We play a point game, thus the reason for tracking point and we play for money. Usually it's $10 per round. In my current db, I have a field "Cost of Game" & "Total Winnings" for that date and I like to track total spent & total winnings. The form collects "Cost of Game" and a query sums the total for the game and winnings.

[chrisb]

your data is incompatible with the attachment which requires both the number of shots made & value of par for each hole played.

the table "tbl_TES_Golf_DataEntry" has one record for each round of golf.
it shows the number of Eagles, Birdies, Pars etc. for the round.
we can calculate the points total from this data using simple addition & multiplication:

Code: Select all

select
	"Date",
	"Location",
	"Eagles",
	"Birdies",
	"Pars",
	"Bogies",
	"Greater than Bogie",
	"Eagles" + "Birdies" + "Pars" + "Bogies" + "Greater than Bogie" "HolesPlayed",
	"Eagles" * 6 + "Birdies" * 4 + "Pars" * 2 + "Bogies" "Points",
	"Actual Points"
from
	"tbl_TES_Golf_DataEntry"
where
	"Eagles" + "Birdies" + "Pars" + "Bogies" + "Greater than Bogie" > 0

to produce a scorecard we require the value of Par for each hole played and the number of shots the golfer made before sinking the ball.
i see that you have thought about this and added two tables "tbl_GolfCourse_Par" & "tbl_GolfCourse_Actuals".
you store "Hole1", "Hole2" etc. in a single record, fine for a spreadsheet but not for a database, each value should occupy its own row (record) in the same column & each record must be uniquely identifiable.

it should be easy to add money tracking to the demo provided you can find the space in the form 'fRounds', it needs to be linked to "tRoundHeader"."ID".

[Babayaga]

Outstanding!!!!
I used that sql in my original db with my old data and got it to generate a result set.
Thanks for the nudge in the right direction.

As for fixing the tables, can you please provide an example of your suggestion for both, I think I get your point but before I go making more tweaks, want to be sure I'm fully understanding.

I will alter the actual score table as you suggest and then try to fix the money portion of the form.

[chrisb]

we can not analyse the golf data in your db due to insufficient data.
you have not recorded shots per hole.
we don't know and are unable to calculate the total number of shots for the round because the number of shots played which were 2 or more over par are unknown.
your focus was on points scored which was a critical misjudgement.

my little demo focuses on the golf & was put together quickly with the sole purpose of demonstrating a practical CASE statement, unfortunately it was incompatible with your db.

using the demo:
first i use the form 'fSetup' to store Course Name & auto generated ID, HoleNumbers 1 to 18 and the value of Par (sourced online) for said hole.
i only need to do this once.

so i play a round of golf and make a note of Date, Course, number of hole played & number of shots taken to sink the ball on that hole.
i get home and spend less than a minute inputting the info contained in my notes.
using a stored sql statement i can produce a scorecard & calculate a points value using an agreed formula.

i have made some minor improvements to the original attachment.

[Babayaga]

This is an awesome example. I see how each row for the score is captured instead of in a column, it makes more sense. I will mess with this to add my historical data and get ready for 2024. I'd buy you a beer if I could. Excellent guidance and I truly appreciate your effort. Thank You!!!