Fields and records moving about?

[golfortennis1]

Greetings all. Newby to this forum. A search did not turn up quite what I was looking for. I have done a few courses, and now all of a sudden I think of myself as as Access expert till I start to actually use Access.

Not this may be part of a bigger issue, but I figured I best start small. I had previously been using an Excel spreadsheet to do what I am attempting, but after learning about Access, it appears to be the better way.

What I am doing is tracking golf statistics. "Strokes Gained" to be more precise. My fields include the following: Date, Course, Round #, Hole, Stroke(which shot on the hole), Club, Type, Lie, Distance to Pin, Shot(which is a concatenation of the prior two to create a lookup value.)

The way this is supposed to work is before hitting a shot, there is an expected number of strokes to get the ball in the hole, so the previous stroke's information is necessary to get the correct data. I choose Round, Hole, Stroke all together, and sort lowest to highest(ie, the 1st shot of the 1st hole of the 1st round should be first). But every time I do something and save in design view, the fields are in random spots and the table appears to be sorted by club. I do not see anything that suggests why club would be the default.

I currently do not have a primary key on this table, as I do not want to have Access simply number each shot(I have >2000 now and growing). I tried to create a key with concatenating, but Access doesn't allow this, and I can't use the existing fields as there are duplicates everywhere else(e.g. round 1 has 75+ records, every course has 18 holes, every hole has a first stroke).

If there are some suggestions, I would be very appreciative. Feels like this should be simple to get past, but I'm stuck.

Thank you.

 

[Galaxiom]

There is no order to records in a table. You need to apply an order on a field in a query or on a form or report to control the order.

 

[plog]

Can't Excel do this easier? Why are you using Access?

Date, Course, Round #, Hole, Stroke(which shot on the hole), Club, Type, Lie, Distance to Pin

The first 3 fields belong in 1 table (tblRounds) and the rest belong in another (tblStrokes). Unless you do that you've just got a spreadsheet in Access.

As Galaxiom said, when you want to ensure an order, you used a tool that guarantees that. I suggest you create a query based on your table and then explicitly define the Sort order of the data in the query.

Again though, either use a spreadsheet tool for a spreadsheets. Or use a database tool for a database. You're using a database tool with a spreadsheet.

 

[golfortennis1]

Can't Excel do this easier? Why are you using Access?



The first 3 fields belong in 1 table (tblRounds) and the rest belong in another (tblStrokes). Unless you do that you've just got a spreadsheet in Access.

As Galaxiom said, when you want to ensure an order, you used a tool that guarantees that. I suggest you create a query based on your table and then explicitly define the Sort order of the data in the query.

Again though, either use a spreadsheet tool for a spreadsheets. Or use a database tool for a database. You're using a database tool with a spreadsheet.

Excel can do it. And for just this info, probably easier. From what I saw from Access though, when I want to start working with the info, Access is a great tool, with reports, I thought the use of form might make it nice for input, and queries would be better than doing a ton of sumifs and the like in Excel.

I have a Round Log table that has the score, date, and course. Type, Clubs, Lie, all are lookups on their own tables, and hole is a set of numbers I input. I guess my question is how do I make sure that records 1-75 which are from round 1, tie back to round 1 somehow? Or is that the part I am missing or is impossible in this situation? I was looking into component keys, but that seems to be discouraged everywhere I have looked.

I have deleted so many fields, forms and queries trying to maintain as much normalization as I could, trying new and old things.

 

[golfortennis1]

There is no order to records in a table. You need to apply an order on a field in a query or on a form or report to control the order.

OK so run a query to maintain that. Ok, I will try to play with that.

 

[plog]

Instead of writing sentences about your tables/fields, show us your tables/fields. Complete the Relationship Tool in Access with all your tables, expand all of them so we can see all the fields, then post a screenshot of it back here and we can work through your tables/fields.

Also give us 2 paragraphs--1. Describe in simple terms, without database jargon what it is this database is for. I know its about golf, but is it a tournament, a league, etc.. Expand a little. 2. Tell us what you hope this database will help you accomplish. Minimal database jargon allowed, but focus on what in the real world this database will assist with.

 

[MajP]

You can post your database (tables only) and we can help with ensuring the structure is correct and recommend some queries.

 

[golfortennis1]

Here is a screenshot of my relationships. tblRoundLog and tblShotData are the only ones that would see any form of regular additions to their tables. @MajP I am not sure how to post what you are asking unless you simply mean post the entire file?

This file is meant to compile all my shots over the course of time to track my game in the different areas(type would be that section), while also allowing to compile ancillary data that I have to believe is there to be extracted(birdies, how close do I get to the pin from the sand, etc), and if possible I could look at a specific round and see a summary of the round.

 

[plog]

That's not normalized, here's the biggies:

1. Duplicated data. In a relational database you only store data in one spot, then if you need data together with other data, you relate it via queries. Every field in tblRoundlog is also in tblShotData, that is incorrect. This is where autonumber primary keys come into play. You add one to tblRoundlog and it becomes the one value you can use to uniquely identifies every round. You then take out every field from tblRoundLog that is in tblShotData and put in a field to hold that autonumber primary key value from tblRoundLog. Then when you need round and shot data together you make a query and JOIN them via that vield.

2. Circular paths of relationships. There should only be one way to travel between 2 tables in a relationship, you have created a loop with your tblShotData, tblType_Options and tblShot_Values tables.You can travel clockwise or counterclockwise in that relationship which is wrong, it should terminate not be a loop. tblShotData and tblType_Options should not be directly related. Take out the Type field from tblShotData and only keep Strokes Baseline. With it you can get to Type via a query and then get the Type when necessary.

3. Storing calculated values. Score should not be a field in your database because you can use math on other data to calculate it. Simply count the number of records in tblShotData for a round and you get the round score--you don't store it.

Those are the biggies and here's a few other things:

4. Tables with only 1 field should not exist. You are already storing the text value of club/lie option/type in tblShotData, you don't need them in a seperate table.

5. Non alphanumeric characters in names. Non alphanumeric characters in names ( spaces and #) just make coding and querying harder. Instead of [Distance to Pin] use either DistanceToPin or Distance_to_Pin.

 

[MajP]

I had to type on phone so disregard any duplication with @plog

You can post a whole database as an attachments, but we prefer you strip it
to bare bones to only what is relevant. To do this you can create a new
blank database. You can then import what you want into that database using
the External data tab. In this case ideally just your tables.

Couple of things. Naming can make your life much better.
1. Normally Primary key fields end in ID. Such as CourseID, ClubID,
RoundID.
2. Does not matter if you think a table does not need a PK, add one. Make
it autonumber. ShotDataID
3. The foreign key that you link to should be obvious. I personally simply
add FK
CourseID_FK, ClubID_FK
4. NO SPACES OR SPECIAL CHARACTERS. !@#$%^&*()_+ etc. You have an
especially bad one #. Round_No or RoundNumber
5. No reserved words. Things that can confuse the compiler. You have a lot
TYPE, DATE
use something more descriptive
ShotDate, ShotType

It is just a whole lot easier to link tables by number and autonumber always
works.
In table clubs I assume for the PK you are using the actual name instead of
an autonumber. That may be fine but it is more efficient to join by number
instead of PK of "5 Iron" to FK "5 Iron".
It will work in this case, but I would not focus on meaningful PK and I 90%
of the time just add an autonumber. The user should never see this or know
this or even type these FK. 95% of the time the use just pulls the "5 Iron"
choice from a combobox which saves a hidden number relating to "5 Iron"

I personally think you would want a course table. That way you can store
info about the course to and link by CourseID. May want this in future.
Maybe you want to query shot only on Stadium/Championship, or links courses.
tblCourseID
courseID 'Autonumber
courseName
CourseNotes
courseAddress
CourseType
NumberHoles
etc
...

Same for Hole
tblHole
HoleID ' autonumber
HoleNumber
CourseID_FK ' relates to the course
Par
HasWaterObstacle
other hole information

I would definitely assume if you are doing shot analysis you many want to
know how you do (par wise) on different par holes

In tblRound_Log
I personally do not use the term number to mean id. I assume in this design
your RoundNumber is RoundID and it is an autonumber. Minor point but adds
clarity. When I see number i expect something like PlayerJerseyNumber,
AircraftTailNumber, PhoneNumber. A value that has meaning.

If you do build a TblHole then you can get rid of course from tblShotData.
The course can be pulled from the hole in a query. Likely on a form you
would have Cascading Combos. You pick a course, then that filters the
second to those holes. Then you pick a hole.

 

[golfortennis1]

Thank you both. Upon reading, I do have a course list table but it's not linked yet as I was still trying to get the rest figured out, but based on what you have said, I likely have a circular relationship about to form there as well.

It will take me a few days likely to digest and get this all straight but I will report back with an update. Thank you again.

 

[Pat Hartman]

4. Tables with only 1 field should not exist. You are already storing the text value of club/lie option/type in tblShotData, you don't need them in a seperate table.

Rather than using a separate table for each lookup table, I use a more sophisticated method. However, defining a table to store a list of valid values is far superior to having to embed value lists in tables, unless @plog thinks that lists should not be validated. So, for now, I would keep the tables.

However, I suggest adding some discipline to your naming standards.
1. Object names should consist of ONLY the letters a-z, A-Z, numbers 0-9, and the Underscore. The underscore is the only non-letter/number that is valid for use in a name. Just because Access lets you create object names with special characters and embedded spaces doesn't make it right. It is an accomodation for people who don't know any better. VBA is not so tolerant.
2. For your own sanity standardize how you name primary keys. I assume you are using text values. I don't because it interferes with my actual method of storing lookup lists but it is not technically wrong. I agree with Maj, even if you are using separate tables for each list, I would use an autonumber as the PK. So, if you use ClubID as the PK, the linking field in the shot table would be ClubID or if you prefer, ClubID_FK. The two fields would NEVER be selected in the same query so there actually isn't any conflict with the "duplicate" name. Having the same name just reduces brain strain and helps you to easily pick out foreign keys and primary keys. If you use ClubID as the PK, then you also need a field named ClubDesc so you have a text version to display for human consumption. You might want a type field so you can mark a club as driver, iron, putter, wedge, etc

 

[golfortennis1]

That's not normalized, here's the biggies:

1. Duplicated data. In a relational database you only store data in one spot, then if you need data together with other data, you relate it via queries. Every field in tblRoundlog is also in tblShotData, that is incorrect. This is where autonumber primary keys come into play. You add one to tblRoundlog and it becomes the one value you can use to uniquely identifies every round. You then take out every field from tblRoundLog that is in tblShotData and put in a field to hold that autonumber primary key value from tblRoundLog. Then when you need round and shot data together you make a query and JOIN them via that vield.

2. Circular paths of relationships. There should only be one way to travel between 2 tables in a relationship, you have created a loop with your tblShotData, tblType_Options and tblShot_Values tables.You can travel clockwise or counterclockwise in that relationship which is wrong, it should terminate not be a loop. tblShotData and tblType_Options should not be directly related. Take out the Type field from tblShotData and only keep Strokes Baseline. With it you can get to Type via a query and then get the Type when necessary.

3. Storing calculated values. Score should not be a field in your database because you can use math on other data to calculate it. Simply count the number of records in tblShotData for a round and you get the round score--you don't store it.

Those are the biggies and here's a few other things:

4. Tables with only 1 field should not exist. You are already storing the text value of club/lie option/type in tblShotData, you don't need them in a seperate table.

5. Non alphanumeric characters in names. Non alphanumeric characters in names ( spaces and #) just make coding and querying harder. Instead of [Distance to Pin] use either DistanceToPin or Distance_to_Pin.

OK. Replies/thought process.

1. Autonumber. I will incorporate.

2. So here is the thought process behind that. When I enter a shot, what I need to input are the hole I was on, the stroke of the hole(1st, 2nd, etc--although that could be calculated), the club, the type(tee shot, approach-for categorization later), the lie(this is to allow the correct number to use for calculation of SG), and the distance to the pin. The club, type and lie tables are there so that only the correct choices can be chosen(ie., 52W instead of 52* for a wedge). Maybe I simply do like hole and put in my own values and eliminate the tables? But I do need type in the same place as shot data because there is nothing that would allow me to query them together. Shot concatenates Lie and Distance into a "code" that can then look up against shot values for the number needed to calculate what I want.

3. Yes, good plan.

4. Ties back to #2.

5. Worked on. Thanks.

 

[golfortennis1]

I had to type on phone so disregard any duplication with @plog

You can post a whole database as an attachments, but we prefer you strip it
to bare bones to only what is relevant. To do this you can create a new
blank database. You can then import what you want into that database using
the External data tab. In this case ideally just your tables.

Couple of things. Naming can make your life much better.
1. Normally Primary key fields end in ID. Such as CourseID, ClubID,
RoundID.
2. Does not matter if you think a table does not need a PK, add one. Make
it autonumber. ShotDataID
3. The foreign key that you link to should be obvious. I personally simply
add FK
CourseID_FK, ClubID_FK
4. NO SPACES OR SPECIAL CHARACTERS. !@#$%^&*()_+ etc. You have an
especially bad one #. Round_No or RoundNumber
5. No reserved words. Things that can confuse the compiler. You have a lot
TYPE, DATE
use something more descriptive
ShotDate, ShotType

It is just a whole lot easier to link tables by number and autonumber always
works.
In table clubs I assume for the PK you are using the actual name instead of
an autonumber. That may be fine but it is more efficient to join by number
instead of PK of "5 Iron" to FK "5 Iron".
It will work in this case, but I would not focus on meaningful PK and I 90%
of the time just add an autonumber. The user should never see this or know
this or even type these FK. 95% of the time the use just pulls the "5 Iron"
choice from a combobox which saves a hidden number relating to "5 Iron"

I personally think you would want a course table. That way you can store
info about the course to and link by CourseID. May want this in future.
Maybe you want to query shot only on Stadium/Championship, or links courses.
tblCourseID
courseID 'Autonumber
courseName
CourseNotes
courseAddress
CourseType
NumberHoles
etc
...

Same for Hole
tblHole
HoleID ' autonumber
HoleNumber
CourseID_FK ' relates to the course
Par
HasWaterObstacle
other hole information

I would definitely assume if you are doing shot analysis you many want to
know how you do (par wise) on different par holes

In tblRound_Log
I personally do not use the term number to mean id. I assume in this design
your RoundNumber is RoundID and it is an autonumber. Minor point but adds
clarity. When I see number i expect something like PlayerJerseyNumber,
AircraftTailNumber, PhoneNumber. A value that has meaning.

If you do build a TblHole then you can get rid of course from tblShotData.
The course can be pulled from the hole in a query. Likely on a form you
would have Cascading Combos. You pick a course, then that filters the
second to those holes. Then you pick a hole.

Tons of good stuff here, and I really like your idea about splitting course and hole. Fantastic. Getting to work on some of this now. Thank you.

Question about id in tblShotData. Ideally I would want to be able to incorporate the roundID, the hole and the stroke on the hole. I know there is a risk of simply doing 111 for the first shot of the first hole of the first round. But could I do something like 1:1:1? Or does that cause problems?

Also, reserved words.... is CourseandTee for a field name a problem, or is it only when AND is isolated?

 

[MajP]

No problem. Reserved words are words used for database objects, and SQL functions

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

Lots are obvious: You can have a FIELD in a TABLE that has a datatype of DATETIME, BOOLEAN, DATE, CHAR, CHARACTER, CURRENCY....
In a query you can JOIN, SELECT, FROM, GROUP BY, ORDER, AND, OR AVG, sort ASC, DESC....
You can use reference objects like CURRENTDB, APPLICATION,...

A
    ADD
    ALL
    Alphanumeric
    ALTER
    AND
    ANY
    Application
    AS
    ASC
    Assistant
    AUTOINCREMENT
    Avg
-B
    BETWEEN
    BINARY
    BIT
    BOOLEAN
    BY
    BYTE
-C
    CHAR, CHARACTER
    COLUMN
    CompactDatabase
    CONSTRAINT
    Container
    Count
    COUNTER
    CREATE
    CreateDatabase
    CreateField
    CreateGroup
    CreateIndex
    CreateObject
    CreateProperty
    CreateRelation
    CreateTableDef
    CreateUser
    CreateWorkspace
    CURRENCY
    CurrentUser
-D
    DATABASE
    DATE
    DATETIME
    DELETE
    DESC
    Description
    DISALLOW
    DISTINCT
    DISTINCTROW
    Document
    DOUBLE
    DROP
-E
    Echo
    Else
    End
    Eqv
    Error
    EXISTS
    Exit
-F
    FALSE
    Field, Fields
    FillCache
    FLOAT, FLOAT4, FLOAT8
    FOREIGN
    Form, Forms
    FROM
    Full
    FUNCTION
-G
    GENERAL
    GetObject
    GetOption
    GotoPage
    GROUP
    GROUP BY
    GUID
-H
    HAVING
-I
    Idle
    IEEEDOUBLE, IEEESINGLE
    If
    IGNORE
    Imp
    IN
    INDEX
    Index, Indexes
    INNER
    INSERT
    InsertText
    INT, INTEGER, INTEGER1, INTEGER2, INTEGER4
    INTO
    IS
-J
    JOIN
-K
    KEY
-L
    LastModified
    LEFT
    Level
    Like
    LOGICAL, LOGICAL1
    LONG, LONGBINARY, LONGTEXT
-M
    Macro
    Match
    Max, Min, Mod
    MEMO
    Module
    MONEY
    Move
-N
    NAME
    NewPassword
    NO
    Not
    Note
    NULL
    NUMBER, NUMERIC
-O
    Object
    OLEOBJECT
    OFF
    ON
    OpenRecordset
    OPTION
    OR
    ORDER
    Orientation
    Outer
    OWNERACCESS
-P
    Parameter
    PARAMETERS
    Partial
    PERCENT
    PIVOT
    PRIMARY
    PROCEDURE
    Property
-Q
    Queries
    Query
    Quit
-R
    REAL
    Recalc
    Recordset
    REFERENCES
    Refresh
    RefreshLink
    RegisterDatabase
    Relation
    Repaint
    RepairDatabase
    Report
    Reports
    Requery
    RIGHT
-S
    SCREEN
    SECTION
    SELECT
    SET
    SetFocus
    SetOption
    SHORT
    SINGLE
    SMALLINT
    SOME
    SQL
    StDev, StDevP
    STRING
    Sum
-T
    TABLE
    TableDef, TableDefs
    TableID
    TEXT
    TIME, TIMESTAMP
    TOP
    TRANSFORM
    TRUE
    Type
-U
    UNION
    UNIQUE
    UPDATE
    USER
-V
    VALUE
    VALUES
    Var, VarP
    VARBINARY, VARCHAR
    VERSION
-W
    WHERE
    WITH
    Workspace
-X
    Xor
-Y
    Year
    YES
    YESNO

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

 

[MajP]

Question about id in tblShotData. Ideally I would want to be able to incorporate the roundID, the hole and the stroke on the hole. I know there is a risk of simply doing 111 for the first shot of the first hole of the first round. But could I do something like 1:1:1? Or does that cause problems?

NO!! Do not do this. IDs should not carry meaning. Make it an autonumber and be done with it. You will never need to choose manually or display them.

Simply make CourseID, RoundID, ShotID, HoleID all autonumbers.

HoleID 987 could represent 18th at Augusta. On any form you will select (normally with combos) Augusta, then Hole 18 and automatically store 987.

Imagine this
1.) Enter all your course information into the database. Name, location, whatevery you want to have. You will never see a course id
2) Open the Course Hole form. The main form has the course Name and can display course information. The subform allows you to enter information about all the holes (number, par, length, number of water obstacles, whatever is unique to a hole). It Automatically saves the CourseID_FK. You will never see a HoleID
3) Open the Round Information for. Main Form is the same as Number 2, but this subform allows you to enter rounds. It automatically stores the CourseID_FK.
You may need another field that identifies which round for that given day if you play more than once. This could be done by requiring a date and time for your date. The time representing the starttime of the course. Or you can add a Field RoundNumberForDay (1,2). The default value is one for most days.
4) In form ShotData. The main form is based on a query that shows Course, Round information so you can select a specific round. The subform links to the main form by, round.ID_FK. In the subform you have combos to select the Hole. This will be a little tricky, but you will make the combo only populate holes for that course but it will store the specific hole id

Remember if you use an autonumber (and strongly recommend you should. Especially avoid composite keys, not worth the work). Your table will have every hole for every course. So the HoleID and the HoleNumber (for display) cannot be the same. Maybe just maybe, the first course has hole ids ranging from 1 to 18. The next will have IDs 19 to 36. They will display 1 to 18 though.
In my shotdata form my combo may show Hole 2 but will store ID 20. I can use a query to show that this is the second hole on Course2.

 

[MajP]

FYI. Once you get your tables done you can post the database. We can help with the form. Easier for me to do a working demo then type what I just did.

 

[golfortennis1]

FYI. Once you get your tables done you can post the database. We can help with the form. Easier for me to do a working demo then type what I just did.

I only have tables so far. So just post the full file?

 

[golfortennis1]

Here are the updated relationships. I still need to tie Club and lie. Will also do the PK, FK thing afterwards. Thought I removed # from the field names, and I have removed score from tblRound_Log

My big question is that in the shot data(and maybe I won't care about this with a form), for the Hole my lookup gives me courseID then hole, but the course is numbered, not named in the view. In Round log however, I can choose the course names.

This is my course listing table. And Hole listing:

The shot data output.

Thinking i might have to do with how tblcourselist is feeding tblholelisting.

 

[golfortennis1]

So essentially I have what I posted above. My sticking point seems to be the hole information on tblShotData. When I try to create a form(for further adding), I can only choose numbers. tblHoleListing displays the course name, but tblShotData(which is looking at tbl hole listing) only gives its number. For the table itself, that can be fine, but on the form it would be much easier to choose course by name, but I can't seem to get that to happen. All I can choose is 1-108(6 courses at 18 holes each). I may be missing something, but I think that is my last item before I can start getting into forms and queries and reports.