Fields and records moving about? (1 Viewer)

[Pat Hartman]

I also use the form/report wizards to build the basic controls. Usually I create a query first and use the wizard on the query. That lets me get the columns in the order I want them to appear on the form which might not be the same order as what is in the table and it also allows me to include ancillary data from other tables. I always lock the controls of the ancillary controls to prevent accidental update. Then I change some of the controls to combos as needed and move everything around. As for formatting, I try to stick with the default for the theme. If you are inclined to change themes, be aware that the point sizes for the various fonts are inconsistent so if your form looks great with theme1, switching to theme2 might make the letters too big to display in your labels so you would have to resize ALL the controls.

 

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

 

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