Need Ideas For Layout Of Form

[Tom]

I am looking for some ideas for how to design the layout of the form for
data entry and to display the data for the following situation:
There are many sales associates. A sales associate can work for multiple
companies and work for multiple divisions within each company. Within each
division he can work in multiple departments and within each department he
can work with multiple groups. In each group he works on multiple projects.
All the work areas of each sales associates needs to be input in the form
and displayed in the form - Companies, Divisions, Departments, Groups and
Projects.There are one-to-many relationships between company and division,
division and dpartment, department and group and group and project.

Thanks for all help,

Tom

 

[Guest]

Based on your post, I would suggest the form be based on the sales associate
and that you use cascading combo boxes to select the entities.

 

[Guest]

Here's my suggestion. On the form for each sales associate, include a subform
(as a datasheet) to serve as a list of work areas for the sales associate.

The datasheet has combo box fields for company, division etc. When the user
chooses a company, the division combo box has its row source updated to show
only divisions of that company. When the division combo box is updated, the
department combo box has its row source updated to show only departments in
that division. Etc etc.

You might want to lock or disable all of the combo boxes apart from company.
Then, when company has a value, unlock division. When division has a value,
unlock department etc etc. This will mean the user enters the information in
the correct order.

Your architecture does suggest that all companies are organised in the same
way - divisions, departments, groups, projects. You might want to think about
how the database handles exceptions to that pattern.

Hope this helps.

David

 

[Allen Browne]

Tom, the question is not so much about the form, as it is about getting the
data structure right.

To my way of thinking, companies, divisions, and departments are actually
layers of the same kind of entity, whereas projects are a different kind of
entity. A project might be shared amongst different departments or
divisions, and could have a starting and ending date, whereas a department
would be a (normally) permanent section of just one division and so forth up
the tree.

If that is so, you might consider making a "Client" table where you can
store all the first kind of entity. It will have a ClientID field as primary
key, and a ParentID field that indicates the immediate parent of this
client. So, if the "client" is a division type, its ParentID would be the
ClientID of a record that is a company.

You would then have another table for Projects, and a table for Staff.
Another table might then have fields:
ClientID: foreign key to Client.ClientID (so can be a co, dept, div,
....)
ProjectID: foreign key to Project.ProjectID
StaffID: foreign key to Staff.StaffID
WorkDate: date the work was done.
Each record says something like this: on August 1 (work date), Joe Smith
(staff) worked for the IT Department (client) on project 49 (whatever that
is.)

If that copes with what you need, you would then have a form with 3 combos
and a text box for the date. Chances are, that's not exactly what you need,
but hopefully it gets you thinking in a useful direction.

 

[Tom]

Allen,

Thanks for the response!

I need records to say:
On August 1 (work date), Joe Smith worked for the Industrial Group (group)
of the IT Department (department) of the ABC Division (division) of IT Inc.
(company) on project 49 (project).

On August 1 (work date), Joe Smith worked for the Refining Group (group) of
the Engineering Department (department) of the Construction Division
(division) of IT Inc. (company) on project 27 (project).

On August 1 (work date), Joe Smith worked for the Marketing Group (group) of
the Sales Department (department) of the XYZ Division (division) of IT Inc.
(company) on project 84 (project).

I need a form to enter these records for Joe Smith for August 1.

Thanks,

Tom

 

[Tom]

Thank you for your response!

So you are suggesting a combobox for company, division, department, group
and project and creating records in a continuous form. I thought of that too
but was also wondering about using a form, subform, subsubform,
subsubsubform etc. What do you think?

Thanks,

Tom

 

[Tom]

Thank you for your response!

So you are suggesting a combobox for company, division, department, group
and project and creating records in a continuous form. I thought of that too
but was also wondering about using a form, subform, subsubform,
subsubsubform etc. What do you think?

Good thouight about the architecture but all companies are organised in the
same way - no exceptions.

Thanks,

Tom

 

[Allen Browne]

So, you have a couple of choices.

Standard structure
==============
Create:
- a table of possible Group values (records for "Industrial", etc);
- another table to hold the Department values (records for "IT", etc.)
- another table for Division values.
- another table for Companies.
- the main table that will have fields:
o GroupID
o DepartmentID
o DivisionID
o CompanyID
o StaffID
o WorkDate

The disadvantage of that approach is that you can end up choosing nonsense
combinations, such as the Welfare department of a Law form.

Alternative structure
==============
The other structre suggested earlier has the disadvantage that you do have
to go through and enter each division that applies to each company, and then
each deparment of each division, and so on. While this takes more effort
initially, it may provide better data, and it copes with internal company
structures that may not match the standard one (e.g. small companies don't
have departments of divisions.)

Either way, once you have the tables in place, and the relationships between
them, you can start to think about how to create forms.

 

[Tom]

I have the standard table structure and now I'm looking for ideas for a
user-friendly form.

Tom