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 21:01 On 6/14/2010 7:47 PM, Salad wrote: > 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; Thanks a lot. That advice is highly appreciated. Would you recommend a book/site for coming up to speed on creating/programming such forms in Access? Currently, I am using http://msdn.microsoft.com/en-us/library/bb149076%28v=office.12%29.aspx Thanks again.
From: Salad on 14 Jun 2010 21:41
g wrote: > 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/library/bb149076%28v=office.12%29.aspx > No I don't. That seemed to be a common question a few years ago. I think it depends on a person's skill level, info required, future plans 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 > Thanks again. UR welcome. |