Can I create a form in Access that allows me to enter the following? 1

[SubjectiveEffect]

I’m trying to create a form in Access that links to a database with the following construction:

Customer details table:
Cust number {Primary key)
Name
Address
Etc

Order junction table:
Order number
Cust number

Order table:
Order number (Primary key)
Order item

Payment method junction table:
Method number
Cust number

Payment method table:
Method number (Primary key)
Payment method

The junction tables are because of the many-many relationships.

The form should allow me to enter all the customer details plus multiple payment methods and multiple orders per customer. To this end I want a form that has eight fields for entering payment methods and orders. When I enter, for example, a new customer plus 2 payment methods and 5 orders I want it all stored at once.

Is this possible? Seems it should be but somehow when I create the form and copy the order item and payment method fields the form turns out blank – so can’t even test if this simple method will work. Am I doing something wrong?

 

[JoeAtWork]

You could create a form for the customer data, a subform for the order (header) and payments tables, and a sub-subform for order details on the order header table. The hierarchy being:

Customer
Order Header
Order Details
Payments

Note my use of Order Header / Order Detail tables, which is the standard design, as opposed to your "Order junction" table.

The relation between Customer and Order is not really many-many, it is one-to-many, i.e.

Customer (one) - Order Header (many)

The table structures would generally be:

Customer
CustomerID
CustomerName
etc.

Order Header
OrderID
CustomerID (foreign key to Customer.CustomerID)
OrderDate
etc.

Order Details
OrderDetailID
OrderID (foreign key to [Order Header].OrderID)
ProductID
Quantity
UnitPrice
etc.