469,366 Members | 2,198 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

Form to perform calculation

77
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
Feb 6 '07 #1
133 10547
Rabbit
12,516 Expert Mod 8TB
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".
Feb 6 '07 #2
muhes
77
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 TblEmployeeCategory 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
Feb 6 '07 #3
muhes
77
One other thing:

If Certain employee categories are selected, the annual year is measured differently.
Feb 6 '07 #4
Rabbit
12,516 Expert Mod 8TB
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 TblEmployeeCategory for each Employee Category. Are you saying that you have multiple tables? i.e. TblEmpCatProgrammer, TblEmpCatAnalyst, 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.
Feb 6 '07 #5
muhes
77
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 TblEmployeeCategory 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 TblEmployeeCategory 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


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 TblEmployeeCategory for each Employee Category. Are you saying that you have multiple tables? i.e. TblEmpCatProgrammer, TblEmpCatAnalyst, 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.
Feb 7 '07 #6
Rabbit
12,516 Expert Mod 8TB
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?
Feb 7 '07 #7
Rabbit
12,516 Expert Mod 8TB
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.
Feb 7 '07 #8
muhes
77
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.
Feb 7 '07 #9
Rabbit
12,516 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dr. Jonas     Category1     BudgetA
  2. Dr. Jonas     Category3     BudgetB
Feb 7 '07 #10
muhes
77
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
Feb 7 '07 #11
Rabbit
12,516 Expert Mod 8TB
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.
Feb 7 '07 #12
MMcCarthy
14,534 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dr. Jonas     Category1     BudgetA
  2. 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.
Feb 8 '07 #13
NeoPa
32,185 Expert Mod 16PB
Just posting to register this thread in my list so I can keep an eye out.
Feb 8 '07 #14
muhes
77
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
Feb 8 '07 #15
Rabbit
12,516 Expert Mod 8TB
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
We'll start off with a Main Menu form.
In this form there will be a list box with two options: Create New Budget and Edit/View Budget. In the On Click event of the list box, there will be code to open a form depending on what they click.

So, let's say they click on Create New Budget. This will open up a different form or maybe just an input box that asks them to name the budget. After typing in a name, it will check for any budgets with the same name. After the check, it will open the continuous form for them to start adding employees.

If they click on Edit/View Budget, it would open a different form with a list box that lists the available budgets. After selecting a budget to view/edit, it would open the continuous form turning on the filter to just filter out all the records except the one chosen.

As for renaming a budget, it could be a third option on the main menu which opens up a form where they select which budget they want to rename. Then they type in the new name. And most likely, they would click a command button which runs an update query.
Feb 8 '07 #16
muhes
77
Thanks to everyone who has shown interest in my project, and special thanks to Rabbit for his invaluable help.

At this point I have restructured my tables slightly. Here are the tables and their relevant fields. Quotes designate the field name, and parathensis the relevant info.

TblEmployeeType: "Employee ID" (autogenerated, and key), "Employee Type" (text).

TblPositionName: "Name ID" (autogenerated, and key), "Employee ID" (number), "Position Name" (text)

TblEntry: "Entry ID" (autogenerated, and key), "Name ID" (number), "Budget ID" (number), "Name" (text), "Salary" (currency), "AY months" (number), "AY Percent of Effort" (number), "Summer months" (number), "Summer percent of effort" (number), "Project Start Date" (Date, short date), "Number of years" (number).

The form FrmEntry has a combo box which selects from TblPositionName, and has a control source of "Name ID". The rest of the form comes from TblEntry and uses all of the fields.

Since I am using a continuous form view, the project start date, the number of years, and the budget ID appear on each record. Is there a way that I can either duplicate the information automatically, or make it so that these fields only appear on the first record? I guess that this question is related to what you were discussing about creating a user prompt from the main form.

Also, can someone give me a good sense of whether my tables are fully normalized?

Thanks for your help.
Feb 9 '07 #17
Rabbit
12,516 Expert Mod 8TB
I'm curious as to how the TblEmployeeType relates to TblPositionName and if they need to be seperated at all.

To have a field only appear once, put them in the header or footer of the form and not the detail.
Feb 9 '07 #18
muhes
77
Hello Rabbit,

Thanks as always for your help.

Moving those fields to the header was a good solution. I guess my only question about this is how do I indicate that I have completed a budget?

It would seem that if I change the Budget ID field, that this action would change it for everything on the form. The next question I guess is, is having a budget ID the best way to designate different budgets? I liked your idea before about prompting the user to name the budget (which I assume would replace what I currently have as I Budget ID field in the TblEntry), but I don't know how to make this work.

With regards to your question about how TblEmployeeType relates to TblPositionName, I don't really know if they do need to be separated. Perhaps you can help me make that determination? TblEmployeeType designates an employees category (what I was talking about before with Category 1 employees). My thinking is that I will need that designation later when I run reports, because a category 1 employee will have his annual salary figured out differently. I guess I imagine it as creating a IIF statement, something like IIF field X is 2, (X=2(X/9*1.036*12*50%), (I'm sure that I don't have the correct syntax, but I'll work on that later) something like that. TblPositionName is used for the selection combo box. I wanted the association from the name and category to be automatic.
Feb 9 '07 #19
muhes
77
One other thing. I created a new text box for project end date, and I used the following statement tied to "Project Start":
Expand|Select|Wrap|Line Numbers
  1. =DateSerial(Year([Project Start])+([# of years]),Month([Project Start]),Day([Project Start])-1)
  2.  
However, now when I switch to a new record, the Project Start field and Project End one are blank. Is there anything I can do to prevent it from changing?

Thanks.
Feb 9 '07 #20
Rabbit
12,516 Expert Mod 8TB
I'm off work for the 3-day weekend. I'll get back to this when I'm in the office on Tuesday. Perhaps someone else will be able to guide you along until I get back.
Feb 9 '07 #21
NeoPa
32,185 Expert Mod 16PB
One other thing. I created a new text box for project end date, and I used the following statement tied to "Project Start":
Expand|Select|Wrap|Line Numbers
  1. =DateSerial(Year([Project Start])+([# of years]),Month([Project Start]),Day([Project Start])-1)
  2.  
However, now when I switch to a new record, the Project Start field and Project End one are blank. Is there anything I can do to prevent it from changing?

Thanks.
I won't take on this whole project, but while Rabbit's away I think I may be able to help here (assuming you can express more clearly what you want). What does the last question (in bold) mean? To prevent what from changing from what?
Feb 10 '07 #22
muhes
77
Thanks NeoPa for your response.

I have been working with Rabbit to design a form. Most recently, he informed me that I could place a text box (or anything else) in the header of a form with a continuous view so that I don't have repetition of unnecessary fields (these fields have the same data for each record in the form for any one budget). I placed three text boxes in the header, one for project start date, one for # of years ( both of which are fields in TblEntry), and a third field with the code provided to calculate the project end date.

Each record represents a person in the budget, and each has a distinct employee category which influences how their salary is calculated. The problem is that after I have entered all of the data for the first person, when I tab down to the next record, the date fields are blank. My question is is there a way to freeze or copy the data in a given field so that it doesn't change.


Thanks,

James
Feb 10 '07 #23
NeoPa
32,185 Expert Mod 16PB
To prevent what from changing from what?
So I guess the answer is to prevent the calculated control [Project End] from changing from the previously calculated value.
Please confirm this is correct.

As the calculation is performed on the current record of the form, a new record doesn't have the data to work with so has to resolve to blank. One way to get around this (assuming I understand your previous reply correctly) is to take away the Control Source of the [Project End] control and set it, when required, in the OnCurrent event procedure.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     If (Me![Project Start] & Me![# of Years]) > "" Then _
  3.         Me![Project End] = DateAdd("yyyy", _
  4.                                    Me![# of Years], _
  5.                                    Me![Project Start])
  6. End Sub
Feb 10 '07 #24
muhes
77
Thank you NeoPa for your response.

Yes, you are correct that I don't want the project end date to change;however, I also don't want the project start date and the # of years to change (these three fields are all part of the form header). I don't know whether what you suggested below will take that into account. The information in these fields would be valid for the entire project (for the entire budget). Basically, I don't want any of the fields used in the header of the form to change once data has been entered.

Thanks for your help.


So I guess the answer is to prevent the calculated control [Project End] from changing from the previously calculated value.
Please confirm this is correct.

As the calculation is performed on the current record of the form, a new record doesn't have the data to work with so has to resolve to blank. One way to get around this (assuming I understand your previous reply correctly) is to take away the Control Source of the [Project End] control and set it, when required, in the OnCurrent event procedure.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     If (Me![Project Start] & Me![# of Years]) > "" Then _
  3.         Me![Project End] = DateAdd("yyyy", _
  4.                                    Me![# of Years], _
  5.                                    Me![Project Start])
  6. End Sub
Feb 11 '07 #25
NeoPa
32,185 Expert Mod 16PB
Not exactly.
The controls you mention are bound controls so when you select the New record, there is nothing there to display. It sounds as if you may need a form with a sub-form rather than a single form using the Header and/or trailer.
Feb 11 '07 #26
muhes
77
Thank you again for your response.

I have redesigned both my form and tables slightly. I took the fields from TblEntry for "Project Start Date" and "# of years", and I deleted them. I then created a new table called TblDate with those fields and also "Budget ID", and "Date ID" which is the key for this table.

I then created a new form, and added all the information that was previously in the header of FrmEntry. I also added a command button that opens the FrmEntry. However, I still want to display the date information and # of years in the header of the FrmEntry. What do I need to do? Does what I have done solve my problem?

Thanks.
Feb 12 '07 #27
NeoPa
32,185 Expert Mod 16PB
As I'm just babysitting this thread until Rabbit gets back I've focused on one narrow area only. I am not in a position to make judgements that rely on a full understanding of your problem. It sounds as if you may well be on the right track but I'll leave any progression from here to Rabbit.
Feb 12 '07 #28
muhes
77
As I'm just babysitting this thread until Rabbit gets back I've focused on one narrow area only. I am not in a position to make judgements that rely on a full understanding of your problem. It sounds as if you may well be on the right track but I'll leave any progression from here to Rabbit.

Ok, thank you for your help.
Feb 12 '07 #29
Rabbit
12,516 Expert Mod 8TB
Just bumping to the end of my list. I go from oldest to most recent but want to tackle this one last.
Feb 13 '07 #30
Rabbit
12,516 Expert Mod 8TB
You're on the right track. I was going to suggest creating a different table to store the information for each budget seperate from the employees in each budget so that you don't end up storing the same information multiple times.

What you want to do is set up a main form that is linked to your Budget table which will display your Start/End Date, # of years, and so on. Then you want to set up a Subform that will be the continous form that displays your employee data.
Feb 13 '07 #31
muhes
77
You're on the right track. I was going to suggest creating a different table to store the information for each budget seperate from the employees in each budget so that you don't end up storing the same information multiple times.

What you want to do is set up a main form that is linked to your Budget table which will display your Start/End Date, # of years, and so on. Then you want to set up a Subform that will be the continous form that displays your employee data.
Hi Rabbit,

I hope that you had a good break over your three day weekend.

I want to thank NeoPa for assisting me during the break.

At this point, I have done as you suggested. However, I noticed that when I look at TblEntry, I am not seeing any information under budget ID. However, I do have information for Date ID. I'm wondering if I need the field budget ID at all, since it seems that Date ID is essentially filling the slot.

I have tried to insert a picture of the tables, but I haven't been able to at this point. Is there a way to do this?

Thanks.
Feb 13 '07 #32
Rabbit
12,516 Expert Mod 8TB
Hi Rabbit,

I hope that you had a good break over your three day weekend.

I want to thank NeoPa for assisting me during the break.

At this point, I have done as you suggested. However, I noticed that when I look at TblEntry, I am not seeing any information under budget ID. However, I do have information for Date ID. I'm wondering if I need the field budget ID at all, since it seems that Date ID is essentially filling the slot.

I have tried to insert a picture of the tables, but I haven't been able to at this point. Is there a way to do this?

Thanks.
I'm not sure what you mean by not seeing any information under budget ID. There won't be any information in there unless you enter it. As for whether or not you need budget ID, if you know for a fact that Date ID will be different for every budget, i.e. there will never be 2 budgets started on the same date, then you can safely get rid of budget ID.

I'm not sure why you are trying to insert a picture of the tables.

With this new setup, you'll need a key to link the employee data to the budget data. Most likely budget ID or Date ID, whichever one you decide to go with.
Feb 13 '07 #33
muhes
77
I'm not sure what you mean by not seeing any information under budget ID. There won't be any information in there unless you enter it. As for whether or not you need budget ID, if you know for a fact that Date ID will be different for every budget, i.e. there will never be 2 budgets started on the same date, then you can safely get rid of budget ID.

I'm not sure why you are trying to insert a picture of the tables.

With this new setup, you'll need a key to link the employee data to the budget data. Most likely budget ID or Date ID, whichever one you decide to go with.

Hi Rabbit,

Thank you for your quick response. I wanted to paste a picture of the relationships that I created because that would show how my tables are currently related. I will write it out instead.

TblDate has a 1 to many relationship with TblEntry via the field "Date ID". TblPositionName has a 1 to many relationship with TblEntry via the field "Name ID". TblEmployeeID has a 1 to many relationship with TblPositionName.

Date ID is not a date, but rather a number which is autogenerated. My thinking is that since I created the new table, it has replaced the original purpose that I had for a budget ID.

I will try it, and see if it works.

I wanted to explore an idea that you mentioned about creating a way for the database when it opens to prompt the user for a database name, and to conduct a search for budgets with that existing name:

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.
Can you tell me a little bit more about that? Obviously, we have already solved some of the problems mentioned above, but I would like to know more about the last part mentioned.

Thanks again for your help.
Feb 13 '07 #34
Rabbit
12,516 Expert Mod 8TB
What it basically comes down to is a form with a text box and a control button. This form will be unbound. In the text box they will type a unique name for the new budget they are creating. They will then click a button. The button, using code, will check for a duplicate budget ID or date ID, or however you want to identify the budgets. If there is no duplicate, then it will insert a new budget record into your budget table. It will then open the continous form that will allow them to enter employee information. And will also autofill the budget ID of the employee table.
Feb 13 '07 #35
NeoPa
32,185 Expert Mod 16PB
I want to thank NeoPa for assisting me during the break.
I'm glad if I was able to help any James.
For now I'll leave you in the very capable hands of our newest (well soon-to-be actually) forum moderator.
This thread was, in no small way, responsible for bringing to my attention their excellent attitude, leading me to suggest them for the post in the first place.
Feb 13 '07 #36
Rabbit
12,516 Expert Mod 8TB
I'm glad if I was able to help any James.
For now I'll leave you in the very capable hands of our newest (well soon-to-be actually) forum moderator.
This thread was, in no small way, responsible for bringing to my attention their excellent attitude, leading me to suggest them for the post in the first place.
I kinda figured you had a hand in this!
Feb 13 '07 #37
NeoPa
32,185 Expert Mod 16PB
You have to take some responsibility yourself too you know ;)
Ooooh, your tag's gone Green. Now you can edit posts retrospectively and all that stuff :)
Feb 14 '07 #38
muhes
77
Hello Rabbit,

Congratulations, and as always thanks for your assistance.

I am going to start switching gears a little bit and start working on the queries. If you have the time, I would appreciate your continued support.

I am trying to write a querry that determines the number of months prior to the change in pay grade using the project start date and a given date. For example: the project start date is 12/1/2007, the change in date for a category 1 employee would be 7/1/2008. I want to determine how many months fall before and after this date, and even how many might fall after 7/1/2009 (given a different start date, of course).

I have created a criteria where employee category =1. Then I created a new expression in a new field:
Expand|Select|Wrap|Line Numbers
  1. Expr1: Month([Project Start Date])-Month(#7/1/2008#).
  2.  
This works to give me the month subtracted from the month (obviously), but I realized that what I need is a way to write [Project Start Date]- (any date) and have the answer displayed by month, so that it takes the year into consideration as well. Can you help me?

Thanks.
Feb 14 '07 #39
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. DateDiff Function
  2.  
  3. Returns a Variant (Long) specifying the number of time intervals between two specified dates.
  4.  
  5. Syntax
  6.  
  7. DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
  8.  
  9. The DateDiff function syntax has these named arguments:
  10.  
  11. Part Description 
  12. interval: Required. String expression that is the interval of time you use to calculate the difference between date1 and date2. 
  13. date1, date2 Required; Variant (Date). Two dates you want to use in the calculation. 
  14. firstdayofweek Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed. 
  15. firstweekofyear Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs. 
  16.  
  17.  
  18.  
  19. Settings
  20.  
  21. The interval argument has these settings:
  22.  
  23. Setting Description 
  24. yyyy Year 
  25. q Quarter 
  26. m Month 
  27. y Day of year 
  28. d Day 
  29. w Weekday 
  30. ww Week 
  31. h Hour 
  32. n Minute 
  33. s Second 
  34.  
  35.  
  36.  
  37. The firstdayofweek argument has these settings:
  38.  
  39. Constant Value Description 
  40. vbUseSystem 0 Use the NLS API setting. 
  41. vbSunday 1 Sunday (default) 
  42. vbMonday 2 Monday 
  43. vbTuesday 3 Tuesday 
  44. vbWednesday 4 Wednesday 
  45. vbThursday 5 Thursday 
  46. vbFriday 6 Friday 
  47. vbSaturday 7 Saturday 
  48.  
  49.  
  50.  
  51. Constant Value Description 
  52. vbUseSystem 0 Use the NLS API setting. 
  53. vbFirstJan1 1 Start with week in which January 1 occurs (default). 
  54. vbFirstFourDays 2 Start with the first week that has at least four days in the new year. 
  55. vbFirstFullWeek 3 Start with first full week of the year. 
  56.  
  57.  
  58.  
  59. Remarks
  60.  
  61. You can use the DateDiff function to determine how many specified time intervals exist between two dates. For example, you might use DateDiff to calculate the number of days between two dates, or the number of weeks between today and the end of the year.
  62.  
  63. To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d"). When interval is Weekday ("w"), DateDiff returns the number of weeks between the two dates. If date1 falls on a Monday, DateDiff counts the number of Mondays until date2. It counts date2 but not date1. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.
  64.  
  65. If date1 refers to a later point in time than date2, the DateDiff function returns a negative number.
  66.  
  67. The firstdayofweek argument affects calculations that use the "w" and "ww" interval symbols.
  68.  
  69. If date1 or date2 is a date literal, the specified year becomes a permanent part of that date. However, if date1 or date2 is enclosed in double quotation marks (" "), and you omit the year, the current year is inserted in your code each time the date1 or date2 expression is evaluated. This makes it possible to write code that can be used in different years.
  70.  
  71. When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.
  72.  
  73. Note   For date1 and date2, if the Calendar property setting is Gregorian, the supplied date must be Gregorian. If the calendar is Hijri, the supplied date must be Hijri.
The preceding is an excerpt from the help file.
In your case, you would use DateDiff("m", [Project Start Date], [Second Date]).
This second date could be a field, could refer to a control on a Form, or could bring up an input box asking the user to enter a date. It all depends on how you want to do it.
Feb 14 '07 #40
muhes
77
Hello Rabbit,

Thank you for the information that you provided about the DateDiff function. I think that that is what I need. However, I am still missing something. I spent the last week trying to figure out how to make the function fit my needs, but unfortunately I don't have a solid grip yet. I already have a table in place for Employee Category, so what I tried to do was add a Change Dates field since these 'change dates' don't change. For example, a category 1 employee might have a change date of July 1st. So I created a field for "Change Date Current", "Change Date Future", and "Change Date Further Future" (in this case July 1st of 2006, 2007, & 2008 respectively). Then I used the DateDiff expression to create a querry for each field. For example:
Expand|Select|Wrap|Line Numbers
  1. Expr1: DateDiff("m",[Project Start Date],[Change Date Current])
  2.  
I used the Project start Date 12/1/2007. When I run the querry, I get -5, 7, 19 as the solution. To some extent this is correct; there would be no months at the current rate, seven months at the future rate, but there would obviously only be five months at the furthest future rate. I can't quite understand how to get the expression to work. Looking at the numbers I can see that the answers reflect the 12 month difference between each change date, which isn't quite what I want.

To further complicate matters. I also need to separate the answers into summer months and accademic months for some employee categories with summer being considered to start June 1st and end August 31st.

I am really trying to work these things out for myself as was requested of me, and I am not trying to abuse the board, but I would appreciate some help. What takes me hours (if not days) to figure out can be clarified in a matter of minutes.

I am always grateful for your help.

Thanks.
Feb 20 '07 #41
Rabbit
12,516 Expert Mod 8TB
Hello Rabbit,

Thank you for the information that you provided about the DateDiff function. I think that that is what I need. However, I am still missing something. I spent the last week trying to figure out how to make the function fit my needs, but unfortunately I don't have a solid grip yet. I already have a table in place for Employee Category, so what I tried to do was add a Change Dates field since these 'change dates' don't change. For example, a category 1 employee might have a change date of July 1st. So I created a field for "Change Date Current", "Change Date Future", and "Change Date Further Future" (in this case July 1st of 2006, 2007, & 2008 respectively). Then I used the DateDiff expression to create a querry for each field. For example:
Expand|Select|Wrap|Line Numbers
  1. Expr1: DateDiff("m",[Project Start Date],[Change Date Current])
  2.  
I used the Project start Date 12/1/2007. When I run the querry, I get -5, 7, 19 as the solution. To some extent this is correct; there would be no months at the current rate, seven months at the future rate, but there would obviously only be five months at the furthest future rate. I can't quite understand how to get the expression to work. Looking at the numbers I can see that the answers reflect the 12 month difference between each change date, which isn't quite what I want.

To further complicate matters. I also need to separate the answers into summer months and accademic months for some employee categories with summer being considered to start June 1st and end August 31st.

I am really trying to work these things out for myself as was requested of me, and I am not trying to abuse the board, but I would appreciate some help. What takes me hours (if not days) to figure out can be clarified in a matter of minutes.

I am always grateful for your help.

Thanks.
Sorry, I'm having a little bit of trouble interpreting your questions.

1) Why do you need to store the other change dates at all? It should be enough to store the change month and change day.

2) If you're trying to find the amount of months till the next change, how is the fact that the results are 12 month intervals apart the wrong answer?

3) Why do you need to seperate the answers into summer and academic months?

In the end, I'm unsure of exactly what result you're looking for.
Feb 21 '07 #42
muhes
77
Hello Rabbit,

Thank you as always for your quick response.

I will try to clarify what I am asking. Budgets are broken down into annual intervals. However, those intervals do not always match up with the financial year. For example the current financial year for a category one employee might run from July 1st 2006 to June 30th 2007. A budget that had a start date of December 1st 2007 would cross into the next financial year. (It would run from December 1st 2007 until June 30th 2007 or seven months in the current financial year, and from July 1st until November 30th, or five months in the future financial year.

As you can see, what I need to determine is how many months of the budget year fall into the current, future, or further future financial year. Since each budget year is always twelve months it is impossible for it to fall in all three catgories simultaneously.

The reason that I need to know which months are summer is because certain employee categories are paid differently in the summer. For this reason my form has a field for "# of months summer", "and % of effort summer".

I stored the additional change dates because I thought that I would need the year to distinguish between current, future, and further future. There is probably a way to write a querry and just add one year to the "Change Date" field, but I didn't know it.

The end desire is to develop an expression that uses the start date and determines how many months are in the AY year and Summer, and for each category how many months are in each financial year. Then I would have a separate expression which determines the monthly salary for the current, future, and further future. (For example, a category one employee who made $50,000 annually might have a current monthly amount of $5,556; a future monthly amount of $5,744; and a further future monthly amount of $5,940). Finally, I would multiply the monthly amount for each possible financial year by the actual number of months as determined above for both AY and summer, and multiply that by the percent of effort for both summer and AY. The sums would then be added together. (In this case, with a projected start date of December 1st 2007, there would be 0 current months, 7 future months {6 AY: December 2007, January, February, March, April, and May 2008, 1 summer: June 2008}, and 5 further future months {2 summer: July, August 2008, and 3 AY: September, October, November 2008}. Assuming a 50% effort for AY, and 100% effort for summer, the final calculations would be (6 x $5,744) x 50% + (5744 x 1) x 100% + (3 x 5940) x 50% +(2 x 5940) x 100% = $43,766

You probably don't really need to know the full equation, but I thought that it would help to clarify what my question is about how to determine the number of months for each financial period. Sorry about the long post.

Thanks as always.

Sorry, I'm having a little bit of trouble interpreting your questions.

1) Why do you need to store the other change dates at all? It should be enough to store the change month and change day.

2) If you're trying to find the amount of months till the next change, how is the fact that the results are 12 month intervals apart the wrong answer?

3) Why do you need to seperate the answers into summer and academic months?

In the end, I'm unsure of exactly what result you're looking for.
Feb 21 '07 #43
Rabbit
12,516 Expert Mod 8TB
Examples always help.

I assume that with this calculation you are trying to figure out how much money the employee will make in one year starting from the budget start date. Is this correct?

In your example, why are there 0 current months?
Feb 21 '07 #44
muhes
77
Examples always help.

I assume that with this calculation you are trying to figure out how much money the employee will make in one year starting from the budget start date. Is this correct?

In your example, why are there 0 current months?
Hello Rabbit,

Yes, that is the idea. Actually, it is how much an agency would pay for that time period, but basically yes.

In the example there are 0 current months because the project starts after 7/1/07. Current months represent the current financial year, which in this case was from 7/1/06 to 6/30/07.

Thanks again.
Feb 21 '07 #45
Rabbit
12,516 Expert Mod 8TB
I'll have to think about this.
I'll get back to you when I have an algorithm.

One question: What do you do if the Start date falls before the today's date?
Feb 22 '07 #46
muhes
77
I'll have to think about this.
I'll get back to you when I have an algorithm.

One question: What do you do if the Start date falls before the today's date?
Hi Rabbit,

The only time that happens is when someone is designing a budget to fit an award that was already made. It is fairly rare, but I suppose that it can happen. In our current system, it isn't really a problem since it is all done by hand, so to speak. I did actually design an excel sheet that partly meets our needs, but in truth it isn't versatile enough. I suppose that if I had to do without for that particular instance it wouldn't be terrible.

Thanks again for your help.
Feb 22 '07 #47
Rabbit
12,516 Expert Mod 8TB
The reason I ask is how is salary calculated if say the award was for the fiscal year prior to the current fiscal year?
Feb 22 '07 #48
muhes
77
Generally in that case we use the current salary, or we use an arbitrary number that fits into the total final amount.

Thanks for the extra effort that you are putting into this for me. I really appreciate it.
Feb 22 '07 #49
Rabbit
12,516 Expert Mod 8TB
Generally in that case we use the current salary, or we use an arbitrary number that fits into the total final amount.

Thanks for the extra effort that you are putting into this for me. I really appreciate it.
Not a problem.

I've been working off and on all day trying to get it to work in a Query Design View and while I think I have a working algorithm, it's much too complicated and convoluted to implement.

So I've decided to go the easier route, easier for me, harder for you, and do it through code. It'll be harder for you to implement because I don't think you've dealt with VB Code yet but you will have to eventually so we can start here.

What I need from you is the metadata from your tables, in case you've made any changes.
Feb 22 '07 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by DebbieG | last post: by
reply views Thread by gavo | last post: by
2 posts views Thread by RICHARD BROMBERG | last post: by
4 posts views Thread by vg-mail | last post: by
2 posts views Thread by campos | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.