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.