Fields and records moving about? (1 Viewer)

[golfortennis1]

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

Yeah, the math gets real interesting as you go further down, but as you say Baby steps.

How do I rename Expr1? Either I'm an idiot(wait, don't tell my wife), or Access isn't letting me do it.

 

[Minty]

In the Query window it says

Exrp1: [Lie] & [DistancetoPin]

Change it to
ShotCalc1: [Lie] & [DistancetoPin]

The part before the : is known as an alias in DB speak. In the SQL statement it looks like this

[Lie] & [DistancetoPin] AS ShotCalc1

 

[golfortennis1]

In the Query window it says

Exrp1: [Lie] & [DistancetoPin]

Change it to
ShotCalc1: [Lie] & [DistancetoPin]

The part before the : is known as an alias in DB speak. In the SQL statement it looks like this

[Lie] & [DistancetoPin] AS ShotCalc1

Thank you. Now, please, really don't tell my wife!!

 

[NauticalGent]

CowPasturePool, I just stumbled across the thread and I have to say that you have the right attitude and gumption to go far with Access. Converted Excel users are the hardest to re-train and tend to have a death-grip on that concept.

You have demonstrated a willingness to learn and ability to take direction without attitude - which is why the best of AWF have stuck with you to help see this to fruition. You will go far with Access and I wish you the best of luck in your endeavors...

 

[golfortennis1]

CowPasturePool, I just stumbled across the thread and I have to say that you have the right attitude and gumption to go far with Access. Converted Excel users are the hardest to re-train and tend to have a death-grip on that concept.

You have demonstrated a willingness to learn and ability to take direction without attitude - which is why the best of AWF have stuck with you to help see this to fruition. You will go far with Access and I wish you the best of luck in your endeavors...

I will assume you are referring to me and not Cowpaturepool.... I thank you for the kind words. It would seem like it should be obvious, but these folks don't *need* to help, they are choosing to. Attitude would be showing disrespect to that fact.

I can't say I have been such a positive away from this forum while working on this stuff, but it's certainly never been directed towards these folks here. Learning the difference from Excel is a huge step, but helpful. Ironically, I can think back to a couple of items in my old job that were clunky excel spreadsheets, and I can already see how they would have been better handled using Access. But alas, those days are gone.

 

[NauticalGent]

I will assume you are referring to me and not Cowpaturepool....

Down south, we peasants refer to Golf as "Cow-Pasture Pool" (snooker for the Brits on this site), so I took liberties with your username!

 

[mike60smart]

I will assume you are referring to me and not Cowpaturepool.... I thank you for the kind words. It would seem like it should be obvious, but these folks don't *need* to help, they are choosing to. Attitude would be showing disrespect to that fact.

I can't say I have been such a positive away from this forum while working on this stuff, but it's certainly never been directed towards these folks here. Learning the difference from Excel is a huge step, but helpful. Ironically, I can think back to a couple of items in my old job that were clunky excel spreadsheets, and I can already see how they would have been better handled using Access. But alas, those days are gone.

Hi

I just took a look at your latest uploaded database and there are many things you need to address before proceeding any further.

In the attached file I have listed the various errors.

 

[golfortennis1]

Down south, we peasants refer to Golf as "Cow-Pasture Pool" (snooker for the Brits on this site), so I took liberties with your username!

Nice...lol. Never heard that one.... historical roots Sheep Pasture would fit real nice!!

 

[golfortennis1]

Hi

I just took a look at your latest uploaded database and there are many things you need to address before proceeding any further.

In the attached file I have listed the various errors.

Ok, I thank you for taking this time. I can put my primary keys on Club, Lie and Type in their respective tables rather than the ID. BUt my IDs in some of the others are autonumbers, so I'm not sure where how to fix the errors. I've looked up referential integrity to see what is needed, but I'm not certain exactly what I would need to do.

If you are referring to the combo boxes in the tblshotdata as lookups needing to be removed, I probably can now that the form is set. If it's something else can you please specify? I'll fully admit to not knowing what I don't know.

Appreciate any feedback on this.

 

[golfortennis1]

Ok, I thank you for taking this time. I can put my primary keys on Club, Lie and Type in their respective tables rather than the ID. BUt my IDs in some of the others are autonumbers, so I'm not sure where how to fix the errors. I've looked up referential integrity to see what is needed, but I'm not certain exactly what I would need to do.

If you are referring to the combo boxes in the tblshotdata as lookups needing to be removed, I probably can now that the form is set. If it's something else can you please specify? I'll fully admit to not knowing what I don't know.

Appreciate any feedback on this.

Update: I have brought in referential integrity to every one of them. Changed the autonumbers into long integer number and it seems to have handled it.

 

[Minty]

You should really be storing the ClubID, TypeID, etc in the main shot data table, not the text descriptions from the lookup tables.
You are defeating the idea of having a unique Id field by doing that.

 

[golfortennis1]

You should really be storing the ClubID, TypeID, etc in the main shot data table, not the text descriptions from the lookup tables.
You are defeating the idea of having a unique Id field by doing that.

So I have to admit that question went through my mind. Why have the unique ID? The advice given earlier in the thread was to have the ID, but if referential integrity is important(which in some cases, for this particular project I'm not so certain it is), this would need to be the solution.

The Type and Lie won't change, but in case this gets shared out, I would want to be able to "control" it by only allowing certain inputs. Clubs is a little more malleable, which is why I would think the clubs especially should have their own table so that it can be updated there.

I have to say I got quite confused after that post...

 

[Minty]

Personally, I always use an ID for any lookup and that's what I store in the target data table.

There are a number of benefits, but the main one for me is data consistency, numbers are much easier to deal with than strings, and in a large system take up less space. Joins on them are generally more efficient than strings joins.

Even if it's not an autonumber, a numeric ID primary key gives you the ability to filter groups of things by using a number range, regardless of the text value.

 

[golfortennis1]

Personally, I always use an ID for any lookup and that's what I store in the target data table.

There are a number of benefits, but the main one for me is data consistency, numbers are much easier to deal with than strings, and in a large system take up less space. Joins on them are generally more efficient than strings joins.

Even if it's not an autonumber, a numeric ID primary key gives you the ability to filter groups of things by using a number range, regardless of the text value.

The only string join I can see happening is the one discussed earlier. The rest are for classifications that won't combine.

So, trying to think this through, would you say I should, similar to what I have with course name on my frmShotData, I should do the same with some of the others(ie., table stores 1 for club, but inputter on form sees D)?

 

[Minty]

So, trying to think this through, would you say I should, similar to what I have with course name on my frmShotData, I should do the same with some of the others(ie., table stores 1 for club, but inputter on form sees D)?

Yes - it makes for slightly more work on the forms but ensures consistency.

 

[ebs17]

You should really be storing the ClubID, TypeID, etc in the main shot data table, not the text descriptions from the lookup tables.

This reference has two heavyweight reasons:

1) Data integrity: The name of a club might be changed (new owner, merger, different policy). The idea of the lookup table is that a change should only happen in exactly one place. In the other tables it is only referred to with a key, which itself does not have to be changed. This way you always have the current and correct club name in all queries.

2) Performance: Keys are used en masse for comparative operations (JOIN, WHERE, GROUP BY, ORDER BY). In a JOIN, each key of one table must be compared to each key of the other table for equality. This is a very large number of comparison operations, even for a single query.

Simplified shown:
If Long is used for the two keys, 4 bytes are compared against 4 bytes in each case. Using text for the keys results in a significantly larger byte width for comparison: 2 bytes per character (Unicode) + 10 bytes for managing the whole. With an average text length of 10 characters, you are there with 30 bytes. Comparing 30 bytes against 30 bytes instead of 4 against 4 - that's a huge difference, and it adds up over the many operations. Work takes time.

 

[golfortennis1]

This reference has two heavyweight reasons:

1) Data integrity: The name of a club might be changed (new owner, merger, different policy). The idea of the lookup table is that a change should only happen in exactly one place. In the other tables it is only referred to with a key, which itself does not have to be changed. This way you always have the current and correct club name in all queries.

2) Performance: Keys are used en masse for comparative operations (JOIN, WHERE, GROUP BY, ORDER BY). In a JOIN, each key of one table must be compared to each key of the other table for equality. This is a very large number of comparison operations, even for a single query.

Simplified shown:
If Long is used for the two keys, 4 bytes are compared against 4 bytes in each case. Using text for the keys results in a significantly larger byte width for comparison: 2 bytes per character (Unicode) + 10 bytes for managing the whole. With an average text length of 10 characters, you are there with 30 bytes. Comparing 30 bytes against 30 bytes instead of 4 against 4 - that's a huge difference, and it adds up over the many operations. Work takes time.

So the Club in question is the item used(Driver, 3wood, pitching wedge, etc.) Other than adding a different club to the list(put in a 5 wood for one round), I can't see that list ever getting larger than about 20 or so. By rule you can't change clubs within a round, a person can't make a change between Drivers mid round. So if they played one round with a Callaway driver, and the next with a Titleist driver, they could make notes on a round.

But, if this is going to be a decent database, maybe they should be able to instantly choose a certain driver. Although that has to be weighed against time. Ideally if you're inputting a round, it should take between 5-10 minutes. This will have 70-100 inputs done basically at once on a regular basis. If a person has too many choices in some of the categories, it gets too cumbersome to use. But I do appreciate the need to think this through even further. I can update my forms to do as I did with the course.

My question would be then, how do I make easy user friendly way to lookup the corresponding shot value to a specific lie and distance? A person can't be expected to search for the autonumber among the 2000+ shotvalues records? Unless this is the Excel in me speaking, the joining of T and 401 to find the corresponding value to T401 seems to be the easiest way. Or maybe the question should be how do I restate the shot values table?

 

[Minty]

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.

 

[ebs17]

For an input one will use a form. There a ComboBox can offer a selection (content of the lookup table or further query). This selection will be limited by known parameters to the point that a probable selection can be set as default, just to make it user-friendly.

 

[moke123]

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.

As an aside, I highly recommend getting an Oculus 2 and the VR game "Golf+" They have a deal with the PGA and have several real courses as well as a few based on real courses. They have Wolfs Creek, Kiawah Ocean course, Pebble Beach, Pinehurst #2, Valhalla, TPC Scottsdale, TPC Sawgrass, and more on the way. Watching golf on tv you find yourself saying "I've played that hole"

The multi-player games are a blast and you play with people from all over the world. They also have a "Beat the Pro" event in cooperation with the PGA. Last week, during The Players Championship, I hit a hole in one on the Island green (17) at Sawgrass and beat the pro.

GOLF+ on Meta Quest | Quest VR Games | Meta Store

The ultimate VR Golf experience and the official VR Golf game of the PGA TOUR! ⛳️ Iconic Courses: 3 Free courses and 25+ real-world destinations available for purchase including Pebble Beach. 🏌️ Multiplayer: Play with friends, or join public rounds. 📈 Performance Feedback: Stats, strokes gained...

www.oculus.com