Trying to create a form in Access that fills in mulitple entries

[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 table
Order number (Primary key)
Cust number (Foreign key)
Order item

Payment method table
Method number (Primary key)
Cust number (Foreign key)
Payment method

These are 1 to many relationships.

The form should allow me to enter all the customer details plus multiple payment methods and multiple orders per patient. To this end I want a form that had eight fields for entering payment methods and orders. When I enter, for example, a new patient 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?

 

[fneily]

First post Access questions in one of the six Access forums. That's where we experts live.
Your table explanation isn't quite clear. So, it seems to me you have three main tables.
CustomerTbl with fields CustNum, Name, City, State, etc.
PaymentTbl with fields MethodNum, Method
OrderTbl with fields OrderNum, OrderItem

These tables can be prefilled so you would have a entry form for each table.

Then you would create a TransactionTbl. It would look like:
CustNum, MethodNum, OrderNum
These three fields TOGETHER would create the primary key for the TransactionTbl.

It would be in this TransactionTbl you would store the connection between the tables.

You DO NOT have one to many relationships. You have MANY TO MANY relationships so the TransactionTbl is a JUNCTION table.

Suggestions: First take Access courses. You can not learn Access on your own.
Study NORMALIZATION. VERY, VERY, VERY IMPORTANT. You need to know this to create tables.
Search the Access forums on references to creating RELATIONSHIPS between tables.
You might also want to read:
Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

Further question should be posted in the Access forums.

 

[SubjectiveEffect]

Right you are - it IS many to many!

Thanks. It's been a while since I designed a database. This will end up being normalised to 3rd or B-C normal form.