Setting Up the Data

As I mentioned in Everything Ain’t a Nail, I’m putting together a little program to keep track of my golf league. It’s more difficult than I thought it would be, particularly the algorithms to compute the scores. As long as I’m spending so much energy on this, I might as well document my steps. I set up this category to do just that.

The first thing you need when building an app is a catchy name. Tenth Hole is the name of the league and in a flash of creative genius, I came up with Tenth Hole Tracker. A google search on that phrase turns up no hits, so I’m solid in the trademark department.

Here’s how I set up the tables

Access relationship window

When I originally set up TblPlayers, Player was a String and was the key field. Why would I need an autonumber when I could use the player’s name? People won’t be changing their names, will they? In retrospect, that was a pretty stupid decision. First, of course people change their names. Like when women get married. Second, substitute golfers don’t write their first and last name on the score card. They just write their first name and they may write “sub” to let me know they’re not a regular. Now that I know a certain last name, I want to change it but it would be a major doing.

Also, a mistake could be made when entering the golfers’ names. Maybe I don’t notice the mistake for a few weeks, but want to change it when I do. But I have all these incorrect keys in my transactions table. What a pain.

I added the PlayerName field and changed Player to an autonumber field. It wouldn’t let me do it because I have data in the table already. So I had to copy the structure, make the change, then do an append query. A little renaming later, and I was in business. I was in the business of finding and fixing all the errors this changed caused. Relationships screwed up. SQL statements screwed up. VBA screwed up. I vow, from this moment forward, to ever use fake, contrived, meaningless, autonumber-esque key fields.

Other notes on the setup:

I went with three email fields rather than a separate one-to-many table. This is a hard limit that could be unlimited if I had done the separate table. Sometimes I think it would be better if everything was unlimited, but I don’t know if it’s worth it.

The Sub field is a Boolean that’s true if the golfer isn’t a regular member of a team. I could get the same information by joining this table with TblTeams, which I probably should have done. I may delete that field.

The Holen fields hold the handicaps for those holes. That number is necessary to calculate the points. I added those fields when I started working on the points algorithm. Poor planning, I guess.

Everything is based off a week number. I wanted to correspond the week number with a date, although I haven’t had cause to use it yet. Sometimes I underplan; sometimes I overplan.

I started with a structure that had a Hole field and a Score field. I changed it to Hole1, Hole2, etc. In our league, we play nine holes. Never more, never less. I may regret that decision when I want to scale this to an 18 hole league, though.

By the way, I decided to keep the data in Access and build the UI in Excel. Next I’ll start showing the functions I’m writing to get the information I need. As always, comments welcome.

Posted in Uncategorized

4 thoughts on “Setting Up the Data

  1. I am very interested in your progress and am willing to help you out. I have a little Excel programming and Java knowledge, though little experience with Access.

    I, too, am running a golf league based on match play and have attempted to do the same thing with a Java program. It became to much of a bother, so I use brute force in excel (no macros or vba yet).

    Let me know if I can assist.

    Hope you keep us all informed.

  2. Dick,

    There are many reasons not to use strings like Player’s Name as a Primary Key. Chief among them is if you have two players with the same name and primary keys must be unique. In addition, joining tables is quicker with a number field than a string field and the number fields take up less space.

    Good luck with your progress.

  3. Combining the powers of Excel and Access, that’s exactly what I am doing to keep on top of really complex data. With Excel you can solve any numerical problem, as long as you sort of disconnect it from the rest of your data. Access, on the contrary, wants you to connect as many data as possible, however it has very little calculating power. The secret of managing your data correctly is to systematically import the calculated data from Excel into Access.

  4. I hope everybody reads frank’s words with care – he’s got it exactly right.

    I’ve been making a living for years as a one man shop doing just that.

    Leverage Excel on Access and vice-versa. It’s the way to go.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.