Missy Engel's Marketing

Question: creating a table for accessing data via Access forms?
I have to create a table which has data about different divisions(and
departments inside it) of a company. company A has divisions of Sales,
Engineering, Customer Support, Corporate Office. Sales division further has
departments like marketing, pre-sales department, accounting. Engineering
division has departments like Research and Development, Building group,
Testing group. Customer Support has departments like Phone Support, Email
support, Feedback. Corporate Office has deparments like CEO office, Public
Relations and so on.

For each department of a division like Engineering, there are some methods to
gauge performance of the department. For instance, in the Research and
Development department, a criteria is set like

Sample Criteria Name Sample Value

Aim 1 Develop New Products

State what/how will ‘X’ new products
be done to achieve will be developed
the Aim 1 this year

Determine how By mid year 3 new
progress will be products will be
measured launched in the
for reaching Aim 1 market

Determine The revenue generated
proposed benefit by new products will
from Aim 1 be amount ‘N’

Aim 2 Research new markets

State what/how will ‘Y’ new markets where our
be done to achieve company products can be
the Aim 2 sold will be found this year

Determine how By mid year 2 new
progress will be markets will be
measured tested for viability
for reaching Aim 2

Determine The revenue generated
proposed benefit by selling products in the
from Aim 2 new markets will be amount ‘N’

.
.
.
.

Aim N Research new methods to increase
productivity

State what/how will ‘Z’ new methods to boost employee
be done to achieve productivity will be
the Aim ‘N’ found this year

Determine how By end of June, 2 new
progress will be methods will be
measured tested for increasing
for reaching Aim ‘N’ productivity

Determine The costs reduced
proposed benefit by increase in productivity
from Aim ‘N’ will be amount ‘V’

The same for Building group and other departments
in other divisions.

For year 2001, there can be 10 number of aims(targets) for a department in a

division, for 2002 there can be 12 and so on. But, a aim will not be repeated
for the same year for the same department in a division. For example,

“Research new markets” aim will be present only once for 2001 for Research

and Development department in Engineering division. And, the maximum number

of aims can be 15 for a department in a division for a given year.

After the table is created and populated with data,
using forms created in MS-Access, users will run some
queries like they may choose

Year = 2001
Division = Engineering
Department = Research and Development
Aim = Research new markets
Proposed Benefit(Field for which he needs information)

The Aim field in the form will need to search all Aim 1, Aim 2…Aim N
fields for year 2001, Engineering division, Research and Development

department to find out which Aim field has content = “Research new markets”,

then display data on what is present for corresponding field Proposed Benefit

which will be “The revenue generated by selling products in the new markets

will be amount ‘N’ ”

Questions are

1. What would be an advisable way to create a new table so that data can be
accessed from the Access forms? Currently, the data is in below format in a

another Access table.

Field 1: Year

Field 2: Division

Field 3: Department

Field 4: Aim 1

Field 5: State what/how will
be done to achieve
the Aim 1

Field 6: Determine how
progress will be
measured
for reaching Aim 1

Field 7: Determine
proposed benefit
from Aim 1

Field 8: Aim 2

Field 9: State what/how will
be done to achieve
the Aim 2

Field 10: Determine how
progress will be
measured
for reaching Aim 2

Field 11: Determine
proposed benefit
from Aim 2

.
.
.
.

Field M: Aim N

Field M+1: State what/how will
be done to achieve

Answer:

Answer by C
Split the table by nouns like the tables will be Department, Division, Aims and so on. Add a primary key, then add a relation between tables.