creating a table for accessing data via Access forms
in [Help]
|
Prev: Get Access to send data back to website
Next: BUY FAKE ID CARD,SALE FALSE ID CARD,BUY FAKE ONLINE ID CARD,BUY FAKE ID UK,BUY FAKE PASSPORT
From: g on 14 Jun 2010 13:19 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 14 Jun 2010 17:33 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 14 Jun 2010 19:27 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 14 Jun 2010 19:32 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 14 Jun 2010 19:47
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; |