creating a table for accessing data via Access forms

G

g

I have to create an Access 2007 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 departments 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)
to generate reports


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 2007 table(Table A).

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
the Aim 'N'

Field M+2: Determine how
progress will be
measured
for reaching Aim 'N'

Field M+3: Determine
proposed benefit
from Aim 'N'


and all data types are of type Memo.


2. How can Table A be restructured so that it complies with good
database design principles?

Any suggestions would be appreciated.
 
J

James A. Fortune

I have to create an Access 2007 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 departments 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)
to generate reports

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 2007 table(Table A).

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
the Aim 'N'

Field M+2: Determine how
progress will be
measured
for reaching Aim 'N'

Field M+3: Determine
proposed benefit
from Aim 'N'

and all data types are of type Memo.

2. How can Table A be restructured so that it complies with good
database design principles?

Any suggestions would be appreciated.
Click to expand...

Conventional wisdom might suggest something like the following:

tblDivision
DivisionID AutoNumber
Division Text
DivisionAbbr Text

tblDept
DID AutoNumber
DepartmentName Text
DivisionID Long (Foreign Key)

tblDeptEntries
DEID AutoNumber
EID Long (Foreign Key)
DID Long (Foreign Key)
DepartmentName Text (Optional Denormalization - not recommended by
most)
DivisionID Long (Foreign Key)
Division Text (Optional Denormalization - not recommended by most)
MetricID Long (Foreign Key)
MetricName Text (Optional Denormalization - not recommended by most)
MetricValue Double

tblMetrics
MID AutoNumber
MetricYear Long
MetricDepartment Long
MetricName Text
MetricDescription Text
What Text
How Text
Benefit Text

tblExpandedIndex
EID AutoNumber
ProductID Long
TaskNumber Long
DID Long

tblProducts
ProductID AutoNumber
Version Text
....

tblTasks
TID AutoNumber
TaskNumber Long
TaskDescription Text

tblDivision
1 Sales
2 Engineering
3 Customer Support
4 Corporate Office
....

tblDept
1 Marketing 1
2 Pre-Sales Department 1
3 Accounting 1
4 Research And Development 2
5 Building Group 2
6 Testing Group 2
7 Phone Support 3
8 Email Support 3
9 Feedback 3
10 CEO Office 4
11 Public Relations 4

tblMetrics
1 2010 Aim1 Develop New Products 'X' new products will be developed
this year By mid year 3 ...
2 2010 Aim2 ...

tblDeptEntries
1 1 1 Marketing 1 Sales 1 Aim1 9.3

tblExpandedIndex is thrown in to have something to think about if the
tables are going to get really large. However, no matter how hard you
try to nail down Divisions and Departments, they are going to change.
When they do, it is going to create a maintenance nightmare. My
suggestion is to come up with a way to tame the nightmare before she
gets too wild. One way to do that would be to define a Division as a
set of Departments and run update queries that reassign old
departments into newly defined departments. Something like:

tblDivision
DID AutoNumber
DivisionName Text
DID Long (Foreign Key)

tblDivision
1 Sales 1
2 Sales 2
3 Sales 3
4 Engineering 4
....

That's still not perfect, but I recommend spending a lot of time up
front (perhaps a few months) trying to make everything in the schema
as flexible against future change as possible. It wouldn't hurt to
write out the SQL of some of the queries you know you'll likely need
against proposed schemata. You'll probably need even more tables.
Your database is going to start off fairly complicated and get more
complicated with time. Good planning is paramount.

James A. Fortune
(e-mail address removed)
 
S

Salad

James said:
Conventional wisdom might suggest something like the following:

tblDivision
DivisionID AutoNumber
Division Text
DivisionAbbr Text

tblDept
DID AutoNumber
DepartmentName Text
DivisionID Long (Foreign Key)

tblDeptEntries
DEID AutoNumber
EID Long (Foreign Key)
DID Long (Foreign Key)
DepartmentName Text (Optional Denormalization - not recommended by
most)
DivisionID Long (Foreign Key)
Division Text (Optional Denormalization - not recommended by most)
MetricID Long (Foreign Key)
MetricName Text (Optional Denormalization - not recommended by most)
MetricValue Double

tblMetrics
MID AutoNumber
MetricYear Long
MetricDepartment Long
MetricName Text
MetricDescription Text
What Text
How Text
Benefit Text

tblExpandedIndex
EID AutoNumber
ProductID Long
TaskNumber Long
DID Long

tblProducts
ProductID AutoNumber
Version Text
...

tblTasks
TID AutoNumber
TaskNumber Long
TaskDescription Text

tblDivision
1 Sales
2 Engineering
3 Customer Support
4 Corporate Office
...

tblDept
1 Marketing 1
2 Pre-Sales Department 1
3 Accounting 1
4 Research And Development 2
5 Building Group 2
6 Testing Group 2
7 Phone Support 3
8 Email Support 3
9 Feedback 3
10 CEO Office 4
11 Public Relations 4

tblMetrics
1 2010 Aim1 Develop New Products 'X' new products will be developed
this year By mid year 3 ...
2 2010 Aim2 ...

tblDeptEntries
1 1 1 Marketing 1 Sales 1 Aim1 9.3

tblExpandedIndex is thrown in to have something to think about if the
tables are going to get really large. However, no matter how hard you
try to nail down Divisions and Departments, they are going to change.
When they do, it is going to create a maintenance nightmare. My
suggestion is to come up with a way to tame the nightmare before she
gets too wild. One way to do that would be to define a Division as a
set of Departments and run update queries that reassign old
departments into newly defined departments. Something like:

tblDivision
DID AutoNumber
DivisionName Text
DID Long (Foreign Key)

tblDivision
1 Sales 1
2 Sales 2
3 Sales 3
4 Engineering 4
...

That's still not perfect, but I recommend spending a lot of time up
front (perhaps a few months) trying to make everything in the schema
as flexible against future change as possible. It wouldn't hurt to
write out the SQL of some of the queries you know you'll likely need
against proposed schemata. You'll probably need even more tables.
Your database is going to start off fairly complicated and get more
complicated with time. Good planning is paramount.

James A. Fortune
(e-mail address removed)
Click to expand...

The neat thing about this group is that people spend time helping others
out. You spent some time, James.

My concept was create a lookup table of Aims. Then create another
select query that selects existing AimIDs for year, division, department
that already exist.

Link the lookup table as a leftjoin to the query.

The form's recordsource would have an AimID. The would be the
controlsource for the combo. Tnen create a hidden control (TextboxAim)
with no controlsource.

In the OnCurrent event, do something like
Me.TextboxAim = IIF(Not Me.NewRecord,Me.AimID,0)

Then create a combo for Aims lookup table. I guess there's 15 Aims.

The query would be something like
Select AimID From MasterTable where
Division = Forms!Formname!Division and
Department = Forms!Formname!Department and
YearOf = Forms!Formname!YearOf

The rowsource would select all AimIDs from the lookup table where the
year, division, department don't exist in the query or is the same AimID
as TextboxAim.

The user, if a new record, would need to select/enter the division,
department, and year before a valid list would be created/displayed in
the combobox.
 
G

g

Conventional wisdom might suggest something like the following:

tblDivision
DivisionID AutoNumber
Division Text
DivisionAbbr Text

tblDept
DID AutoNumber
DepartmentName Text
DivisionID Long (Foreign Key)

tblDeptEntries
DEID AutoNumber
EID Long (Foreign Key)
DID Long (Foreign Key)
DepartmentName Text (Optional Denormalization - not recommended by
most)
DivisionID Long (Foreign Key)
Division Text (Optional Denormalization - not recommended by most)
MetricID Long (Foreign Key)
MetricName Text (Optional Denormalization - not recommended by most)
MetricValue Double

tblMetrics
MID AutoNumber
MetricYear Long
MetricDepartment Long
MetricName Text
MetricDescription Text
What Text
How Text
Benefit Text

tblExpandedIndex
EID AutoNumber
ProductID Long
TaskNumber Long
DID Long

tblProducts
ProductID AutoNumber
Version Text
...

tblTasks
TID AutoNumber
TaskNumber Long
TaskDescription Text

tblDivision
1 Sales
2 Engineering
3 Customer Support
4 Corporate Office
...

tblDept
1 Marketing 1
2 Pre-Sales Department 1
3 Accounting 1
4 Research And Development 2
5 Building Group 2
6 Testing Group 2
7 Phone Support 3
8 Email Support 3
9 Feedback 3
10 CEO Office 4
11 Public Relations 4

tblMetrics
1 2010 Aim1 Develop New Products 'X' new products will be developed
this year By mid year 3 ...
2 2010 Aim2 ...

tblDeptEntries
1 1 1 Marketing 1 Sales 1 Aim1 9.3

tblExpandedIndex is thrown in to have something to think about if the
tables are going to get really large. However, no matter how hard you
try to nail down Divisions and Departments, they are going to change.
When they do, it is going to create a maintenance nightmare. My
suggestion is to come up with a way to tame the nightmare before she
gets too wild. One way to do that would be to define a Division as a
set of Departments and run update queries that reassign old
departments into newly defined departments. Something like:

tblDivision
DID AutoNumber
DivisionName Text
DID Long (Foreign Key)

tblDivision
1 Sales 1
2 Sales 2
3 Sales 3
4 Engineering 4
...

That's still not perfect, but I recommend spending a lot of time up
front (perhaps a few months) trying to make everything in the schema
as flexible against future change as possible. It wouldn't hurt to
write out the SQL of some of the queries you know you'll likely need
against proposed schemata. You'll probably need even more tables.
Your database is going to start off fairly complicated and get more
complicated with time. Good planning is paramount.

James A. Fortune
(e-mail address removed)
Click to expand...

Thanks James,

That is very useful indeed.
 
S

Salad

Salad said:
The neat thing about this group is that people spend time helping others
out. You spent some time, James.

My concept was create a lookup table of Aims. Then create another
select query that selects existing AimIDs for year, division, department
that already exist.

Link the lookup table as a leftjoin to the query.

The form's recordsource would have an AimID. The would be the
controlsource for the combo. Tnen create a hidden control (TextboxAim)
with no controlsource.

In the OnCurrent event, do something like
Me.TextboxAim = IIF(Not Me.NewRecord,Me.AimID,0)

Then create a combo for Aims lookup table. I guess there's 15 Aims.

The query would be something like
Select AimID From MasterTable where
Division = Forms!Formname!Division and
Department = Forms!Formname!Department and
YearOf = Forms!Formname!YearOf

The rowsource would select all AimIDs from the lookup table where the
year, division, department don't exist in the query or is the same AimID
as TextboxAim.

The user, if a new record, would need to select/enter the division,
department, and year before a valid list would be created/displayed in
the combobox.
Click to expand...

Forgot to supply the reason for the hidden textbox.
SELECT AimsLookup.AimID, AimsLookup.AimDescription
FROM AimsLookupr LEFT JOIN Query1 ON AimsLookup.AimID = Query1.AimID
WHERE Query1.LocationID Is Null OR Query1.AimID=Forms!FormName!TextboxAIM;
 
G

g

Forgot to supply the reason for the hidden textbox.
SELECT AimsLookup.AimID, AimsLookup.AimDescription
FROM AimsLookupr LEFT JOIN Query1 ON AimsLookup.AimID = Query1.AimID
WHERE Query1.LocationID Is Null OR Query1.AimID=Forms!FormName!TextboxAIM;
Click to expand...

Thanks a lot. That advice is highly appreciated.

Would you recommend a book/site for coming up to speed on
creating/programming such forms in Access? Currently, I am using
http://msdn.microsoft.com/en-us/library/bb149076(v=office.12).aspx

Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

why should i use access over oracle, for a facilities management . 3
Transpose Access Table / Query 5
Logical planning and implementing company's structure in PS 2007 6
Efficient Table Design Questions 3
normalizing and creating fields or related tables 0
Db design review 14
Linking data from imported Excel Worksheets 0
?is my table design poor? 5

Top