Form to perform calculation

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • muhes
    New Member
    • Feb 2007
    • 76

    #1

    Form to perform calculation

    Hello,

    I am working on a project for my department to design a form to calculate budgets. I really don't know much about access or programming in general.

    At this point, I am trying to design a form where I can add fields to a table. (At least I think this is what I want to do). The form currently has a text box for each category of employee, which is bound to a table. Every employee category will have different rules that determine the final budget. What I need is a way to add in additional employees within a category: for example one budget might have three category one employees, two category five employees, and ten category eight employees. Each of these employees will have will then have attendant information associated with it such as the name, salary, percent of effort etc...

    My form currently has a listing for each employee category. What I would like to design is either a form or subform where I can input the number of employees within each cateogry, and a new field or new text box will automatically be created.

    Thanks for your help.

    James
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    I have a bit of trouble following everything.

    Could you give me some more information about the tables?
    Can you list the fields in the table and the type of information it stores.

    And please provide an example of how things would change when you "create a new field".

    Comment

    • muhes
      New Member
      • Feb 2007
      • 76

      #3
      Hello Rabbit,

      Thank you for your quick response.

      I don't know if this is the optimal table setup for what I hope to accomplish, but right now I have a table TblEmployeeCate gory for each category of employee. This has a key number which is auto-generated. It is a text table with 50 character max. I also have an TblEntry, which the form FrmEntry is based on. This has a Project Start date field (Short Date), a number of years field, a salary field (currency), a percent of effort for summer, winter, and a list box for Employee Category. It might seem that what I have would work since each record represents one person, but for the calculation, I need to be able to distinguish where one budget begins and another ends. For example one budget might have ten people, where another would have twenty or more. Also, the project start date, end date, and # of years won't change for any one person. So what I would like to have set up is a form where once I enter the data for the first person, the fields for the second are automatically created on that form. I guess that I want each form to represent a budget, if that makes sense.

      Thanks again for your help

      Comment

      • muhes
        New Member
        • Feb 2007
        • 76

        #4
        One other thing:

        If Certain employee categories are selected, the annual year is measured differently.

        Comment

        • Rabbit
          Recognized Expert MVP
          • Jan 2007
          • 12517

          #5
          The very first thing we want to do is to clarify and modify how you are storing your data.

          Please take a look at Normalisation and Table structures

          I haven't read it myself but I hear it's very good.

          Secondly, when you say you have a TblEmployeeCate gory for each Employee Category. Are you saying that you have multiple tables? i.e. TblEmpCatProgra mmer, TblEmpCatAnalys t, etc. Or do you mean in that table you have multiple fields/variables with those headings. These are not the best ways to store the data.

          As for grouping people by budgets, you can just add a budget ID field.

          Once you get the basic data storage structure down, we can continue from there.
          Last edited by Niheel; Feb 7 '07, 06:20 PM.

          Comment

          • muhes
            New Member
            • Feb 2007
            • 76

            #6
            Hello Rabbit,

            Thank you again for your reply. I have read about normalization before, but I guess that I don't entirely understand how to apply it to what I am hoping to accomplish. For example, I don't need to store data about each employee's phone number, address, position etc... All that I need to know is what their salary is, what their employee category is (which determines how annual increases are determined), what their perecent of effort is for AY and or Summer (which is also determined by their employee category). But what is confusing is that these attributes by themselves are not always connected with the employee.

            Let me try to give an example: Employee Category 1 is a Professional Research Associate. They are budgeted for a raise on October 1st. Their annual salary is based on a twelve month year and requires no adjustment. Their perecent of effort doesn't require a division between the summer and the academic year.

            Employee Category 2 is a Professor. They are budgeted for a raise on July 1st. Their annual salary is based on a nine month year, and does require an adjustment. Their percent of effort does require a division between the summer and accademic year.

            Dr. Jonas might work on one project as an Employee Category 1, and a separate project as an Employee Category 2, at least for budget purposes.

            What I have right now is the TblEmployeeCate gory which holds only the automatically generated key number "employee category ID", and the "Title". I also have EntryTable, which has the above mentioned information with employee category ID from TblEmployeeCate gory listed as a foreign key in EntryTable. This isn't ideal, but in terms of running a query, I can say that when it is a Category 1, that the field for percent of effort AY, be considered the total amount.

            I appreciate your help. Please let me know if you can think of a better way to normalize. Part of the problem for me is that I am not necessarily using access in its traditional sense to store data, but rather to manipulate it.

            Thanks again.

            James


            Originally posted by Rabbit
            The very first thing we want to do is to clarify and modify how you are storing your data.

            Please take a look at http://www.thescripts.com/forum/show...72#post2296372

            I haven't read it myself but I hear it's very good.

            Secondly, when you say you have a TblEmployeeCate gory for each Employee Category. Are you saying that you have multiple tables? i.e. TblEmpCatProgra mmer, TblEmpCatAnalys t, etc. Or do you mean in that table you have multiple fields/variables with those headings. These are not the best ways to store the data.

            As for grouping people by budgets, you can just add a budget ID field.

            Once you get the basic data storage structure down, we can continue from there.

            Comment

            • Rabbit
              Recognized Expert MVP
              • Jan 2007
              • 12517

              #7
              First let's expand on your examples to get a better idea of how you want to manipulate your data.

              Dr. Jonas works as a Category 1 and Category 2. Knowing this, what are you trying do determine about Dr. Jonas?

              Comment

              • Rabbit
                Recognized Expert MVP
                • Jan 2007
                • 12517

                #8
                I'll have to pick this up tomorrow. I'm off work now. Maybe someone else can pick up where I left off until I get back tomorrow.

                I tend to come here when I have nothing else to do at work.

                Comment

                • muhes
                  New Member
                  • Feb 2007
                  • 76

                  #9
                  Thanks again for your response Rabbit:

                  What I need to know about Dr. Jonas is his Employee Category for the particular budget. (Dr. Jonas would never be a category 1, and a category 2 within the same budget.) The way the data needs to be manipulated varies depending on the category of employee. Let me try to provide a more detailed example. A very simple budget might have only two people, one employee from two distinct categories, say a category 1 and 3. The people putting together the budgets would enter the name for each person, their starting salary, the number of months they work (possibly broken down into AY and Summer), and the percent of effort (again possibly broken down into AY and Summer). Let's assume the project starts in May, and will go for three years.

                  The Category one person is a Professional Research Associate, call him Dr. David. For the current year he makes 50,000, and will be budgeted to work 100% time for 12 months. The project starts in May, and as a Category 1 professional, he will get an assumed raise on October 1st. For year 1, his budget is his yearly amount divided by twelve multiplied by the number of months prior to October, plus his yearly amount divided by twelve multiplied by the remaining amount of months, multiplied by the inflationary factor of 5%. So: (50,000/12*5)+(50,000/12*7*1.05).

                  The category three person is a Graduate Research Assistant, call him Mr. Nora. He is not paid an annual amount, but rather a monthly amount of $1500, assuming a 50% work effort for 9 months. A category three person will have his effort broken down between the AY year and the Summer. In this case, we'll say that Mr. Nora works seven months in the AY, and two months in the summer. Again the project starts in May, but as a category three person, he will get an assumed raise on July 1st. For year 1, his budget is his monthly amount multiplied by the number of months prior to July plus his monthly amount multiplied by the remaining months, multiplied by the inflationary factor (in this case 3.6%). So: (1500*2)+(1500* 10*1.036).

                  Note that in the above examples percent of effort isn't a factor, because it is 100% for the first example, and because for the second example 50% is the standard, and their is no variation between AY effort and Summer.

                  To some extent this is extraneous information, but I want you to better understand what I hope to accomplish. The information that stays constant is the rules governing how the yearly budget for each category of employee is determined, but the employees themselves aren't always in the same category, depending on the work they do for any given project. This was a fairly simple budget, and I left out some of the complicating factors, but you can probably imagine that when you have a budget that has twenty people from multiple categories, it becomes more complex. Thanks again for your help. I apologize for the long explanation.

                  Comment

                  • Rabbit
                    Recognized Expert MVP
                    • Jan 2007
                    • 12517

                    #10
                    Not at all, the more specific your examples and details, the better we can gear a solution to your needs.

                    But for now, it seems you just need to identify who is in which budget. The easiest solution to this is to have an extra field called, let's say, BudgetID which is assigned to each employee of each budget. This BudgetID could either be manually inputted or automatically caluclated, depending on your needs.

                    But, let's say Dr. Jonas is Category 1 for Budget A and Category 3 for Budget B.

                    Then in the table, you would have 2 records for Dr. Jonas:
                    Code:
                    Dr. Jonas	 Category1	 BudgetA
                    Dr. Jonas	 Category3	 BudgetB

                    Comment

                    • muhes
                      New Member
                      • Feb 2007
                      • 76

                      #11
                      Thanks again for your response, Rabbit.

                      If this field were automatically generated, could it determine where one budget ends and the other begins?

                      Is there a way to get multiple records displayed on one form? From what you said, I would imagine a form where one person is displayed at a time. For example Dr. Jonas, with his salary, percent of effort, AY time, etc... In order to see the next person you would click the next record button at the bottom. This might work, but it would a little cumbersome if you had to change something specific, or multiple things. Also it wouldn't let you see the overview of a budget during the entry process.

                      Along this line, is there a way that I can get the form to respond to prompts? For example, it might start out with a listing for one entry for each category, but with the ability to say add five category one employees, skip all of the category two and three, and add six category four employees. This is somewhat what I was referring to before when I talked about adding a field.

                      Thanks again for your time and help. I feel like I am making progress.

                      James

                      Comment

                      • Rabbit
                        Recognized Expert MVP
                        • Jan 2007
                        • 12517

                        #12
                        Originally posted by muhes
                        Thanks again for your response, Rabbit.

                        If this field were automatically generated, could it determine where one budget ends and the other begins?

                        Is there a way to get multiple records displayed on one form? From what you said, I would imagine a form where one person is displayed at a time. For example Dr. Jonas, with his salary, percent of effort, AY time, etc... In order to see the next person you would click the next record button at the bottom. This might work, but it would a little cumbersome if you had to change something specific, or multiple things. Also it wouldn't let you see the overview of a budget during the entry process.

                        Along this line, is there a way that I can get the form to respond to prompts? For example, it might start out with a listing for one entry for each category, but with the ability to say add five category one employees, skip all of the category two and three, and add six category four employees. This is somewhat what I was referring to before when I talked about adding a field.

                        Thanks again for your time and help. I feel like I am making progress.

                        James
                        For your purposes I would shy away from autocalculating the Budget ID. It would make it hard for you to identify which budget you're looking at. Instead you could, at the creation of the budget, ask for the user to name the project. And so to retrieve everyone on a certain budget, the database could ask for the user to input or select the name of the budget and it would pull all the records where the budget name was the same.

                        There is a way to display all the records on one form. If you change the default view to continuous form in the form's properties, this will show all the records on one form. Much like a report but with fields you can edit.

                        As for adding records, it'd be much simpler to have the user fill in the employee's information one person at a time along with their category rather than choosing the category, its amount of employees, and then to fill in personal information.

                        In the end, you would most likely have multiple forms. A main menu form which opens up at the start with options such as: Create New Budget and View/Edit Budget. And whatever else you may want to have. And then each of those would open up their own forms. The View/Edit Budget would, let's say, have them choose a Budget Name from a list which would open up the continuous form displaying only those records. And the Create New Budget would ask for a Budget Name, check to see if it exists, and then open up the same continuous form but it would only have 1 record, a blank record because there would be no records with that Budget Name.

                        And on the opening of these forms, depending on what option the user chose, it would set and turn on the filter for the form.

                        This could be a possible way to organize the database.

                        Comment

                        • MMcCarthy
                          Recognized Expert MVP
                          • Aug 2006
                          • 14387

                          #13
                          Originally posted by Rabbit
                          Not at all, the more specific your examples and details, the better we can gear a solution to your needs.

                          But for now, it seems you just need to identify who is in which budget. The easiest solution to this is to have an extra field called, let's say, BudgetID which is assigned to each employee of each budget. This BudgetID could either be manually inputted or automatically caluclated, depending on your needs.

                          But, let's say Dr. Jonas is Category 1 for Budget A and Category 3 for Budget B.

                          Then in the table, you would have 2 records for Dr. Jonas:
                          Code:
                          Dr. Jonas	 Category1	 BudgetA
                          Dr. Jonas	 Category3	 BudgetB
                          If you think of this as a join table. You could have a main form based on Dr. Jonas and it could contain any single record information on Dr. Jonas.
                          Then you could have a subform based on this table which would show each category and budget record for Dr. Jonas.

                          Comment

                          • NeoPa
                            Recognized Expert Moderator MVP
                            • Oct 2006
                            • 32669

                            #14
                            Just posting to register this thread in my list so I can keep an eye out.

                            Comment

                            • muhes
                              New Member
                              • Feb 2007
                              • 76

                              #15
                              Hello Rabbit,

                              Thank you once again for your response to my questions. With regards to naming the budget, how exactly would that work? Would it be a required field at the top of the budget, or would it be some sort of user prompt? Could it be changed at a later date? People don't always know the name of their project prior to beginning.

                              Thank you for the information about continuous viewing. I tried that, and I think that will work fairly well.

                              Can you tell me a little bit more about how to setup the structure mentioned in your last post? Do I use something like a command button, or do I create a subform?

                              Thanks

                              Comment

                              Working...