Fields and records moving about?

[golfortennis1]

Your shot value data could have the club ID and the distance as separate fields then you simply join on both from your shot record data.

If I understand you correctly, the problem there is there is no guarantee as to what club you are using for a given shot. F150 may be an 8 iron one day, and a 6 iron the next. The wind, elevation, where you want to leave your shot all play into what club you hit.

On the input form you input the distance you have to the pin on its own, and in the separate field input the lie. The query then joins them into the format of the shot values table in order to match up with the correct value.

 

[MajP]

If I understand you correctly, the problem there is there is no guarantee as to what club you are using for a given shot. F150 may be an 8 iron one day, and a 6 iron the next. The wind, elevation, where you want to leave your shot all play into what club you hit.

On the input form you input the distance you have to the pin on its own, and in the separate field input the lie. The query then joins them into the format of the shot values table in order to match up with the correct value.

Was this not demonstrated here a long time ago.
Post in thread 'Fields and records moving about?' https://www.access-programmers.co.u...-and-records-moving-about.326250/post-1862417
Seems like this whole thread is starting over from the beginning and just rehashing the same things already discussed and demonstrated.

 

[golfortennis1]

Was this not demonstrated here a long time ago.
Post in thread 'Fields and records moving about?' https://www.access-programmers.co.u...-and-records-moving-about.326250/post-1862417
Seems like this whole thread is starting over from the beginning and just rehashing the same things already discussed and demonstrated.

I thought I had moved ahead but then the referential integrity item was brought up. I'm assuming those who have posted read the thread from the beginning.

The referential integrity for the shotdata table will be fine, I can easily change the data to the autonumber, and then make the form do what I want it to do(and it's probably better int he long run that I do that anyway). It's the queries that have me asking questions at this point.

 

[MajP]

There are two ways lookup data can be handled, and neither one is necessarily better. The term "lookup table" for this discussion is a table with a single field of information. You have 3 ClubOptions, LieOptions, TypeOptions

You can store a foreign key and then always relate back to the "lookup table". Or you can simply store the "lookup value." My point is that the pros and cons of storing the foreign key or the actual lookup value has already been discussed. It is not as cut and dry as others have said. The decision to store a foreign key or store the actual value is based on size of lookup table, complexity of lookup data, how the data is used. Have to ask yourself do you really want to build a bunch of tables just for small lookups? Does the cost of always having to add a join out weigh the cost of storing a text value? Is there a remote chance of needing additional data fields in the future?

So I disagree with those saying you have to add a numeric key and create a relationship. I will however say it is never wrong to do that, but it may not really be worth the extra work

Imagine you have a table with clubs. The only information it stores is a club name. No need for an any ID field since it is never used (except maybe to enforce a sort order)

tblClubOptions tblClubOptions

Clubs
D​
3w​
3h​
4i​
5i​
6i​
7i​
8i​
9i​
PW​
52W​
56W​
60W​
P​
5h​

If you are confident that you will never want additional related fields (pure single column lookup) then storing the text value is easier. You simply populate a field in your data table with one of the values above. If you want "D" you store "D". However, it seems to always come back to haunt you and you realize you do want additional related information. What if you want at a later date to get a club "group type" field (Wood, Iron, Hybrid, Putter, ...). Or how about a long name display fielsd ("Nine Iron"...)

You do not have to add a join to display your data. The argument that it requires more space is correct, until you get hundred of thousands of records probably not a big deal. The names are not going to change unless they even new types of clubs. In this design relational integrity is not a thing since there is no foreign key. However, you will have to ensure that your combo box has "limit to list" so that you can not add a bogus response and you need to make the field required.

In the second method you do not store the club name value, but the Primary Key. You store 1 in the data table and not "D".
tblClubOptions tblClubOptions

ClubID	Clubs
1​	D​
2​	3w​
3​	3h​
4​	4i​
5​	5i​
6​	6i​
7​	7i​
8​	8i​
9​	9i​
10​	PW​
11​	52W​
12​	56W​
13​	60W​
14​	P​
15​	5h​

The advantage here is that it is always efficient, you can add additional related fields, you can change the value and update everywhere (5h to 5H or 5Hybrid and all records are updated through the relation.), you can ensure data integrity at the table level. However to simply display data you have to pull in the related table.
When you have a foreign key in a data table storing a primary key, you can add extra layer of data integrity by ensuring relational integrity. This way you can ensure that at the table level only a valid PK can be saved as an FK value in your data table.

Without looking I think I remember you were inconsistent with your lookups. In some cases you saved the lookup value and in some cases you saved a Primary key. It is not wrong, but may be confusing to you and others. I would pick one and be consistent.
("shot type" if you want to list shot types)
One advantage if you have a lot of small lookups and store just the value is that it can be done in a single table. So your three lookups could be combined something like this. You comboboxes pull from tblLookups and filter to the correct Category
tblLookups tblLookups

Category	Choice
Shot Type	Tee Shot
Shot Type	Approach
Shot Type	Penalty
Shot Type	Short Game
Shot Type	Putt
Shot Type	Recovery
Club Name	D
Club Name	3w
Club Name	3h
Club Name	4i
Club Name	5i
Club Name	6i
Club Name	7i
Club Name	8i
Club Name	9i

I have seen this taken to extremes. The user had a table of Priorities with values of 1,2,3,4,5. Then gave these a primary key with values 1,2,3,4,5.
I have seen another user create a lookup table of First Name because they thought it would be more efficient to store a numeric foreign key. The lookup had about 2k records. This required the user to first update the lookup table and then selecting a choice from the lookup table. There was something like 6k data records so only a small percentage of names were repeated.
So the point in your case if you saved lookup values it would be easier and there would absolutely be no noticeable size or efficiency issues. However all the other flexibility issues would be sacrificed for the ease of use.

 

[MajP]

So I opened the DB and you are storing the Lookup values and not the Primary key for your 3 lookup tables. Personally I would leave it, as long as you review the above thread and understand potential pros and cons. So if you leave it the discussion of referential integrity is not relevant since there is no relation to the lookups. There is one place you are not consistent. In the Round table you are storing a course name. Courses are different than these other single field "lookup tables". I course has several fields of information which makes it more a "data table". Courses have additional information. In the shot table you store the CourseID as foreign key which is correct, but you are storing the name in the Round table where you should store a courseID_FK.

Also look at my examples for Main form and Subform. I doubt any is going to want to enter 18 holes for a golf course (hole data) in single form view, with no idea of what has been entered and which course you are referring to. You would have a main form with the ability to select a Course at the top and a continuous form for the holes.

 

[golfortennis1]

So I opened the DB and you are storing the Lookup values and not the Primary key for your 3 lookup tables. Personally I would leave it, as long as you review the above thread and understand potential pros and cons. So if you leave it the discussion of referential integrity is not relevant since there is no relation to the lookups. There is one place you are not consistent. In the Round table you are storing a course name. Courses are different than these other single field "lookup tables". I course has several fields of information which makes it more a "data table". Courses have additional information. In the shot table you store the CourseID as foreign key which is correct, but you are storing the name in the Round table where you should store a courseID_FK.

Also look at my examples for Main form and Subform. I doubt any is going to want to enter 18 holes for a golf course (hole data) in single form view, with no idea of what has been entered and which course you are referring to. You would have a main form with the ability to select a Course at the top and a continuous form for the holes.
View attachment 107031

Thank you. I actually have gone back and updated the other tables to relate the autonumber primary key. Now that I have the shot data form as the primary input, and I can make the club/lie/ etc., visible for the choice, but feeding the primary key to the tblshotdata, I should have not only referential integrity but also an easier time running reports. I noticed that about the course name and have removed them from the tblroundlog.

This form description you gave is actually a very good idea. You're right that someone will not want to input one at a time. Once I get my frmshotdata input down, this will be my next thing to work on.

While there may have been some frustration, I appreciate how you all have made me go back and look and find the tweaks. Just getting the primary key to input onto the tblshotdata forced me to find a few things just now.

With the updates my relationships now look like this. (*I will amend the shotvalue field names immediately. Thought I got them all.)

 

[golfortennis1]

Made a few adjustments.

 

[MajP]

I should have not only referential integrity but also an easier time running reports.

1. I doubt it will have any impact on running reports. For sure I see no way that would make it easier
2. If you are truly running a single field lookup table, I am not sure what concern there is on referential integrity. There is really no issue or benefit.

Again, you cannot go wrong storing an FK for these lookups, but those reasons you list are not the pros of doing so. Saving the look up FK is more involved and complicated than simply saving the lookup value (requires a join to show or query data, requires comboboxes that hide a PK). So reporting should be harder. There are advantages but not what you list.

You need to figure out how you want to handle ShotValues. There are several possibilities (composite key, autonumber, natural key, no key) and is probably debatable on how to do it.
You could save a another foreign key in tblShotData from tblShot_Values. But I do not recommend that, because you already store two seperate fields lie_FK and DistanceToPin which determine the BaselineStroke. I would change Lie in tblShot_Values to the Lie_FK (numeric key) since you are now storing the lie_FK in the data table. I would get rid of typeandDistance since it is not used and can always be concatenated dynamically. Now you simply join the two tables by both Lie_FK and DistanceToPin to get the BaselineStroke. Ensure you uniquely index the composite index of Lie_FK and DistanceToPin in tblShot_Values.

(Change Lie to Lie_FK, and delete TypeAndDistance)

One thing I should have mentioned is that there is no reason you have to use an autonumber or a numeric for your PK. It is always fine to use a numeric key, but not always worth doing so.

I personally would not do what you have.
tblLieOptions

ID	Lie
1​	T
2​	F
3​	R
4​	RE
5​	S
6​	G

I would simply do this.
tblLieOptions tblLieOptions

Lie_ID
F
G
R
RE
S
T

I would make my primary key a natural key. Since this natural key has all the qualities of a good PK, it is clean, and unique, and never changing. This then gives you the best of both worlds. You have a FK that is easy to load, can add additional related fields later if needed, You can display data without a join or can join to show other fields. The argument that a numeric Key is more efficient, is trivial. Maybe after a million records will it be concerning.

 

[golfortennis1]

On the shot values discussion, the problem would be that you have F10, for example, but then there is G10, R10. So if the distance is a field on its own, 10 would show up multiple times. And you need the different values, because 10 yards from the fairway you would be expected to get down in fewer shots than 10 yards away in the rough, and the G is in feet. That's kind of why I'm starting with the two combined for that table.

Unless I am missing something you mentioned in the post, in which case I apologize for being dense and will ask you to clarify.

 

[MajP]

I think you missed the point. In the data table you are now storing a DistanceToPin and a Lie_FK. Your shot value table is then wrong you cannot join on that.

tblShotValues tblShotValues

ShotValueID	TypeandDistance	BaselineStrokes
302​	T401​	3.99​

You need to update it to look like this

tblShot_Values tblShot_Values

Lie_FK	DistanceToPin	BaselineStrokes
1	401​	3.99​

Now a T shot at 401 is stored in the data table as
1 401
and that will join to the tblShotValues by joining on two fields, lie_FK and DistanceToPin.

 

[golfortennis1]

I think you missed the point. In the data table you are now storing a DistanceToPin and a Lie_FK. Your shot value table is then wrong you cannot join on that.

tblShotValues tblShotValues

ShotValueID	TypeandDistance	BaselineStrokes
302​	T401​	3.99​

You need to update it to look like this

tblShot_Values tblShot_Values

Lie_FK	DistanceToPin	BaselineStrokes
1	401​	3.99​

Now a T shot at 401 is stored in the data table as
1 401
and that will join to the tblShotValues by joining on two fields, lie_FK and DistanceToPin.

So the fact the same number would show up multiple times in the distancetopin column wouldn't be an issue?

 

[MajP]

No. You will join on two fields not just the distance field. Look at my demo where I already do this.

 

[golfortennis1]

No. You will join on two fields not just the distance field. Look at my demo where I already do this.

Ok, I think I see it now. I was getting stuck on the same number appearing multiple times without a differing characteristic in the same field. Trying this out now.