Fields and records moving about?

[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.

 

[golfortennis1]

So I have taken some time to study up, and also learn a different program for a different project, so this got moved to the background.

I have gotten myself to this point in the database. The forms are roughly how I want them(aesthetics aside).

So based on what I understood from these discussions, I have not done any calculating and I have kept repetition to a minimum as it pertains to tables. My thought was a query would put together my Lie and Distance to the Pin from tblShotData, and in the same query bring in the Baseline strokes value for the corresponding Lie&Distance.

I am able to get Lie&Distance together(ie. T and 410 becomes T410), but it comes down as an expression, and then I can't figure out how to get the expression to look for the baseline strokes value for T410(when I try to build, I can't even choose the expression.) So then I thought use a calculated field in tblShotData(I know, I was advised against it here), but then I run into the dual problem: a calculated field can't be the relational item, and I am at a loss as to what I can relate tblShotValues to.

So ixnay the calculated field idea brings me back to the query. I may be at a brain lock at this point. Taking the first three rows(which is the first hole on there). My Lie and Distance to the Pin are what form column 1. I can get the query to put them together. The second column is the corresponding value from tblShot Values. Columns 3 and 4 are the math I eventually want to do with the data(likely will only have one column, this is just to illustrate).

T401	3.99	=(3.99-2.81-1)	.18
F103	2.81	=(2.81-1.75-1)	.06
G14	1.75	=(1.75-1)	.75

But the pulling in is where I am stuck. I have the query getting me column 1, but the next column is the roadblock. Am I at least on the right train of thought looking to do these in the queries?

 

[Minty]

You can easily join your current query to a new one with the Shot values in it giving you this:
Query1 Query1

ShotID	RoundID_FK	HoleID_FK	Stroke	Club	Type	Lie	DistancetoPin	ShotCalc1	BaselineStrokes
1​	1​	1​	1​	d	Tee Shot	T	401​	T401	3.99​
2​	1​	1​	2​	52W	Approach	F	103​	F103	2.81​
3​	1​	1​	3​	p	Putt	G	14​	G14	1.75​
4​	1​	2​	1​	d	Tee Shot	T	520​	T520	4.54​
5​	1​	2​	2​	3w	Approach	F	243​	F243	3.47​
6​	1​	2​	3​	56W	Short Game	S	20​	S20	2.53​

I renamed Expr1 to ShotCalc1

Then you create a second query as

SELECT [qryShotData Query].*, tblShotValues.BaselineStrokes
FROM [qryShotData Query] INNER JOIN tblShotValues ON [qryShotData Query].ShotCalc1 = tblShotValues.TypeandDistance;

After that, it gets a bit more interesting, as you need to perform the calculations based on the shot order for that round and hole, which will prove quite challenging.

Little steps...