Table design and table link question

[larpup]

I have a table (TABLE1) with a compound key (two fields). A CustomerID
field and an Order Number field. (Parent Table) The Order Number Field
is provided by the Customer. Customers do have same order numbers.

I have another table (TABLE2) that need be linked to the first table.
(Child Table)

The way I would do this in my old database is;
1. Have a form based on TABLE 1 with fields from TABLE 2 also on the
form.
2. After both key fields (from TABLE 1) are entered into the form, I
would have a hidden field concatenating both fields of the compound
key. This field would be linked to an indexed field in TABLE 2. (The
reason is that order numbers can be duplicated in this database. By
concatenating Order Number with the CustomerId field, it creates
uniqueness.)

What is the best way to accomplish this in Access without having a
concatenated field in TABLE 1 (which I need to link to TABLE 2) and how
would I link them?

Lar

 

[Guest]

In Good Access Design, which I belive is your main Question

Fields (Customer) need to be Customer related, such as Name, Address, phone
#, (Customer ID.(KEY field)
Fields (Order) likewise need to be Order related such as Quanity, Value,
(Order ID)Primary Key), (Customer ID.(foreign key)
Keys are (Primary) or (Foreign)
If the Orders Key can have Duplicates by Data Entry then No Integrity Exists
in the orders field.
It is far greater to Keep your Tables simple with related information for
that specific field, use quality form design for input, Query's to mix up
the fields for the information required by your reports.

I have no knowledge base of Childs Table or Master table. My understanding
is the Childs field and Master field is related to Form Design not to Table
design.

When you create a subform using the form wizard, Access automaticly sets the
properties for the Link Child Fields and the link Master Fields to the fields
that the relationship is defined on.
Note: Their is a subform "Field Linker" dialog box / Click the Build Button
to access it.
WLM