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

    I think i get it but....

    I know people have told me when i first came here not to build lookups into my tables and i never really got it till just now because I didn't understand the theory enough at the time to grasp it.



    So I am going to try to break this down to construction paper and crayons then look forward to the responses.

    What is the Proper way to "Index" a Record with Categories



    tblCustomerAddressType
    CustomerAddressTypeID
    CustomerAddressType

    and

    tblCustomerAddress
    CustomerAddressID
    CustomerAddressTypeIDfk
    AddressLine1
    AddressLine2
    ect
    ect


    AND NOW I JUST GRASPED

    I Could Have:

    tblCustomerAddress
    CustomerAddressID
    CustomerAddressType <---Store Types "Branch Location" "Billing" here to be used
    CustomerIDfk
    AddressLine1
    AddressLine2
    ect
    ect



    If I am understanding when people say not to build lookups into table it's because that is a functionality specific to Access and it will not apply to Data methodology as a whole.

    Seems to me the 2 tables method will keep your records smaller and more code, not to mention flexible. Where as Sheet KeyIndex will result in Office and Home and Billing and Vendor Branch Location populating the hell out of your Address List and thats only one Table, at an enterprise level.......thats a lot of
    Home
    Home
    Home
    Home
    Home
    Home
    Home
    Home
    Home
    Office
    Branch
    Home




    I get it thanks everyone
    Last edited by DBID10T; 10-25-2019 at 06:14 PM. Reason: Eureka!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    CustomerAddressType <---Store Types "Branch Location" "Billing" here to be used
    There is nothing wrong with having an address type table and storing the type PK in the customer addresses table (e.g. "2" rather than "Billing"). You can also use this type table as the row source for a form combo and store the PK in customer addresses, or the string value if you prefer. The advantage to the former is that IF you change the string value for the type, the fact that the value in customer addresses is the type PK (e.g. 2) then changes to the value are of no consequence because you are using the record ID rather than the string.

    The advice you were given regarding table lookups was about not creating lookup values at the table level. While you will see "Billing" in the table field, the real stored value is the number that Access has assigned to it - same as if you had done it yourself as part of good normalization practice. The difference isn't only that you can't upsize a db with such tables, it can make what should be simple queries or recordset handling require workarounds.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    DBID10T is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    46
    Quote Originally Posted by Micron View Post
    There is nothing wrong with having an address type table and storing the type PK in the customer addresses table (e.g. "2" rather than "Billing"). You can also use this type table as the row source for a form combo and store the PK in customer addresses, or the string value if you prefer. The advantage to the former is that IF you change the string value for the type, the fact that the value in customer addresses is the type PK (e.g. 2) then changes to the value are of no consequence because you are using the record ID rather than the string.

    The advice you were given regarding table lookups was about not creating lookup values at the table level. While you will see "Billing" in the table field, the real stored value is the number that Access has assigned to it - same as if you had done it yourself as part of good normalization practice. The difference isn't only that you can't upsize a db with such tables, it can make what should be simple queries or recordset handling require workarounds.

    Awesome Good to Know
    Thanks.

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

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