Correct method to build tables (Access)

Topic

New Reply

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

Reply | Quote

Replies

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 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.

Reply | Quote

Thank you for your reply!

Reply | Quote