Order Entry Form Advice

[Scoop]

I've developed a fairly complex table structure for an Order Processing
application. In addition to processing orders it will be a primary
store of customer information, including multiple addresses
(one-to-many), marketing campaign data, etc. I'm now at the point of
developing the forms/user interface...something I don't really have a
lot of experience doing.

I'm hoping someone can give me some basic advice to get me started on
the main Order Entry form. My main concern is whether the form should
be bound to the "Orders" table or a query. If bound to the table, I
think I'll need to setup subforms for the billing/shipping addresses,
in addition to the subform that I'll have to setup for "OrderDetails"
table where order line items are stored. If it is bound to a query, I
guess I'll need to include all the information for both addresses but
provide another form that is launched so the users can select from the
multiple addresses that a customer may have stored.

I also have a number of other one-to-many relationships to account for
also...Orders to Invoices...Orders to Financial Transactions, etc.

I'm sure plenty of people have been through this before, any
ideas/advice?

Thanks!

 

[Pieter Wijnen]

As most engineers I tend to hate transactional databases (all that
"redundant" data)
however, I would make for 1 table for the customers with a self-join for any
subsidaries (ie different mailing address, same billing address)
to clarify - make one entry for the main account (billing address) & one for
each mailing addresses with a link to the main (billing) account
If this is not acceptable (it should!), make a combobox to select the
current mailing address according to the selected billing adress (using an
adress table)

HTH

Pieter

 

[Scoop]

Thanks Pieter...can give me an example of how to construct the
self-join?