User interface - add a new customer

[Guest]

What is the best way to have a user update a Customer table if there are
related tables (i.e. Parent table, Division table and a Class table)?

1 Parent (GMC) may have multiple customers (Saab, GM, etc)
1 Parent (GMC) may have 1 or more Divisions (Saab, Cad).
1 Class may have many customers and many Parents.
Parent-Class relationship is 1-1.

In Customer table, I have customer_no, customer_name,parent_no,industry_class.
In Division table, I have div_no, divname, parent_no.
In Class table, I have classno,classname.
In Parent table, I have parentno, parentname.

I have a form for the Customer table, however, how do I get the user to
update the Division table since that also has parent_no? Also, how do I get
the user to update the Class and Parent tables? Do you usually have several
tables to select from in order to update? Or, do I link them all together
and have the user update the query that links them all together?

Thank you!

geri

 

[Allen Browne]

Geri, there is no standard way to handle this, since there are so many
variations.

One approach is to put all the entities into one table (GMC, GM, Cad, etc),
and then define which is the parent of which. The advantage of this approach
is that it is incredibly flexible, and lets you handle any combination of
situations with ease. The disadvantage is that it leads to a
Bill-of-Materials type structure which is not easy to query and can contain
infinite loops (where an entity is its own grandparent, for example.)

For more info on that structure, see:
People in households and companies
at:
http://allenbrowne.com/AppHuman.html

 

[Guest]

Since it would be rather redundant to put it all in 1 table. Would you
recommend having a menu to select the 4 tables to update if any need to have
additions (i.e. adding a new parent, or new division or new customer)?

Appreciate your helpful informative links. You have helped me alot over the
months!!

Thank you very much.

Geri

 

[Allen Browne]

The trouble with 4 tables is that you have different places to maintain.

GM could be a customer in its own right? And it cold also be the parent of
another customer, or the child of another? So how does the poor end user
know which table it should ultimately go into. And what if some day in the
future, GMC becomes a subsidiary of GE or some other company?

I don't know your data, but I can't imagine trying to use 4 separate tables
as you suggest.

 

[Guest]

Hi Allen,

Does that mean I build the 4 tables but create a query where they are all
pull all of the fields into one file and then I create a form with all of
those fields so that the user can update? Sorry, I'm a little confused
still. I thought that usually a table should contain a unique identifier and
name, and then some common linked field. But, isn't it not ideal to store
duplicate name type fields?

Could you please provide an example?

Division table contains--
Div_no Division Parent_no
ACOR AUTO CORPORATION ACO
HOOD HOOD AHO
ACAR ACARA AHO
ANID ANIMATED ANI

Customer table contains--
Customer_no Customer_name Parent_no Industry_class
951111 AMERICAN HOOD CO. AHO OEM
951222 AUTO CORPORATION ACO AWS

Parent table contains--
Parent_no Parent_name
ACO Auto Corporation
AHO AHM Corporation
ANI ANIMA Corporation

Industry Class table contains--
Industry_class_code Industry_class_descrip
DWS Dealers
OEM OEM

Since the Customer table has the parent_no and industry_class, those tables
link to it that way. Then the Parent and the Division link because of the
common parent_no in the Division table.

Thank you!

 

[Allen Browne]

Hi Geri

I have made some bad assumptions about what you were trying to do. It seems
that you already have a structure that copes with everything you need, where
one "Parent" has multiple Customers and Divisions. This implies that the
Parent is already entered before the user enters a new Customer. Since
Divisions relate to the Parent, and not to the Customer, I still don't see
the problem, i.e. there is no need to enter a Division in order to enter a
Customer, since a Division is an attribute of the Parent which was already
entered earlier.

I'm afraid that I'm not seeing your issue well. Perhaps someone else can
help.

My original take was that real-world company tiers don't always fit into
exactly the 4-levels you proposed, but that is not the issue you are
struggling with. Now I don't understand the connection between Customer and
Division, since there does not appear to be a direct connection in your
structure. Sorry that's not much help.

 

[Guest]

I'm not too good at UI's and was hoping that you could explain how best to
structure the form so that the user can update for a new customer, parent,
etc.. You are correct, that there isn't a correlation between Division and
Customer, since it is the Parent that drives which type of Division. Would
you create a UI with the 4 tables or a query that links them all so that the
user could add a new Customer (and also update the other tables)?

Sorry for not explaining this well.

Thanks again.

 

[Allen Browne]

On the Customer form, I would provide some mechanism for adding a new
Parent. It might be a DblClick of the Parent_No combo, or a command button
beside that control. The event procedure would OpenForm the Parent form, so
the user can add/edit entries there.

That form's AfterUpdate event would requery the combo on the other form so
that the combo gets the new/changed entry in its list:
Private Sub Form_AfterUpdate()
If CurrentProject.AllForms.Customer.IsLoaded Then
Forms!Customer!Parent_No.Requery
End If
End Sub
That form would have a subform for entering the Division.

You would so something similar for entering a new Class (DblClick of the
Industry_Class combo on the Customer form.)

Now you can directly open any of the 3 forms, or you can get at any of them
from the Customer form. But each form is bound to just one table.

HTH

 

[Guest]

Thanks for your suggestions, Allen. Will give them a try tomorrow.