A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old May 10th, 2008, 12:49 AM posted to microsoft.public.access.forms
Jeff Rozar
external usenet poster
 
Posts: 2
Default 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  
Old May 10th, 2008, 02:01 AM posted to microsoft.public.access.forms
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.