From: g on
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.
From: James A. Fortune on
On Jun 14, 1:19 pm, g <g...(a)g.com> wrote:
> 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.

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
CDMAPoster(a)FortuneJames.com
From: Salad on
James A. Fortune wrote:

> On Jun 14, 1:19 pm, g <g...(a)g.com> wrote:
>
>>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.
>
>
> 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
> CDMAPoster(a)FortuneJames.com

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.
From: g on
On 6/14/2010 5:33 PM, James A. Fortune wrote:
> On Jun 14, 1:19 pm, g<g...(a)g.com> wrote:
>> 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.
>
> 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
> CDMAPoster(a)FortuneJames.com

Thanks James,

That is very useful indeed.
From: Salad on
Salad wrote:

> James A. Fortune wrote:
>
>> On Jun 14, 1:19 pm, g <g...(a)g.com> wrote:
>>
>>> 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.
>>
>>
>>
>> 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
>> CDMAPoster(a)FortuneJames.com
>
>
> 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.

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;