If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can't get subforms to work with one-to-many tables
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! |
#2
|
|||
|
|||
Can't get subforms to work with one-to-many tables
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Jeff Rozar" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|