Sorry I though we missed each other and my timeline is getting tight.
I am an adequate Access user and have done many designs over the years. However I lack the programming skills to get the most out of queries, reports, etc., especially when the needs require some programming skills. I can sometimes adapt code that I see for my own needs, other times not. I can do simple macros sometimes and calculated fields when the math is moderate. I can usually get around simple queries ok until I need to use more complex functions. I am rusty as I have not done much with it in a few years.
I am a little confused if I should be doing my programing statements the query? Query is preferred.
This is a DB for rounds of golf played in a 2 yr. period. It has about 12 fields which I imported from another tournament tool my club uses. I have about 2100 rounds of golf with score, date, handicap, player name, course, score, slope, position, etc.
The need is to award fixed points for where the person finished in every tournament and then sum it. There are 5 places in most tournaments but for this exercise I need to assign points to the 1st, 2nd, 3rd position.
I have a number field called “position”, for each golfer in 30-40 tournaments which is the place the golfer finished. I need to automate a process in which for each position, Access will assign a fixed # XXXXX points for each position but only positions 1, 2, 3.
For each position 1 the player should accumulate 3 points. For each position 2, accumulate 2 points and of each position 3 accumulate 1 point. And get a sum for each person.
What is the best way of handling this?
I imported all of the fields from a custom report I created in the other tournament tool we have. I exported to Excel and then appended it into the DB. I had to do this about 40 separate times, one time for each tournament.
Yes, I have created update queries. There are no examples in your last message after "do it this way Dave". What am I updating and why, if you don't mind?
That seems like a good approach. Will it create a new field in the table named Points?
My table name is XXXXX XXXXX X suspect I would replace tblPlayers with tblMain as I run the update queries?
Ok, I see if I can put this together. I would like a field in table Main that contains the points after the update query. Then I can make a calculation field in Main which will add the points together.
Any thoughts as I import new data after each tournament, maybe 1-2 x monthly. Will I need to keep using the update query?
Maybe one more thing: How do I count the number of times a person appears in the DB? I also need to count the # XXXXX tournaments they played in, which would come from their many records in the DB?
I am getting syntax errors on both of these. Tried a few changes, no luck. Here is what I did. I am using Access 2010 btw.
Did you want me to use an actual update query or a std query? Tried both. Opened a std query. Opened the "Expression Builder". The builder approach puts all of this into the field line in the query. I created a field in Table Main for Points.
Typed in the following:
UPDATE (tblMain)SET ([Points] = 3,WHERE [Fltpos] = '1'
Ran the query and kept getting syntax errors. Tried moving the paran around, etc.
Did I do this correctly?
Still getting invalid syntax errors. I have tried multiple different approaches. All in one line, in three lines, just as you offered. I deleted that open paren. Still general syntax error
This update statement is not going to change the data in Fltpos, correct?
SET [Points] actually does what? Does this create a new field in the query so that we are not modifying the data in Fltpos? I need to have this data unchanged as I may have to experiment with the # XXXXX points for awhile.
The Fltpos is defined as a numeric field,FYI. Still need " 1"
Yes, great, thank you. That is what I want to do.
I have a field in Main named Points and is set to numeric.
The complete statement still has invalid syntax.
File ID is 545262
Don't know why the size of this test version is about 1/3rd the size of the production version. It looks like it has all of the data. I tried the query in a few versions and same syntax error - operator with no operand.
It would not save it because of the syntax error, so it is not present. It was a new query with just that one statement in it.
Go ahead and create. I was wondering should I be putting the content into the expression builder , which goes into the first line of a query or should I be putting it into the SQL
Thanks Richard! You had the points distribution wrong but I fixed it
I tried to add the other two situations into the same query but it didn't like it.
Do I have to create three independent queries to update the three different point arrangements?
Couldn't we just create an IIF statements that will update Points?
Like this, just not sure of the syntax and also how to add a CREATE command if we can.
IIF Fltpos=1, 3, Null, IIF Fltpos=2, 2, Null, IIF Fltpos=3, 1, Null
Thanks that worked ok, created the other two update queries and it is doing what I wanted. Can you explain why you went from an SQL statement to simply using the update query?
Yes, I guess it does make sense. The query is a graphical representation for ease of use and it actually builds the SQL which does the work, is that right?
You have been great to work with Richard. Thanks for all of the info and your patience.
Where are you located east coast US or further east of that? I am in Calif
Thanks again! I live near Foster City, in Belmont, just south of San Mateo. I have been in the computer field for many years, mostly in a sales/management capacity. I sell professional services to IT for many years. Many large projects to large companies in the Bay Area in the $1M plus sized jobs. Between jobs at the moment but expect to ramp up my search now that summer is just about over.
This is a project for the golf club I belong to in San Mateo. I am the Handicap Director and we are using this tool along with others to manage members scores.
I'll drop you an email or re-connect through Answer if I need anything else.
Let's see if I can guess.....Foster City...Visa, Sprint, Franklin Resources, Gilead Sciences, Electronics For Imaging, maybe Sony, Applied Biosciences??? Did I get close?
Have a nice rest of the summer!
You take care as well.