Can't get subforms to work with one-to-many tables

[Jeff Rozar]

Simple question, just a few tablles. The database is normalized, 2NF I
believe, but I can't get more than one relationship to show on a form.


Table1:
CustomerNumber
Name
Phone

Table2:
CustomerNumber
OrderNumber

Table3:
OrderNumber
SKU
Date

Table4:
SKU
Desc
Price

Table1 to Table2 is one-to-many
Table2 to Table3 is one-to-many
Table3 to Table4 is one-to-many
Table2 links tables 1 and 3 together

I'm trying to have one form that shows:
Table1
Table2
Table 3
Table4

So that when I change the customer number, all the orders for the customer
appear, and all the skus for the selected order appear.

I can get just one one-to-many form/subform to work, but not more.

Any help is appreciated!

 

[Douglas J. Steele]

I'm not sure I understand the point of Table2. Are you saying that an order
can be shared among multiple customers? I'd think that an order would only
be for a single customer (so that you'd put CustomerNumber as a foreign key
in the Order table). However, you need another table to resolve the
many-to-many between Order and Product (one order is placed for multiple
products, one product can be part of multiple orders)

Take a look at what's in the Northwinds sample database that's installed
with Access. Northwinds has tables Customers, Orders, Order Details and
Products. Take a look at forms Orders and Orders Subform to see how they
portray all the information using just two forms.