Help needed with database design

  • Thread starter Thread starter macca
  • Start date Start date
M

macca

Here is a database structure I am working with - but I am having trouble
coming up with a good interface for the processing of new orders.


Clients Orders OrderDetails Items

CustNo (1) OrderNo (1) (M) OrderNo (1) ItemCode

FName (M) CustNo ItemCode (M) NameDesc

SName Date NumItems Desc

Address Value

etc



I have a good Main form interface based on Clients i.e. with a subform
comprising Orders which then has a subform showing Orderdetails. This means
I can scroll from Customer to Customer - and see the orders and order
details. If I want to I can add, edit Customers - I can quickly "see" how to
add the order dertails. I assume this is because the Main form is based on
Clients - and the subforms work out because of a 1 to Many relationship
between Clients and Orders - and then another 1 to Many relationship between
Orders and OrderDetails i.e. there is a sort of nesting of subforms.



What I want to know is - can I have a data entry form which is essentially
based on the Orders table i.e. the Main form is based on Orders. But I want
it to allow me to scroll through the Orders and if I want to add a new
record - I want to "see" the Customer details rather than just have to know
their IDNo. Every attempt I have made has failed to see this connection.

I assume this is because when I start with the Orders form it is actually a
Many to 1 relationship when going back to Clients.


Any advice?
 
But I want
it to allow me to scroll through the Orders and if I want to add a new
record - I want to "see" the Customer details rather than just have to know
their IDNo.
Click to expand...

One way (there are others) to do this is to put a Combo Box on the
Orders form, bound to the Customer's IDNo field. The Combo Box would
be based on the Customers table, and would contain up to ten fields
from that table - they can be set to zero width if you don't want to
see them in the dropped-down combo.

On the Form you can put textboxes with control sources

=cboCustomer.Column(n)

where n is the *zero based* subscript of that field in the combo's
rowsource query.
 
The Northwind sample database included with Access has
examples for tracking orders.
-----Original Message-----
Here is a database structure I am working with - but I am having trouble
coming up with a good interface for the processing of new orders.


Clients Orders OrderDetails Items

CustNo (1) OrderNo (1) (M) OrderNo (1) ItemCode

FName (M) CustNo ItemCode (M) NameDesc

SName Date
Click to expand...
NumItems Desc
 
Back
Top