Results 1 to 7 of 7
  1. #1
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46

    The PK and FK field naming convention....

    When I first Came here I was directed to many different Tutorials about Key index structure and using a _PK and _FK after my field names really helped my learn the one to many relationship and see it during my table mock up process. Now that I am working with the queries I find the _FK and _PK all over kind of distracting.



    Will I be shooting myself in the foot if I drop all those and just use ProjectID and ProjectID in related tables?

    Thanks,

    DBID10T

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Possibly. It can be confusing to see exactly same name multiple times in a query. Will mandate prefixing reference in query with source table/query name. Many prefer to have some distinction between names. You are free to do whatever makes sense to you. Just be consistent.

    If you haven't heard already - do not use spaces nor punctuation/special characters (underscore is only exception) in names nor reserved words as names. I once encountered an issue with a field name that included a reserved word separated by space or underscore (can't remember which). Even enclosing in [] didn't help. Had to rename field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Quote Originally Posted by June7 View Post
    Possibly. It can be confusing to see exactly same name multiple times in a query. Will mandate prefixing reference in query with source table/query name. Many prefer to have some distinction between names. You are free to do whatever makes sense to you. Just be consistent.

    If you haven't heard already - do not use spaces nor punctuation/special characters (underscore is only exception) in names nor reserved words as names. I once encountered an issue with a field name that included a reserved word separated by space or underscore (can't remember which). Even enclosing in [] didn't help. Had to rename field.
    something like: qry_CustomerID_LastName_tblCustomer
    ?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    No, I mean like:

    SELECT Submit.LabNum, Submit.DateEnter, [0SoilsAggGradation].LabNum, [0SoilsAggGradation].Method
    FROM 0SoilsAggGradation INNER JOIN Submit ON [0SoilsAggGradation].LabNum = Submit.LabNum;

    Submit and 0SoilsAggGradation are tables. Note they each have field named LabNum. Table name must prefix field names to distinguish them. The other fields would not require prefix but Access query builder uses anyway. Builder added the brackets as well.




    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Gotcha, Think I'll give it a go thanks

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can't always depend on Access to add prefix and brackets for you, especially when building expressions to do calcs with fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    I think it depends more on your preferences. I myself prefer to have for PK and it's FK's same names.
    When I write any query, I usually use only one of them (e.g. when the query uses outer join, then only ID of left part is displayed) - I always can use ...IDField as IDAlias... when another ID must be displayed.
    Another reason I use same names for PF and it's FK's is, that I seldom define DB-level relationships between tables. And having same names for key fields enables automatic linking whenever a subform is added into form (and I use subforms a lot).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Naming convention question? btn or cmd?
    By RLehrbass in forum Access
    Replies: 7
    Last Post: 09-21-2015, 07:51 PM
  2. Access & sage folder name convention
    By FJM in forum Access
    Replies: 3
    Last Post: 08-06-2015, 08:19 AM
  3. I use my own VBA naming "convention"
    By keviny04 in forum Modules
    Replies: 13
    Last Post: 06-05-2015, 03:09 AM
  4. Naming a Calculated Field
    By wscott in forum Reports
    Replies: 4
    Last Post: 10-11-2010, 02:20 AM
  5. Replies: 0
    Last Post: 07-13-2010, 07:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums