 | |
Re: creating a table for accessing data via Access forms
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: creating a table for accessing data via Access forms -
06-14-2010
, 04:33 PM
|
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. |
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 (AT) FortuneJames (DOT) com
#2
| |||
| |||
|
|
On Jun 14, 1:19 pm, g <g...@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 (AT) FortuneJames (DOT) com |
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.
#3
| |||
| |||
|
|
On Jun 14, 1:19 pm, g<g...@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 (AT) FortuneJames (DOT) com |
That is very useful indeed.
#4
| |||
| |||
|
|
James A. Fortune wrote: On Jun 14, 1:19 pm, g <g...@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 (AT) FortuneJames (DOT) 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. |
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;
#5
| |||
| |||
|
|
Salad wrote: James A. Fortune wrote: On Jun 14, 1:19 pm, g <g...@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 (AT) FortuneJames (DOT) 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; |
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/libr...ice.12%29.aspx
Thanks again.
#6
| |||
| |||
|
|
On 6/14/2010 7:47 PM, Salad wrote: Salad wrote: 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; 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/libr...ice.12%29.aspx No I don't. That seemed to be a common question a few years ago. I |
with a product, etc. I think I would go to something like Amazon or B&K
and do a search for books and spend some time reading reviews. Maybe
get the titles of some that interest you and then visit a bookstore and
see if they have the titles in stock and if so check out the TOC and
index of those books and see first hand what the book covers.
I went to Google and searched on "best books on access 2007". That got
a few hits.
Two sites I like are from Tony Toews and Allen Browne. Tony has a great
app for distibuting applications on a network calle AutoFe. They aren't
tutorial sites, just good info sites.
http://www.granite.ab.ca/accsmstr.htm
http://allenbrowne.com/tips.html
Quote:
|
Thanks again. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.