Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Correct method to build tables (Access)

    Hi Lounge, As I am slowly learning how to make a database a few things I need to clear up with tables since its the most important part of how the database works.

    I see tables as having a few different roles
    Firstly, a table should only have data related to the table name.
    Using Primarykeys and Foreign or composite keys a multiple number of tables can be connected, thus sharing the information.

    If I make a table for the sole purpose of being a list for a combo box which is bound to a feild in a specific table, there is no reason for this table to have a relationship so a list table would need a ListID (autonumber) and ListEntry?
    If a another table were to use the list from the above how would it get these values and not effect the list?

    In relation to User Forms and a Table
    How can a table have a field with a subfield property which will allow more then 1 value entered for a field? for example.

    1 order number can have a schedule with a multiple number of zones.

    Order number field
    Zone field
    - Zone 1 Bob
    - Zone 2 Craig
    - Zone 3 Ben




    Thanks for helping me

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Correct method to build tables (Access)

    If you want to use a table as row source for a list box or combo box, it is generally important to relate this table to the table that contains the field to which the list box or combo box.
    It's best to use an AutoNumber primary index in the "list" table, linked to a Number (Long Integer) field in the "main" table". Enforcing referential integrity for the link between the table ensures that you cannot create orphan entries in the "main" table, i.e. values that do not occur in the list.
    You would include the AutoNumber field in the list box/combo box by setting its column width to 0; the second column, with non-zero width, would display the relevant list entry.

    You shouldn't store multiple values in a field. The situation you describe for orders and zones is a so-called many-to-many relationship: one order can have several zones, and one zone can presumably feature in several orders. A many-to-many relationship is implemented in the form of an intermediary table that has a field linked to the orders table and a field linked to the zones table; the combination of these two fields should be unique: there will be a separate record for each order+zone combination.

    See <post#=364,203>post 364,203</post#> for an example of how to handle data entry for a many-to-many relationship with a main form and subform.

    Microsoft has a short but useful document Understanding Relational Database Design.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Correct method to build tables (Access)

    Thank you for your reply!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •