Select Specific Record and Updaate (1 Viewer)

R

rayh151

Registered User.
Local time
Yesterday, 23:23
Joined
Apr 5, 2015
Messages
11
Hi,
I would as Beginner appreciate some assistance.
I have created a database to calculate our Golf Society Scores and I would like to automate Handicap Adjustments (purely a Society procedure). I require to apply a Deduction to 1st, 2nd , 3rd place and apply a Credit to all others, say 0.1.
For this I have created tblDeduct (DedID, DedMemID, DedDate,DedPrize & DedValue).
My thinking that I first update all tblDeduct to the Credit value and then update the 1st, 2nd & 3rd Highest Scores from tblPoints and then update DedPrize to 1st Prize and DedValue to a number value. Again, the same process to 2nd Prize and 3rd Prize, so my query is how to select and update specific record in tblDeduct and based on result in tblPoints.
I hope I have described my problem adequately and placed in the correct Forum category and please do make it as basic as possible or point me to a previous solution and or Sample Database.
Thanking you in anticipation.
Rayh151
 
M

Micron

AWF VIP
Local time
Today, 02:23
Joined
Oct 20, 2018
Messages
3,478
My thinking that I first update all tblDeduct to the Credit value and then update the 1st, 2nd & 3rd Highest Scores from tblPoints and then update DedPrize to 1st Prize and DedValue to a number value.
Click to expand...
What that really means is not clear to me. Regardless, that sounds like storing of calculated values, which usually is not recommended. Perhaps your forms/reports should be applying a factor (handicap value or whatever) to the value (score?) in question and displaying the result. This will help avoid issues that can arise when calculated data is not properly adjusted when parameters change.
 
R

rayh151

Registered User.
Local time
Yesterday, 23:23
Joined
Apr 5, 2015
Messages
11
Hi Micron,
Thanking you for your prompt response.
Yes – I do understand the difficulties in outling both the project and methods to be considered in such a brief forum.
So let me be brief. I am managing a Seniors Golf Society – tblMembership about 200 members – we hold about 40 events each year and attendances vary from below 10 to over 50 (weather factor in Ireland). Each event requires update of Handicap of Deduct to 1st, 2nd and 3rd, plus credit to remainder.
Credit to all default value OK, but update to Deduct value for 1st, 2nd and 3rd and based on results from tblPoints my problem.
Hope this makes sense and thanking you in anticipation.
Rayh151
 
M

Micron

AWF VIP
Local time
Today, 02:23
Joined
Oct 20, 2018
Messages
3,478
Sorry, no. I'm somewhat of a duffer myself but can't understand this situation. Maybe if you show some raw data plus the desired outcome it would help.
 
R

rayh151

Registered User.
Local time
Yesterday, 23:23
Joined
Apr 5, 2015
Messages
11
Hi Micron,
Once again thanking you.
I now attach copy of todays tblDeduct, both pre and post my manual update and hope this can clarify what I am trying to achieve.
Thanking you
Rayh151
 

Attachments

  • tblDeductPostUpdate.JPG
    tblDeductPostUpdate.JPG
    54.1 KB · Views: 63
  • tblDeductPreUpdate.JPG
    tblDeductPreUpdate.JPG
    48.5 KB · Views: 69
M

Micron

AWF VIP
Local time
Today, 02:23
Joined
Oct 20, 2018
Messages
3,478
So if

DedPrize = "1stPrize" make DedValue = -1.0
DedPrize = "2ndPrize" make DedValue = -0.6
DedPrize = "3rdPrize" make DedValue = -0.3
and this isn't multiplication (e.g. 0.1*-3, or 0.1*-6) it's just a straight conversion; and if you change your mind and alter 1stPrize to 2ndPrize it's just a straight conversion?
If so, you want to do this in a query after the values 1stPrize, 2ndPrize, etc. have been updated? Can be done by calling a function in the query or probably with a nested IFF (which I try to avoid). Question is, is DedValue text or number data, because it matters?
 
R

rayh151

Registered User.
Local time
Yesterday, 23:23
Joined
Apr 5, 2015
Messages
11
Hi Micron,
DedValue is a number of default value 0.1
Regards
Rayh151
 
M

Micron

AWF VIP
Local time
Today, 02:23
Joined
Oct 20, 2018
Messages
3,478
Well, 1 out of 4 questions is a start.
On a copy of your table, try

Code: 
UPDATE tblNameHere SET tableNameHere.DedValue = 
Switch([DedPrize]="1stPrize",-1.0,[DedPrize]="2ndPrize",-0.6,
 [DedPrize]="3rdPrize",-0.3,Nz([DedPrize],0)= 0,0.1)
I'm not sure how Switch deals with Null so that is a guess. I'm also assuming that if DedPrize is Null and DedValue isn't present, you want it to be 0.1
Don't include the line breaks unless you're dumping this into a query sql view.
 
R

rayh151

Registered User.
Local time
Yesterday, 23:23
Joined
Apr 5, 2015
Messages
11
Hi Micron,
Ah - a Function.
Had a bad Tee shot on this hole and ball gone in the rough. Will slow down play and may need to call on Caddy.
Regards
Rayh151
 
R

rayh151

Registered User.
Local time
Yesterday, 23:23
Joined
Apr 5, 2015
Messages
11
Hi Micron,
Help - what's wrong here?
Regards
Rayh151
 

Attachments

  • tblDeductCode.JPG
    tblDeductCode.JPG
    39.3 KB · Views: 67
M

Micron

AWF VIP
Local time
Today, 02:23
Joined
Oct 20, 2018
Messages
3,478
The # after -1?
Better to just copy and paste expressions rather than posting a picture, I think.
 
You must log in or register to reply here.

Similar threads

A
Linking 2 Spreadsheets And Pulling Out Specific Data
Replies
0
Views
283
AlaminK
A
A
Solved runtime error 3021 no current records
Replies
12
Views
1,670
Ashisht76
A
W
SQL Update Records Failed
Replies
13
Views
1,137
Minty
Minty
I
Solved how and where to call a function for ordinal numbers
Replies
4
Views
882
Ihk
I
L
Solved Help on Select code on combobox
2
Replies
33
Views
1,015
lookforsmt
L

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom