Fields and records moving about? (1 Viewer)

[golfortennis1]

One thing that may make this a little easier to understand and keep you data straight is to use what is called a Natural key. I know this is opposite from what was suggested with just using autonumbers. But this is a case where I think it would help. This would work well for courses and holes because the tables are relatively small. There are only so many golf courses. This looks like PGA courses so the list is

PGA Courses

Although we tell people do not worry what the key looks like (you should never see it), if you are importing data from other sources and need to do data validation it may help. I am still convinced that your hole data was wrong in the shot table and it was hard for me to see what happened. But it appeared you picked the ID for the right number hole on the wrong course. Natural keys would have made this either to understand.
A key needs to be:
Unique
Non changing (except very rarely)
Known at data entry
Simple
No unique characters

This is not a hard change if you want to do it. It requires an update query and a little manual entry.
1. Give each course a unique readable identifier like we do with airports (SAN San Diego, DCA Washington National).
AUGS Augusta
PINE Pinehurst
ANGN Angus Glen North
ANGS Angus Glen South
etc.

2. Call that new field CourseID. Call the old CourseID OldCourseID (have to do that in reverse order). It will be needed to create the update to fix the joined tables
These new keys are not as efficient as an autonumber but still very good keys
3. You will have to break the relationship diagram and then put it back together after all changes
4. In the Hole table do something similar. Create new HoleID (string) and name the old HoleID OldHoleID
Now either manually or using an update query create your hole ID as follow. Not to hard now to do it manually.
AUGS1
AUGS2
AUGS3
AUGS4
....
PINE1
PINE2
PINE3

5. Now have to do the same for the FK in the shot table.
Create a HoleID (string), and rename the old HoleID_FK OldHoleID
This one has to be done with an update query to update the new hole id PINE1 where the oldHoleID was 1
6. Oh yeah you will have to do the same thing for Round. The CourseID_FK is now a string. This can be done manually. Where it use to store 2, but AUGS in new ID field.

None of this is required, but may help you with doing the relationships and seeing how it comes together. You would not have made the problems with the hole assignment. Instead of seeing hole id 27, you would AUGS9. It gets real confusing when looking at tables with nothing but foreign numeric keys until everything comes together.

If you like that ID, Create the fields in the Course, Range, Hole tables and populate manually. Rename and leave the old fields. Send it back and will show you the update query for the shot table.

So I must post a mea culpa: re:the hole listing, I thought I had mentioned this already, but upon further review, I had not.

In the first file I posted, I wanted to randomize the names of the courses I had played so as not to reveal any location data. Rest assured I have not played any of those courses ever, and if you are a golfer, your efforts certainly would earn you an invite to one of them if I had anything to say about it. But in my haste, I simply randomly put names against rounds, and did not pay attention to the actual distribution. I do apologize for the effort this caused. I certainly did not think anyone would spend near as much time as you have on my little db.

As this was from an excel file, I had run a lookup to assign the HoleID numbers to the course listing, and the imported. I have trashed probably 7 attempts at a new db, as I found it easier to start over to get it right, and in part because of your (and some others') advice, I broke up the data into smaller chunks to maintain as much relational integrity as this could possibly have. So I understand if you decide this was a bridge too far, and again I apologize, however, unless there is a step in form/query/report future that it could mess up, don't spend any time on the holeID and the shots. Perhaps foolishly I am doing everything twice(once for the actual DB, and once for the one I post here), as more practice = better leaning, and I am pretty confident that I have the holes and courses assigned to the proper shots.

I will look over what you posted and try to digest it. Frankly I haven't had much opportunity to look at your latest db work so I will be looking at that as well.

 

[MajP]

No problem. As I said, I am only demonstrating concepts not trying to define a solution. If you are still playing with the structure then may want to consider the Natural key idea. It will keep you sane. Especially having to import from somewhere.

 

[MajP]

To further drive home the point that display and storage are two separate things. Here is another view of the data in a Treeview. This proves that you data is properly organized for me to do this easily. Helps to show the Hierarchy too.

 

[Minty]

@MajP As a golfer, I should warn you that the courses and hole numbers can get a little tricky at some courses.
A not uncommon situation is where a course has 3 or 4 "Nines", let's call them the Red, Blue, and Orange nine hole courses.

These can frequently have separate cards for each eighteen-hole combination, and as such the holes will have different stroke indexes and hole numbers, depending on the combination used. So Red/Orange will have one set of stroke indexes, Orange/Blue another, and Blue/Red as a third set.

You would effectively have to store each combination as a separate course at that site.
Not difficult to handle but something to be aware of.

 

[MajP]

These can frequently have separate cards for each eighteen-hole combination, and as such the holes will have different stroke indexes and hole numbers, depending on the combination used. So Red/Orange will have one set of stroke indexes, Orange/Blue another, and Blue/Red as a third set.

I am not a golfer, but do walk my dog every day on the course so that is the extent of my knowledge.
What is the smart way to address this? Are these combinations limited? In other words can you make multiple "courses" Augusta (Red/Orange), Augusta (Orange/Blue) and add the hole details for each course? Or do you need a many to many junction table for a Course_Card? It seems you are suggesting the prior.
I am on this site for entering golf stats and see these courses

I believe the colors are the available Tee positions and not what you are talking about. I see lots of different variation of Pinehurst but also when I view any of those it shows multiple possible cards for each Tee with different information. What is that?

 

[Minty]

Correct - The colours above are the different Tees. They sometimes do affect the par on a course but that is rare (in the UK at least), but for the OP's use we can almost certainly ignore that particular added complication, as it is normally a different tee for the ladies, and sometimes a different par.

The different Tees are a version of the same course with different yardages. At my home course there are White tees (Back tees, most difficult, generally used in Adult Mens competitions), Yellow tees (Generally, shorter distances but still Adult Male use often for non-competition play.) There are then Red tees which are generally used for Female competitions (Often shorter than the white or yellow, but not always and with different pars and stroke indexes) and finally, we have some Blue tees that are much shorter for younger juniors.

As you correctly surmised, the different courses when they are split into Nines are normally limited, and I would simply add the different available combinations as different courses, as for scoring purposes they effectively are. (Augusta Red/Orange)

 

[golfortennis1]

What I do actually is simply treat each set of tees as its own course. At my home course I tend to play different tees depending on time of year, playing partners, etc., So I would have Augusta National Blue, Augusta National White, Augusta National Green all as separate courses. As I mentioned, my actual data is different from posted, and in fact of 28 rounds 24 of them are played at the same facility, broken up into two main tees played, with a couple from a third.

 

[MajP]

So it sounds as the DB design is logical to support this. You create multiple "course" records for the same location and then create individual hole data for each "course.

The only thing then is repeated data for a course if you were adding a lot more meta data about a course. That is not an issue currently because all you store is number holes, slope rating, course rating, address. So easier to simply duplicate that information

If you were storing more information like phone number, web site, course manager, etc etc that is generic then you would want
tblParentCourse
- data common to the real golf course
tblCourse
- basically what you have now except the address in the Parent course table

So Augusta National Green would relate to Augusta

 

[golfortennis1]

So it sounds as the DB design is logical to support this. You create multiple "course" records for the same location and then create individual hole data for each "course.

The only thing then is repeated data for a course if you were adding a lot more meta data about a course. That is not an issue currently because all you store is number holes, slope rating, course rating, address. So easier to simply duplicate that information

If you were storing more information like phone number, web site, course manager, etc etc that is generic then you would want
tblParentCourse
- data common to the real golf course
tblCourse
- basically what you have now except the address in the Parent course table

So Augusta National Green would relate to Augusta

The address and the number of holes are the only repeaters anyway, as course and slope ratings are all specific to the tees. I'm not even sure I will keep addresses, I was just trying to show what a table might look like. I do play the majority of my golf at one location, so those things don't really mean much to me. Although I suppose if I were to share it later(the framework), maybe somebody would want that stuff.

The excel file started out as a couple of different guys on a golf forum putting stuff together, and then some other guys did some things. A lot of people want to use the apps now, but I find a)this is an opportunity to learn something that could be useful in the future(access to a pretty advanced degree), and b) the apps don't tell them what they think they tell them. But I may put this db out there for others to use/modify/whatever once I've completed it.

 

[MajP]

The excel file started out as a couple of different guys on a golf forum putting stuff together, and then some other guys did some things. A lot of people want to use the apps now, but I find a)this is an opportunity to learn something that could be useful in the future(access to a pretty advanced degree), and b) the apps don't tell them what they think they tell them. But I may put this db out there for others to use/modify/whatever once I've completed it.

A lot of us here have lots of example forms and code to make this into an application
Once the tables are locked down and you finish your shot entry table you probably want to do things like:

1. Open the database to a switchboard where you can select different forms, reports, There are some reusable examples on this forum. I tend to role my own. Here is an example I use. The database opens without any ribbons or navigation. The users can get to the forms, reports, exports, and admin. The Default data section opens the forms to update a lot of the "reference" lists. In your case Course, Clubs, Types, Lie, Shot_Values.

2. Create Other forms for entry.
Add edit course
Add edit rounds
Maybe add edit clubs, type, and other reference tables
3. Create reports to include graphs
You can do summary data analysis and trends not just dumping records.
4. Maybe create exports to excel for doing additional graphing and sharing information with others.
5. If you plan to share this database and want to work on this collaboratively I would split the database. Tables only in a seperate database and everything else in the Front End database. This will allow you to make changes to the Front end (add bells and whistles) without screwing with real data. You link to the backend and have dummy backend and a real backend. Then you can share the dummy back end. When you give the db to someone you give them a FE and a blank BE. If on a common network multiple people can link to the same BE and work in the same data from different FE. I have code to automate linking and relinking.

 

[golfortennis1]

So trying to build a form, and not sure what is happening. I click form design, and I don't have header/footer showing. I right click and add those. Bring in a combo box that I place in the header, and connect to it's data source. Switch to form view to check it, and I can't see it! If I put it in the detail section everything does what I want, but the header seems strange. Any ideas? Thanks.

 

[MajP]

Make sure it is a Form Header and not a Page Header

 

[golfortennis1]

Thank you.

 

[golfortennis1]

So I am possibly overthinking this, but here goes:

In trying to create my form to input data, I reckon I don't want to just do a form based off tblShotData, because then I can't "control" when it comes to things like HoleID. But when I build from scratch, I can get a CourseName and Hole chosen(the ID linked to the course), but I can't get the data to add to my tblShotData. Access crashed on me so I couldn't retrieve it, but I thought I had put something about recordsource to link it back, but it would not add.

To refresh:

I'm looking to build a form that adds data to tblShotData, while having to choose a CourseName from tblCourseListing in the form header, then a cascade combo box to select the corresponding hole, select a club, select a type, and select a Lie, as well as a RoundID. There would be 2 text boxes for stroke and distance, but not linked from any table.

My first go at it gave me a new record in tblShotData, with a RoundID and a course name, but all other fields were blank, even though I could choose from the listings of each table. I feel like there is something simple I'm missing to get them attached to the tblShotData table, but I'm stuck somewhere.

 

[MajP]

I am guessing you are entering all the shots for a given round. In the header you need unbound combos for Course, Round. This way you do not have to reselect round each time. In the detail you have bound combox for hole, but that is also cascaded for the row source. Then combox for all others.

 

[MajP]

See demo. FYI, the subform is not complete. Need to update the other combos beside the hole combo.

 

[golfortennis1]

So if I have a blank form, I add in combo boxes that grab items from various tables, but want to have the form feed a specific table, how can I do that? I've tried the auto-form variations, but unless I am completely missing something, it isn't giving me what I want. Is it as simple as this? (from clicking record source on the Form Property Sheet).

 

[MajP]

You can make a tabular form off of the shot table. Probably want it sorted. You can use the wizard. If you do it will creat text boxes where you need comboboxes. You can right click the text box and chooses "change to combobox". Now edit the row source of the combo.
Round Id and hole Id are tricky. You will display a round date or other descriptive information, but save a key value. I might display 18th Augusta but store holeID key 27.

 

[golfortennis1]

You can make a tabular form off of the shot table. Probably want it sorted. You can use the wizard. If you do it will creat text boxes where you need comboboxes. You can right click the text box and chooses "change to combobox". Now edit the row source of the combo.
Roundid and hole I'd are tricky. You will display a round date or other descriptive information, but save a key value. I might display 18th Augusta but store key 27.

OK thanks. I will test that out.

 

[MajP]

So if I have a blank form, I add in combo boxes that grab items from various tables, but want to have the form feed a specific table, how can I do that?

Besides using a wizard which I do unless I have a template form. I use the wizard to first select a query or table to use for the form. Then pick which fields to show. It will make a very rough starting point that you can edit. But it will have a control for each field you want to display.
If you instead start with a blank form, the next step is define the recordsource. Now you have a "bound" form. The form is bound to your table or query recordsource. Now when you add controls you set the control source to one of the fields in the recordsource. Now that control is "bound" to a field.
Once you add that recordsource you can drag available fields onto the form. It will create a control See the selection on the ribbon for adding fields. Some controls you can change. So if you want to change a textbox to a combobox or listbox right click it. A yes no field can be changed to a checkbox, toggle button, textbox, or option.