Results 1 to 4 of 4
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Relationship errors

    More learning errors I think
    (I walked away from this thing for a couple of months and now I'm so lost)


    I have relationships set up 2 different ways and I cant figure out why, or what is right.



    In the image below I have tbl_houses and tbl_housetype
    and I have linked the "housetypeID" to "housetypeID"


    I also have tbl_transactions and tbl_TransactionType
    but in this scenario I have linked "TransTypeID" to "transType"

    can someone help me understand the difference here and what is the proper way??

    Thank youClick image for larger version. 

Name:	Capture2.PNG 
Views:	17 
Size:	16.8 KB 
ID:	43391

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It would be helpful to you and readers if you would provide a graphic of all your tables and relationships.

    My first thought is that "housetypeID" to "housetypeID" are both numeric data types.
    But "TransTypeID" to "transType" are numeric and text respectively. You can't have relationships between different data types. But your issue may be more basic if you do not have your relationships established between all tables in your database. That is, you may have a structure that you have not tested.
    For more info see the Database Planning and Design link in my signature --specifically the tutorials from RogersAccessLibrary.

  3. #3
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Sorry I was just trying to show a simplified version of the issue, wasn't thinking the full view would confuse things (now realizing that was poor reasoning)
    Click image for larger version. 

Name:	Capture3.PNG 
Views:	16 
Size:	48.1 KB 
ID:	43392

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is my $0.02 worth....


    I don't know, but I would guess "housetypeID" to "housetypeID" are both numeric data types because you were able to set RI for the relationship.
    Same for "TransTypeID" to "transType". RI has been set so they must be the same data type.
    Tables tbl_HouseType and tbl_TransType are Look up TABLES (good | Lookup FIELDS - BAD); this aids in consistency during data entry; selection is usually a combo box/list box on a form.


    can someone help me understand the difference here and what is the proper way??
    Not sure what you mean by "the proper way", but what you have displayed is how I use tables to be able to "pick" (select) values instead of having to always type in values.

    The only thing I would question your naming convention because it isn't the same as mine.

    Your field names are
    Look up Table
    Field name Data Table Name Field Name
    tbl_HouseType HouseTypeID tbl_Houses HouseTypeID
    tbl_TransType TransTypeID tbl_Transactions transType


    My field names would be
    Look up Table
    Field name Data Table Name Field Name
    tbl_HouseType HouseTypeID_PK tbl_Houses HouseTypeID_FK
    tbl_TransType TransTypeID_PK tbl_Transactions transTypeID_FK


    You didn't use special characters, punctuation or spaces in names.....very good.
    I just like to specify which fields are PK/FK; makes it easier (for me) when creating queries or Main/Child links to know which fields are PK and which are FK.


    Nothing jumps out as being "wrong" as far as your table designs/relationships, but I don't know your dB process/purpose.

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

Similar Threads

  1. Replies: 3
    Last Post: 03-19-2015, 05:26 PM
  2. #Num! and #Div! errors
    By frustratedwithaccess in forum Queries
    Replies: 3
    Last Post: 01-29-2015, 08:16 PM
  3. Errors
    By Thompyt in forum Programming
    Replies: 3
    Last Post: 10-31-2014, 01:29 PM
  4. Replies: 4
    Last Post: 06-21-2014, 05:43 AM
  5. Replies: 10
    Last Post: 07-25-2011, 12:07 PM

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