Form to perform calculation

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".

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

One other thing:

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

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.

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.

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?

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.

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.

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:

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

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.

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.

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

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

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.

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.

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.

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.

One other thing. I created a new text box for project end date, and I used the following statement tied to "Project Start":
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'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.

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?

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

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.

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

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.

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.

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.

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.

Just bumping to the end of my list. I go from oldest to most recent but want to tackle this one last.

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.

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.

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.

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.

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.

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.

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!

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 :)

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:
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.

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.

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:
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.

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.

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.

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?

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.

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?

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.

The reason I ask is how is salary calculated if say the award was for the fiscal year prior to the current fiscal year?

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.

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.