Fields and records moving about? (1 Viewer)

[Minty]

You have, unfortunately, used lookups in your tables and they hide muddy the waters when it comes to stored and displayed values.

Remove those and I suspect things will become clearer. Remember now your data is organised properly, the forms you need to create will tie up those look-ups and present the data in an easy-to-understand format from a user perspective.

 

[golfortennis1]

You have, unfortunately, used lookups in your tables and they hide muddy the waters when it comes to stored and displayed values.

Remove those and I suspect things will become clearer. Remember now your data is organised properly, the forms you need to create will tie up those look-ups and present the data in an easy-to-understand format from a user perspective.

Are you referring to how I have tblHoleListing looking to CourseList? I have to admit, it's likely my way of learning, so I am stuck on what I know? Could I ask you to be a bit more specific? I'm not saying you're wrong, I'm just not seeing what you are saying.

 

[MajP]

I got rid of the Lookups. You will use the same idea, jut do it on the forms and not in the tables. See the form below. I built the combos on the form instead.

You have one big problems in your data. You have made a mismatch on most of the records.
I ran a query to fix it. Hopefully I did it correctly.
You picked holes from the wrong course. In most records you will have for example a round for Augusta. The shots are related to the round ID, but the holes are the corresponding holes at another course say Pinehurst. So I made a translation query. If the round was Augusta then replace the the ID for the hole so that hole 1 at Pinehurst would be replaced with the ID for hole 1 at Augusta. This kind of made my head spin, but I think it is correct.

You do not need ShotDate in the shot table because all shots are related to a round and a round has a date. You could display that in the subform if you wanted by doing a join to the round table.
There is a slight difference from storing a key to simply storing a value. If you are simply storing a value from a lookup table then do not call it an FK unless you are storing the corresponding FK. In Club_FK you are simply storing the actual name of the Club. You could store the PK value as an FK. You just want to make sure you are not eventually going to want extra fields. For example you think you just have to store ClubType.
You then are just using a value and no need to relate back to the table.
Then you decide for reports you need a long name
5w Five Wood
then you decide that you want a category
5i Five Iron Iron
sw Sand Wedge Wedge
Now you have related information and those lookups and now real foreign keys. That means you have to store a PK which can be an autonumber or you can simply make your current ClubType (5w) the PK.

Do not use calculated fields. Do your calculations in a query when you need them. Get rid of the Shot in the table.

Here is my demo form. It has some bells and whistles to demonstrate concepts. Some of this is just way easier to show than explain. I would have separate forms for adding courses, rounds, clubs, etc.

1. The combo allows you to pick a Course. The CourseID field is hidden.
2. The Round subform is linked by CourseID_FK to the Course combobox. I do not allow you to add a round here, but probably could.
3. I use conditional formatting in the Round subform to show the current round
4. There is code in this subform in the current event to set the value of txtLink in red. (This can be made invisible, but shown for demo purpose)
5. The Shot subform is linked to txtLink by RoundID _FK to txtlink
6. There is code in the Hole combo to select only those holes for that course based on the course in the cmboCourse.
7. I calculate shot using a calculated control. This can also be done in a query. Better than a calculated field.
8. I added some code to ensure all fields are filled in. You can do this using the required property, but then you will get the default cryptic error message.
As mentioned prior if you are ever going to play two rounds on the same course in the same day, you will need to add some field to differentiate.

I like to break things up, instead of having one form to do everything. Some people try to do to much in a single form such as adding editing courses, searching. I often separate adding, editing, and navigating. So From here I might have a button next to cmboCourses that brings up a form to add a course.
V1 fixes the validation of the data entry.

 

[golfortennis1]

Thank you. I will go through this and see what came about. Not sure whether to be proud or dismayed I made your head spin....

ETA: My apologies. I should have adjusted it first. I had started on a new layout trying to get the courses lined up better. Something went haywire and I forgot about it until you mentioned it.

 

[golfortennis1]

I got rid of the Lookups. You will use the same idea, jut do it on the forms and not in the tables. See the form below. I built the combos on the form instead.

You have one big problems in your data. You have made a mismatch on most of the records.
I ran a query to fix it. Hopefully I did it correctly.
You picked holes from the wrong course. In most records you will have for example a round for Augusta. The shots are related to the round ID, but the holes are the corresponding holes at another course say Pinehurst. So I made a translation query. If the round was Augusta then replace the the ID for the hole so that hole 1 at Pinehurst would be replaced with the ID for hole 1 at Augusta. This kind of made my head spin, but I think it is correct.

So what happened there was that without the course name, it was showing round number and then the hole # of that particular round. My thought process was somehow looking at the hole ID should bring in the proper course. i.e, HOle #24 is Augusta National #6(and in the form that is what you would see but access would be thinking #24. I think the translate might be an unnecessary item(although I couldn't enunciate why at the moment), but given what you knew about the table, I can understand why you would do that. Hopefully I can reverse engineer this when looking at my own. (My goal is to learn how to do this myself, not have had you build it for me.)

You do not need ShotDate in the shot table because all shots are related to a round and a round has a date. You could display that in the subform if you wanted by doing a join to the round table.

Agreed.

There is a slight difference from storing a key to simply storing a value. If you are simply storing a value from a lookup table then do not call it an FK unless you are storing the corresponding FK. In Club_FK you are simply storing the actual name of the Club. You could store the PK value as an FK. You just want to make sure you are not eventually going to want extra fields. For example you think you just have to store ClubType.
You then are just using a value and no need to relate back to the table.
Then you decide for reports you need a long name
5w Five Wood
then you decide that you want a category
5i Five Iron Iron
sw Sand Wedge Wedge
Now you have related information and those lookups and now real foreign keys. That means you have to store a PK which can be an autonumber or you can simply make your current ClubType (5w) the PK.

Ok. I had originally done Club Type as the PK, but given my head spins with all this new information(which I have asked for), I added autonumbers everywhere. Can't see wanting another field in Club Type, but I do understand on the surface where you are coming from. Bad habits now =bad database later.

Do not use calculated fields. Do your calculations in a query when you need them. Get rid of the Shot in the table.

I struggled with that one because of the fact you need the previous shot's information to get the desired information. While it is a calculated field in the technical sense, it's almost not because on it's own it doesn't really mean much. I'd almost liken it to having a database of customers where you might have first name and last name as separate fields, and combine them into a Full Name, or vice versa. It just gives you the key to look up the expected value, which when subtracted from the previous shot's information(on the same hole), then gives you the gold.

Here is my demo form. It has some bells and whistles to demonstrate concepts. Some of this is just way easier to show than explain. I would have separate forms for adding courses, rounds, clubs, etc.
View attachment 105892

1. The combo allows you to pick a Course. The CourseID field is hidden.
2. The Round subform is linked by CourseID_FK to the Course combobox. I do not allow you to add a round here, but probably could.
3. I use conditional formatting in the Round subform to show the current round
4. There is code in this subform in the current event to set the value of txtLink in red. (This can be made invisible, but shown for demo purpose)
5. The Shot subform is linked to txtLink by RoundID _FK to txtlink
6. There is code in the Hole combo to select only those holes for that course based on the course in the cmboCourse.
7. I calculate shot using a calculated control. This can also be done in a query. Better than a calculated field.
8. I added some code to ensure all fields are filled in. You can do this using the required property, but then you will get the default cryptic error message.
As mentioned prior if you are ever going to play two rounds on the same course in the same day, you will need to add some field to differentiate.

I like to break things up, instead of having one form to do everything. Some people try to do to much in a single form such as adding editing courses, searching. I often separate adding, editing, and navigating. So From here I might have a button next to cmboCourses that brings up a form to add a course.
V1 fixes the validation of the data entry.

You are thinking what I was thinking, I was still 6 steps behind you. I will see what is in here.

To save typing, please assume there is a "thank you" every second sentence.

 

[golfortennis1]

So I've been messing about, and think I may have a path, but I am getting stuck at one spot. I'm building a form for inputting shot data. I want to have Cascading Combo Boxes for Course, then hole. But can cascading combo boxes draw from separate tables?

I would want the CourseName from tblCourseListing, and HoleNumber from tblHoleListing. I'm aware of some VBA needs, and looking at the resources to wrap my head around it, but I figure I should answer this question before proceeding. Thanks.

 

[Minty]

The short answer is yes - the row source for any combo can be from any table you like.
You just need to link it back to your data by the correct path, or in your case use its value to determine the list of holes.

 

[MajP]

Cascading Combo Boxes for Course, then hole. But can cascading combo boxes draw from separate tables?

Not just "yes", but that is the norm.

In your case this could be a little tricky in that only the Hole data should be bound to your shot table. You get the course through the hole table and in fact the Round table. A hole is assigned a Course and a Round is assigned a course too.
This makes doing it in a traditional subform a little tricky, but also not the ideal user experience IMO. If you do it directly in a subform then the user is selecting the course, then hole for every shot. You want to do it once IMO. Assign a course to a round. Now the subform should be every shot for that round. No need to pick the course each time.
If you want to cascade directly in a continuous subform then the Course Combo is unbounded. This will look very strange because when you change the unbound combo all instance of that combo will appear to change. Remember though it is not bound.

 

[golfortennis1]

Not just "yes", but that is the norm.

In your case this could be a little tricky in that only the Hole data should be bound to your shot table. You get the course through the hole table and in fact the Round table. A hole is assigned a Course and a Round is assigned a course too.
This makes doing it in a traditional subform a little tricky, but also not the ideal user experience IMO. If you do it directly in a subform then the user is selecting the course, then hole for every shot. You want to do it once IMO. Assign a course to a round. Now the subform should be every shot for that round. No need to pick the course each time.
If you want to cascade directly in a continuous subform then the Course Combo is unbounded. This will look very strange because when you change the unbound combo all instance of that combo will appear to change. Remember though it is not bound.

Now I looked over your file you posted, which was quite nicely done. I haven't posted anything back because I've been just trying to get some learning done, but my overall thought on the components on this, and you can tell me if I'm wrong, was that a)the tables would be where the data was stored, b)the form(s) would be for input, c)queries and reports would be for summarizing/presentation. Ie., the form you built, which probably seems old hat to you but the functionality looks amazing to me, the bells and whistles you added(which I think you alluded to them being more for show) looked to me like the stuff I would want in a report.

As for this particular post, I think I get where you are going with that. I will play around with that portion of what I am trying to do.

 

[MajP]

I'm wrong, was that a)the tables would be where the data was stored, b)the form(s) would be for input, c)queries and reports would be for summarizing/presentation

Exactly! This is the big difference from working with something like Excel to a database. In a spreadsheet the data storage and display of the data are the same thing. In a database the data is stored in "pieces" (tables) and can be put together and displayed in infinite different ways. I am just demonstrating some concepts for data entry and display. You do not have to use any of these ideas. Some of these are more advanced to make all these subforms synch. Some of these "tricks" can make this data entry far easier and more intuitive. Your database has a lot of related data making a good user friendly design is tougher than most. A "traditional" single Main form with Subform, probably not going to be too user friendly. There are many ways you could do this. However I have to say this seems very intuitive and user friendly and I would probably do something like this.
But this highlights why a good table design is critical. If the framing is good then you can do anything. To demonstrate I changed (added) to the interface, and maybe even more intuitive. I added a Hole subform.

1. Select a Course from the combo
2. It shows all rounds for that course
3. It shows all holes for that course
4. Click on a hole and the subform allows you to enter shot date for that hole on that round
5. The tab lets you go back to the other view where you can pick a whole manully.
Same data different presentation.

 

[golfortennis1]

Exactly! This is the big difference from working with something like Excel to a database. In a spreadsheet the data storage and display of the data are the same thing. In a database the data is stored in "pieces" (tables) and can be put together and displayed in infinite different ways. I am just demonstrating some concepts for data entry and display. You do not have to use any of these ideas. Some of these are more advanced to make all these subforms synch. Some of these "tricks" can make this data entry far easier and more intuitive. Your database has a lot of related data making a good user friendly design is tougher than most. A "traditional" single Main form with Subform, probably not going to be too user friendly. There are many ways you could do this. However I have to say this seems very intuitive and user friendly and I would probably do something like this.
But this highlights why a good table design is critical. If the framing is good then you can do anything. To demonstrate I changed (added) to the interface, and maybe even more intuitive. I added a Hole subform.

1. Select a Course from the combo
2. It shows all rounds for that course
3. It shows all holes for that course
4. Click on a hole and the subform allows you to enter shot date for that hole on that round
5. The tab lets you go back to the other view where you can pick a whole manully.
Same data different presentation.
View attachment 105986

So what you displayed in the screenshot is almost what I would say a report or query would look like, at least to my thinking. I would almost be thinking about the form being relatively plain and utilitarian, but maybe there is something to this. I have to say as much as I am wanting to learn this, you are making it very tough not to just use what you post! Essentially what you did is what I am wanting to learn how to do.

I should note I went back and corrected the hole ID. My original data source was fairly easy to convert, so I brought it back in this format.

 

[MajP]

You do have a lot of Groupings
Course
Hole

Course
Round
Hole
Shot

Reports are very powerful in displaying Groupings. Forms rely on subforms. This form is somewhat complicated because of all the groups. So in that way yes. The queries can do a lot of grouping and summarizing. So in a query you can sum the shots per hole, and subtract from par to get you score per hole. Or you can sum all the holes and all the shots to get overall score.

 

[golfortennis1]

You do have a lot of Groupings
Course
Hole

Course
Round
Hole
Shot

Reports are very powerful in displaying Groupings. Forms rely on subforms. This form is somewhat complicated because of all the groups. So in that way yes. The queries can do a lot of grouping and summarizing. So in a query you can sum the shots per hole, and subtract from par to get you score per hole. Or you can sum all the holes and all the shots to get overall score.

It's tough because for this to work, you need to be able to tie that stuff to each other. I've tried to minimize as much as possible. More importantly, try to make sure the base is set up right.

 

[MajP]

making it very tough not to just use what you post! Essentially what you did is what I am wanting to learn how to do.

I post a lot of code. I hope that people will try to learn, but use them as examples. However, there are certain things that would take forever to explain.
My point about lots of groups is just the nature of this specific database, not a good or bad thing. Some database are very linear and some are hierarchical. Because of the hierarchy this one is a little complex to make an easy user interface.

 

[MajP]

One thing. I think the tables and fields are correct and well named except one. In the round table you have a field called DATE. This is a reserved field because it is an access Function and it is a SQL datatype. It will function but may cause problems and extra work. Rename to RoundDate.

 

[golfortennis1]

One thing. I think the tables and fields are correct and well named except one. In the round table you have a field called DATE. This is a reserved field because it is an access Function and it is a SQL datatype. It will function but may cause problems and extra work. Rename to RoundDate.

Thank you. I thought I had cleaned all of that up. Good catch. Done.

 

[MajP]

I saw one more issue. In the Round table you are storing the Course Name. You should be storing the Course id. I would add CourseID_FK. Then either add them in manually or you can do an update query.
You would link Course table to Round table by Course Name. Then update the CourseID_FK. After that you can delete the Course Name.
You can try it.
Link the two tables my name after creating the CourseID_FK (number field). From the ribbon select Update. It should change the display. Under the new CourseID_FK field in the Update to line put [CourseID]. Then run it. It will update the new FK to the PK where the names match.

 

[MajP]

I sucked your tables into my demo.
If fixed those field names. Your names are a little inconsistent still. In the future if the field is a primary key it ends in ID. CourseID, RoundID, HoleID. The related field in the table should show it is a foreign key. I like CourseID_FK. But if you have a field in a table that you are supposed to link and it is named Hole, Course etc it gets real confusing.

As far as I can tell your hole data is still completely jacked up. You have the wrong hole ID for the the holes. I did the conversion again. You have the id for the corresponding hole but on the wrong course.

The big thing I did was in the Shot Value table I broke it up into Lie and Distance. Now you do not have to do the calculated field and can do a direct join. This is something I rarely ever do, but made these two field into a composite Key. That means the two values Lie and DistanceToPin are a unique combination. This is also how you link the table.

 

[MajP]

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.

 

[Pat Hartman]

I don't object to natural keys but I don't allow multi-field PKs. If it takes multiple natural attributes to make the PK, you are better off with a unique index plus an autonumber as the PK. To work properly, list and combo boxes need a SINGLE unique identifier is the reason for the restriction.