Tables vs Queries for Forms

[Guest]

Tables VS Queries again.
Organization - Subspecialties - Customers - Orders - Orders Details - All
one to many relationships as they go down. Need to see all the orders for the
Subspecialties within the Organization and need to see the Orders for an
individual customer within the Subspecialty. Usually one order per customer
within the Subspecialty as these are unique each year, but sometimes they do
add on before the orders are completed in which case it would appear there is
more than one order.
What would you sugges is the best way to set up a form?

Thanks

 

[Guest]

Without a doubt, a main form with subforms for the other tables. If you try
to link the various tables together into one query, chances are excellent
that you'll have an unupdateable query so people won't be able to use the
form to add or update data.

Also you could run into inner join problems not returning all records if a
table in the query did not have a matching record. Then you will need to mess
with Left or Right joins which would also make the query unupdateable.

Before setting up the new form make sure that the relationships between the
tables are set up in the Relationships window and referiential integrity is
enabled. You might have to clean up some 'orphaned' records, but in the long
run, you'll be glad that you did.