I have a lot of good ideas. Particularly, ideas for applications for which I never seem to have the time or motivation to start, much less complete. My latest idea is a scoring system for my golf league. Last season I would get the weekly updates on an Excel spreadsheet and I would cringe in horror. No data integrity. No formulas beyond simple SUMs. I don’t expect the commissioner to be an Excel guru, so I don’t blame him. There’s just a better way. This year, I volunteered to keep score and made all kinds of promises about how great the scoring system would be. One of the promises was that it would be complete by noon today. That ain’t happenin’.
A couple of days ago, I start putting this app together. I start in Excel – it’s what I know and what I intended to use. In a short time, I abandoned Excel for Access. When I start thinking about applications, the first thing I think about is data stores. What is the nature of the data I need to store and where am I going to store it? Sixteen golfers times 15 weeks times nine holes is 2,160. Why would I incur the additional setup of an Access database when I could simply store it in a worksheet?
I set up worksheets for Teams, Players, Courses, Schedule, Weeks, and Scores. I then set about creating a userform to populate the Scores table, drawing data from the other tables to ease data entry. That’s when I say to myself “Self, this would be so much easier in Access because all the form-to-table code is built in”. It also seems to be easier to query the data in Access. I scrap the workbook and start setting up the mdb, importing all my “tables”, so I really don’t lose much time.
Everything’s going great. I’ve created some relationships and some queries to get some of the easier data out of tables. Then I hit a snag. I need to compute everyone’s handicap with our special handicap algorithm. It requires me to know the last three scores each golfer has posted. Easy enough except for the fact that not everyone plays every week. A golfer’s last three scores could be from weeks 5, 4, and 1 and another is from weeks 5, 4, and 3. I’m not strong enough in SQL to create this query. After an hour of banging my head on the desk, I decide that the best course is to write a UDF that determines the third most recent week a golfer has posted a score. I can then use that in my query and it will be peachy.
I get done with the first draft of this UDF and I have a thought. As long as I’m writing this 40 line UDF, why don’t I just make it a 60 line UDF and have it return the handicap. Just do all the work in the UDF. It works out most awesomely. Now that I know the handicaps, I can write a query to determine who is the A golfer and who is the B golfer (two man teams). Correction: Someone could write that query, but it’s not me. Back to the module to write another UDF to spit out the golfer I want.
I have this terrific set of tables, a beautiful relationship screen, and a couple of good queries. By the time I’m done writing my second UDF to compensate for my lack of SQL ability, I start asking myself why I’m doing this in Access. If I’m going to be doing everything in UDF’s anyway, I might as well go back to Excel where at least I can write code without consulting the help file every two minutes or trying to remember the proper syntax of a where clause.
Should I use Excel as a front end and Access as a data store? That’s a possibility, but I’m not sure what I’d be saving. Instead of creating queries via the Access UI, I’d be writing straight SQL in my VBA code. That doesn’t sound too attractive. Maybe I can have the best of both worlds, though. Maybe I can use ADO when it’s easier to do in a query and I can use CopyFromRecordset to a worksheet when it’s easier that way. Keeping the data in Access will also provide some scalability when, for instance, I want to php this to a website.
You know you’re a hack when you have two half-done apps in two different development environments and you still don’t know which one you should use.
I have a simple Excel spreadsheet that uses a form to populate scores for players. Each player has a spreadsheet of their own that their scores are held on. It’s very basic at this point, but the summary page allows you to choose a course & it converts each person’s index to their handicap for that course. The formula is the USGA formula for calculating handicap, though, so you’d have to change that, but you will get the overall jist to it. I can send it to you if you want – just tell me what email address to send it to. Like I said, it’s fairly basic right now in that there’s no ability to delete players/scores/courses, but it works perfectly from what I’ve tried. It works very well. Good Luck!
I Know how you feel. I am going through the same thing on a conceptional estimating system. I just cannot make up my mind which way to go. So I guess I will change my title to Hack.
Have a good weekend
All I know is that you better get it done or the rest of the league will be less than impressed with not only your putting, but also your scorekeeping skills.
Dick,
What I know about golf and calculating handicaps is zero. However if one of your problems is getting the last 3 scores, in Access SQL you can use TOP, like this query using the northwind database:
SELECT TOP 3
Orders.CustomerID, Orders.OrderDate
FROM Orders
WHERE Orders.CustomerID = [?]
ORDER BY Orders.OrderDate DESC ;
This will give you the 3 most recent orders for the specified customer.
Does that help any?
My approach to Access/Excel questions is when I end up with several worksheets storing “tables” and having to run “queries” and lookups on them, I usually convert the worksheets into tables. If I need to do a lot of data manipulation, I will sometimes bring the results of queries back into Excel using ADO.
HTH,
Dianne
“You know you’re a hack when you have two half-done apps in two different development environments and you still don’t know which one you should use.”
I suppose it is safe to say that of all the people who frequent this site, I am the weakest…
But it sure done my old heart good to read the last sentence of Dick’s post and see the ‘pros’experience what I am faced with ALL the time. :mad:
Hope things work out for ya Dick…
Intresting, do you think .net would help? – from what i’ve seen it’s like a real easy way to get your data soucre in to your front end?
On hacks and choices…
At times, I too face doing it the “right” way or the “dirty hack” way…
I get really annoyed with situations where I’m forced to do a dirty hack just because the pressure is on.
You’ll never regret having built a solution the right way.
John: Thanks for the offer. Send it to dick@dicks-clicks.com.
Z: Hello, kettle? This is pot. You’re black.
Dianne: I tried using TOP, but couldn’t get it to work. I wanted every player to show up in the query, not just one. If it was just one, that would work, but I would need one query per player. Can you change that SQL to get rid of the WHERE clause and show the TOP 3 orders for every customer? So if you had 10 customers, it would return 30 records?
Ross: Did you forget the smiley? :) I think .net would be like using an industrial strength nail gun to hang a picture (but I’m speaking from a position of zero authority on the subject).
Dick,
In Oracle this would be a correlated subquery.
I haven’t used Access all that much at the SQL level – wasn’t quite sure what to expect.
I’ve got a table called golferstats with three columns:
golfername, gamedate, score
This selects the previous 3 scores per golfer
select g.golfername, g.gamedate, g.score
from golferstats as g
where g.gamedate in (select top 3 t.gamedate
from golferstats as t
where t.golfername = g.golfername
order by gamedate desc)
Rob
On choosing platform:
My very first decision when I start something is “do we have one-to-many or many-to-many relations in the data ?” If yes then it’s a database job and I start working.
Not saying this is Best Practice, but it’s a very easy guideline that always worked for me.
I put lots of consideration into the table layout, after some painful experiences from some years back. Lesson learnt:
Database basics is pretty easy and fun. But for a person “coming from spreadsheets” there is an extremely important difference: You can rework, sculpt, redesign, reprogram a spreadsheet solution until it’s really good, but a database must be right from the very beginning. If not then it grows useless and you must choose between a total rebuild or some really ugly workarounds.
Best wishes Harald
Ajaya, Sorry but I can’t reproduce you results in ADO classic using your data as posted. Whether the column is seen as FLOAT and TEXT, I am not getting scientific notation.
Jamie.
Oops, I posted to the wrong thread. To make amends, I’ll take a stab at that query. Avoid the TOP N syntax because it is non-standard and non-relational. So, in standard SQL that subquery should look more like this:
Jamie.
—
Is it not my day? I’ll try that query again:
SELECT g.golfername, g.gamedate, g.score FROM golferstats AS g WHERE 3 > (SELECT COUNT(*) – 1 FROM golferstats WHERE g.Score
Curses.
SELECT g.golfername, g.gamedate, g.score FROM golferstats AS g WHERE 3 > (SELECT COUNT(*) – 1 FROM golferstats WHERE g.Score IS LESS THAN OR EQUAL TO Score AND g.golfername = golfername);
I created a Golf League Manager program in Excel and been using it for several years. Not being particularly good at programming I needed help with the handicap portion. After working well all of a sudden this year it started giving some erroneous values. The premise is basic as it uses the best 4 of the last 8 valid scores. If only 4 scores are available it uses those. The Scores worksheet is managed by Date/Week and the VBA code pulls the scores, ignores any zeros(0) and text (we use initials when for subs) sorts the scores in ascending order, takes the lowest 4 and divides by 4 to get the average. I’d like to simplify this and wonder if anyone would be willing to share their solution with me for calculating handicaps. Thanks…