Form to perform calculation

[muhes]

Hello,

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

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

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

Thanks for your help.

James

[Rabbit]

Example of metadata:

Posting Table/Dataset MetaData

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

[muhes]

Example of metadata:

Posting Table/Dataset MetaData

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
MinMark; Numeric

Hello Rabbit,

I'm very grateful that you have put this much time into my project. You are correct in your supposition that I have little experience with VBA.

I'm not entirely sure what data you need from me, but I will start with the table names, fields, and relationships, and if I need to add additional information I will.

TblDate has the fields "Date ID", "Project Start Date", "# of years", "Budget ID #"

TblEmployeeType has the fields "Employee ID Number", "Employee Type", "Change Date Current", "Change Date Future", "Change Date Further Future", "Change Date Current Begin Summer", Change Date Current End Summer", Change Date Future Begin Summer", Change Date Future End Summer", "Change Date Further Future Begin Summer", "Change Date Further Future End Summer"
(Some of these fields are probably redundant)

TblEntry has the fields "Entry ID #", "Name ID #", "Budget ID #", "Name", "Salary", "AY Months", "AY Percentage", "Summer Months", "Summer Percentage", "Date ID"

TblPositionName has the fields "Name ID", "Employee ID #", "Position Name"

[muhes]

The relationships are:

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.

Thanks again for your help.

[NeoPa]

You're doing a fine job of working with Rabbit on this one.
It's clear you're making every effort not to be a burden and we appreciate that attitude :)
I would make one point a little clearer though, you need to post the table MetaData in the same format as Rabbit posted. If you reply to his post you can copy and paste the tags he uses and just fill in your data. Having reference material displayed as free text makes it quite hard to use.

BTW The PK & FK references are Primary Key & Foreign Key that help to show how the tables are linked together. The type of each field is also critical as the SQL syntax varies for different types.

[muhes]

Thanks for your feedback NeoPa,

I will try again. Though I may be using the wrong information for dates.

Posting Table/Dataset MetaData

Table Name=TblDate
Date ID; Autonumber; FK
Project Start Date; Numeric
# of years; Numeric
Budget ID; Numeric

Posting Table/Dataset MetaData

Table Name=TblEmployeeType
Employee ID Number; Autonumber; PK
Employee Type; String
Change Date Current; Numeric
Change Date Future; Numeric
Change Date Further Future; Numeric
Change Date Current Begin Summer; Numeric
Change Date Current End Summer; Numeric
Change Date Future Begin Summer; Numeric
Change Date Future End Summer; Numeric
Change Date Further Future Begin Summer; Numeric
Change Date Further Future End Summer; Numeric

Posting Table/Dataset MetaData

Table Name=TblEntry
Entry ID #; Autonumber; PK
Name ID #; Numeric; FK 
Budget ID #; Numeric
Name; String
Salary; Numeric
AY Months; Numeric
AY Percentage; Numeric
Summer Months; Numeric
Summer Percentage; Numeric
Date ID; Numeric; FK

Posting Table/Dataset MetaData

Table Name=TblPositionName
Name ID #; Autonumber; PK
Employee ID #; Numeric; FK
Position Name; String

[Rabbit]

I have some questions.

1) Do different employees have different change dates?

2) Is position name related to the employee category?

3) How will AY Months/Percentage and Summer Months/Percentage differ from person to person?

4) Is salary tied to the person or employee category or can it vary? What about the AY Months/Percentage? Are they tied to the person or employee category?

5) Anything else I should know?

[muhes]

I have some questions.

1) Do different employees have different change dates?

2) Is position name related to the employee category?

3) How will AY Months/Percentage and Summer Months/Percentage differ from person to person?

4) Is salary tied to the person or employee category or can it vary? What about the AY Months/Percentage? Are they tied to the person or employee category?

5) Anything else I should know?

Hi Rabbit,

Change dates are related to employee type. Employees can fall into different employee types for different projects. For example, Dr. D might be an employee type 1 for project A, but employee type 2 for project B. On project A, he would have a change date of July 1st, on Project B, he would have a change date of October 1st. (Note he could never be a type 1 and type 2 on the same project). On the form, a person selects from the TblPositionName, which is tied to the TblEmployeeType by the Employee ID #.

Employee name isn't as important as employee type, because all employee types are treated the same (they have the same change dates, and salary is determined the same, summer/AY months apply in the same manner).

For example, a query to find the salary for employee type one would have the criteria of 1, and would find salary for multiple position names. It would treat this type of employee the same: 9 month year, a breakout between summer and AY effort, a change date of July 1st etc...

A query to find the salary for employee type two would have the criteria of 2, and again would find the salary for multiple position names. Here there would be a 12 month year, a breakout between summer and AY effort, a change date of October 1st, an inflationary factor of 1.03 etc...

In answer to your question about salary, it really isn't tied to anything per se. Salary changes quickly, often from one budget to the next. What it is tied to is employee type in the sense that data is entered differently for different employee types. An employee type 4 would have salary entered in the form by monthly amount, so there is no need to take into account the nine month year. The reason for this is that data comes from an accounting system which displays data differently for different employee types.

There is one additional complicating factor for determining current, future, and further future months. I don't know if it will be relevant for what you are designing now or not. The system I have described so far to determine the number of current, future, and further future months broken down by summer and AY time, is based on the idea that an employee always works for the total number of months in a year, but when they work less then the full number of months there is a dilema. Suppose using our old example that an employee category one has a start date of December 1st 2007, and suppose that they still work 50% time AY, and 100% summer, but now they will only be working two months AY, and 1 month summer. The current system would determine that there will 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}, but now they are not working the full twelve months. Since we don't know the specific months they will work (they could choose to begin in December, or they could wait until summer) we always want to budget for the largest possible amount. In this example we would budget for the further future months, and since there are sufficient summer and AY months, the whole budget would be done at the further future rate. But as you can see this requires evaluating several things: 1) whether the months allotted are less than or equal to 9 AY/ 3 summer, 2) if less than, what the greatest combination possible is.

I don't know whether this info is relevant to what you are doing now or not. I believe that if you can help me to develop an expression that determines the number of current, future, and further future months broken down by AY and summer, then I can figure out how to create the appropriate evaluating factors. For the excel sheet I designed, I just set it up so that one cell evaluated whether AY/ Summer were full. If true, then the calculation was the number of months for each category multiplied by their respective percents of effort, multiplied by the monthly amounts. If false, then I evaluated to whether there were any further future months for AY/ summer. If true, I evaluated whether the months requested were greater then the months available, and I subtracted the months available from the total requested and took the remainder from the lower category.

I realize this might be more information then necessary, but I wanted to mention it just in case it was relevant to your design.

Again, I definitely appreciate your help.

[NeoPa]

Thanks for your feedback NeoPa,

I will try again. Though I may be using the wrong information for dates.

Good work there.
Unfortunately you may have gone astray due to my (Rabbit copied my example with the MetaData posting) original MetaData example missing out an example for dates (Foolish I know & I take full responsibility).
If you simply list all the fields in your data which are of Date/Time format, then I will update your earlier post for you to reflect the correct data.

My up-to-date example of the MetaData follows for information or for reuse later.

Table Name=tblStudent
 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[muhes]

Good work there.
Unfortunately you may have gone astray due to my (Rabbit copied my example with the MetaData posting) original MetaData example missing out an example for dates (Foolish I know & I take full responsibility).
If you simply list all the fields in your data which are of Date/Time format, then I will update your earlier post for you to reflect the correct data.

My up-to-date example of the MetaData follows for information or for reuse later.

Table Name=tblStudent
 Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Hello NeoPa,

Thank you for letting me know about this. I appreciate your help. Here are the updated tables:

Posting Table/Dataset MetaData

Table Name=TblDate
Date ID; Autonumber; FK
Project Start Date; Date/Time
# of years; Numeric
Budget ID; Numeric

Posting Table/Dataset MetaData

Table Name=TblEmployeeType
Employee ID Number; Autonumber; PK
Employee Type; String
Change Date Current; Date/Time
Change Date Future; Date/Time
Change Date Further Future; Date/Time
Change Date Current Begin Summer; Date/Time
Change Date Current End Summer; Date/Time
Change Date Future Begin Summer; Date/Time
Change Date Future End Summer; Date/Time
Change Date Further Future Begin Summer; Date/Time
Change Date Further Future End Summer; Date/Time

Posting Table/Dataset MetaData

Table Name=TblEntry
Entry ID #; Autonumber; PK
Name ID #; Numeric; FK 
Budget ID #; Numeric
Name; String
Salary; Numeric
AY Months; Numeric
AY Percentage; Numeric
Summer Months; Numeric
Summer Percentage; Numeric
Date ID; Numeric; FK

Posting Table/Dataset MetaData

Table Name=TblPositionName
Name ID #; Autonumber; PK
Employee ID #; Numeric; FK
Position Name; String

[Rabbit]

This won't be so simple as an expression.

From what you have told me, this is how your tables should be laid out. Let me know if I am wrong in any of my assumptions.

1) TblEmployees will have only one entry per employee.

2) TblEmployeeCategory will only have one entry per category.Each employee category will have their own Change Month, whether or not it is on an academic year, Acadmeic and Summer percentages and Inflation amount regardless of who's in that position.

3) TblBudget will have only one record per budget.

4) TblEntry will have one record per employee per budget. It will draw from all 3 of the other tables, each record will have it's own position name because it is not related to employee or employee category. i.e. Bob, Category 2 and Category 3 may have different position names and also Jim, Category 1 and Joe, Category 1 may have different position names. Salary can also vary among employees and within categories much like position names.

Let me know if all this is correct.

A few followup questions. Are academic percent and summer percent constant? i.e. Regardless of who it is. Academic percent will always be 50% and summer percent will always be 100%.

Also, it seems that this calculation is not based on fiscal year but rather on change date.

Summer months will always be 6,7,8 regardless of whether or not they work on an academic calendar right?

Table Name=TblBudget
Project Start Date; Date
# of years; Numeric
Budget ID; Numeric; PK
 
 Table Name=TblEntry
Entry ID #; Autonumber; PK
Name ID #; Numeric; FK 
Budget ID #; Numeric; FK
Monthly Salary; Numeric
Employee ID #; Numeric; FK
Position Name; String
 
 Table Name=TblEmployeeCategory
Employee ID Number; Autonumber; PK
Employee Type; String
Change Month; Numeric
Academic Year; Boolean
Summer Percent; Numeric
Academic Percent; Numeric
Inflation Amount; Numeric
 
 Table Name=TblEmployees
Name ID #; Autonumber; PK
Name; String

[muhes]

This won't be so simple as an expression.

From what you have told me, this is how your tables should be laid out. Let me know if I am wrong in any of my assumptions.

1) TblEmployees will have only one entry per employee.

2) TblEmployeeCategory will only have one entry per category.Each employee category will have their own Change Month, whether or not it is on an academic year, Acadmeic and Summer percentages and Inflation amount regardless of who's in that position.

3) TblBudget will have only one record per budget.

4) TblEntry will have one record per employee per budget. It will draw from all 3 of the other tables, each record will have it's own position name because it is not related to employee or employee category. i.e. Bob, Category 2 and Category 3 may have different position names and also Jim, Category 1 and Joe, Category 1 may have different position names. Salary can also vary among employees and within categories much like position names.

Let me know if all this is correct.

A few followup questions. Are academic percent and summer percent constant? i.e. Regardless of who it is. Academic percent will always be 50% and summer percent will always be 100%.

Also, it seems that this calculation is not based on fiscal year but rather on change date.

Summer months will always be 6,7,8 regardless of whether or not they work on an academic calendar right?

Table Name=TblBudget
Project Start Date; Date
# of years; Numeric
Budget ID; Numeric; PK
 
 Table Name=TblEntry
Entry ID #; Autonumber; PK
Name ID #; Numeric; FK 
Budget ID #; Numeric; FK
Monthly Salary; Numeric
Employee ID #; Numeric; FK
Position Name; String
 
 Table Name=TblEmployeeCategory
Employee ID Number; Autonumber; PK
Employee Type; String
Change Month; Numeric
Academic Year; Boolean
Summer Percent; Numeric
Academic Percent; Numeric
Inflation Amount; Numeric
 
 Table Name=TblEmployees
Name ID #; Autonumber; PK
Name; String

Hi Rabbit,

I'm sorry to hear that this will be more complicated then was originally thought. I do appreciate your effort.

1) In regards to your question about TblEmployees, I'm not quite clear on what you are asking. Is TblEmployees the same as TblPositionName? What do you mean one entry per employee? Currently, the form doesn't create any entries in this table, but it does refer to it for the drop down menu, which creates an entry in TblEntry under the field name ID #.

2) In regards to your question about TblEmployeeCategory, I'm also unclear. It is true that each employee type has its own change month. Employee type is also used for salaries in the way in which data is entered. (a type 1 has data entered by annual amount, a type 4 has data entered by month, a type 5 has data entered by hour). However, again there is no data created by the form for the fields in these tables.

3) yes, you are correct. TblBudget will only have one record per budget. The form does create data for this tbl, and each entry in the TblEntry corresponds to this data. In this case, it is Date ID which is a foreign key in TblEntry, or in your table design it looks like it is Budget ID.

4) Yes, Tbl Entry only has one record per employee per budget. That record set, if you could call it that, includes: name, salary, AY months, AY percent, Summer months, summer percent of effort (all of which are user entered), and is tied to the other tables through selecting the position name, which links it to the employee type. It is also tied to the TblBudget because each record set for every employee has the same Date ID (or budget ID on your tables).

The form is setup so that Project Start Date, # of years, and Project End Date are all part of the main form, and the subform contains everything from TblEntry.

With regards to AY and Summer Effort, and months, these change and there is no inherrent relationship to anything. An employee could work 1% time for AY months or 100%. This information, of necessity, is all user entered.

Yes, summer months are constant, and so are AY months. Summer is always, June, July, and August, and AY is always everything else. Some employees have no need to separate that amount because they work the same percent of effort regardless. There are two ways to handle that: require that every employee enters the same data, or make it so that employee types that don't differ in effort treat AY months/ percentage the same as the whole year. For the sake of simplicity, the first solution might be better.

I like that you broke out month and year for change date, but can you explain why the year is "boolean"? What will that represent?

Thanks again for your help. I know I say this every time, but I really do appreciate the effort, and I feel like you have taught me a lot.

[Rabbit]

Hi Rabbit,

I'm sorry to hear that this will be more complicated then was originally thought. I do appreciate your effort.

1) In regards to your question about TblEmployees, I'm not quite clear on what you are asking. Is TblEmployees the same as TblPositionName? What do you mean one entry per employee? Currently, the form doesn't create any entries in this table, but it does refer to it for the drop down menu, which creates an entry in TblEntry under the field name ID #.

2) In regards to your question about TblEmployeeCategory, I'm also unclear. It is true that each employee type has its own change month. Employee type is also used for salaries in the way in which data is entered. (a type 1 has data entered by annual amount, a type 4 has data entered by month, a type 5 has data entered by hour). However, again there is no data created by the form for the fields in these tables.

3) yes, you are correct. TblBudget will only have one record per budget. The form does create data for this tbl, and each entry in the TblEntry corresponds to this data. In this case, it is Date ID which is a foreign key in TblEntry, or in your table design it looks like it is Budget ID.

4) Yes, Tbl Entry only has one record per employee per budget. That record set, if you could call it that, includes: name, salary, AY months, AY percent, Summer months, summer percent of effort (all of which are user entered), and is tied to the other tables through selecting the position name, which links it to the employee type. It is also tied to the TblBudget because each record set for every employee has the same Date ID (or budget ID on your tables).

The form is setup so that Project Start Date, # of years, and Project End Date are all part of the main form, and the subform contains everything from TblEntry.

With regards to AY and Summer Effort, and months, these change and there is no inherrent relationship to anything. An employee could work 1% time for AY months or 100%. This information, of necessity, is all user entered.

Yes, summer months are constant, and so are AY months. Summer is always, June, July, and August, and AY is always everything else. Some employees have no need to separate that amount because they work the same percent of effort regardless. There are two ways to handle that: require that every employee enters the same data, or make it so that employee types that don't differ in effort treat AY months/ percentage the same as the whole year. For the sake of simplicity, the first solution might be better.

I like that you broke out month and year for change date, but can you explain why the year is "boolean"? What will that represent?

Thanks again for your help. I know I say this every time, but I really do appreciate the effort, and I feel like you have taught me a lot.

It's only as complicated as the calculation. The end result I am working towards is code that will create a table that has a field called Salary which will show how much money they need to be budgeted for.

Yes, TblEmployees is the same as TblPositionName. What I meant by one entry per employee is that even if one employee is in multiple budgets, they will only be listed in TblEmployees once.

As for TblEmployeeCat, when you go about calculating how much to budget, do you first convert everything to monthly salary? For an hourly worker you would assume 40 hour work week, 4 weeks a month for the purposes of calculating how much to budget for them. And for yearly salary, you divide by 12.

Academic Year is boolean to represent whether or not the employee will have different percentages for AY and Summer months. If true, then yes, if false, then 100% for both AY and Summer months. So basically, only the month field holds the change date.

Take a look again at my layout and let me know if anything is out of place.

Some followup questions.

1) What are the fields Summer months and AY months for? You said they're always the same months so there should be no need for them.

2) As for calculating the amount to be budgeted, it seems that change date is more important than the fiscal year. Because it is not the end of the fiscal year that denotes a raise in compensation but the change date.

[muhes]

It's only as complicated as the calculation. The end result I am working towards is code that will create a table that has a field called Salary which will show how much money they need to be budgeted for.

Yes, TblEmployees is the same as TblPositionName. What I meant by one entry per employee is that even if one employee is in multiple budgets, they will only be listed in TblEmployees once.

As for TblEmployeeCat, when you go about calculating how much to budget, do you first convert everything to monthly salary? For an hourly worker you would assume 40 hour work week, 4 weeks a month for the purposes of calculating how much to budget for them. And for yearly salary, you divide by 12.

Academic Year is boolean to represent whether or not the employee will have different percentages for AY and Summer months. If true, then yes, if false, then 100% for both AY and Summer months. So basically, only the month field holds the change date.

Take a look again at my layout and let me know if anything is out of place.

Some followup questions.

1) What are the fields Summer months and AY months for? You said they're always the same months so there should be no need for them.

2) As for calculating the amount to be budgeted, it seems that change date is more important than the fiscal year. Because it is not the end of the fiscal year that denotes a raise in compensation but the change date.

Hi Rabbit,

Thanks for your quick response.

I have one other consideration that might be important. The excel sheet that I designed works well to some extent, but one of the key limitations is that it makes it very difficult to trace how the answers were calculated. One thing that I hope to do with the access sheet is design a way to trace the calculations used, either a report that shows the calculations, or something else. If you are designing a table that jumps to the final answer, so to speak, I wonder if it will make this more difficult.

What I originally had in mind was a query that determines the number of months for each financial year broken down by summer and AY, for example if 12/1/2007 is the start date, assuming a type 1 employee, the query would return 0 current summer, 0 current AY months, 6 future AY, 1 future summer months, 3 further future AY, and 2 further future summer months. (These are the total amount of months available for a given year given this start date) Then I would design a separate query that evaluates how the months requested fit this schema. First this would evaluate whether the requested months for a given employee type are less then 9 AY months or 3 summer months. If this is false, then there is no difference between the months available and the months requested. If it is true for either one, then there is a need to further evaluate.

As an example, suppose that the employee will only be working for 7 AY months, and 2 summer months. As I mentioned before, we create budgets with the most flexibility, ie the largest reasonable amount. It is possible that this employee would begin the project immediately, we'll call it scenario 1, in which case he would work six future AY months and 1 further future month, and 1 future summer and 1 further future summer months, but what if he is delayed and isn't able to start until five months later in May, (something very common, call it the second scenario). If we budgeted for the first scenario, his expenses would be larger then the budgeted amount. The largest flexibility given this start date is to have 3 further future AY months, 2 further future summer months, and 2 future AY months (or to say it another way, this employee would start on May 1 2008, and would finish on November 30th 2008, the latest possible start date to work the full 7 months). We always budget for the second scenario.

So if the evaluation determines that the employee will not be working the full 9 AY/ 3 summer months, then it would examine whether there are any further future months available for both AY and summer. (For example, a hypothetical start date of July 2006, would not have any further future months available) If this is true, then it would evaluate whether the months requested are greater than the months available for this category. If true then it would use the further future months available, and subtract these from the requested AY months. If there were less months or equal months requested than available, then it would use the months requested. This process would continue until it reached current months.

There would be a separate query for the monthly salary, but that is fairly simple. It basically evaluates to the monthly amount for all employees for current future and further future. Say in this case that the employee was paid 75,000. Since this is a category one employee, it is a nine month salary so you would divide salary by 9, and the inflationary factor is 3.4%. Current months would be 75K/9 = $8,333, future months would be 75K/9 *1.034 = $8,617, and further future months would be 75K/9*1.034*1.034= $8,910.

So now all that remains is multiply the monthly salary by the # of months of each category and by the percent of effort. Suppose that there is a 10% effort for AY and a 25% effort for summer. So for scenario 1 (full time, 9 AY months and 3 Summer months) with a start date of 12-1-07 the calculation would be: (0 *$8,333*10%)+0*$8,333*25%)+(6*$8,617*10%)+(1*$8,61 7*25%)+(3*$8,910*10%)+(2*$8,910*25%). The first number in the calculation represents the month, (Please see the second paragraph of my response if you have questions about where those numbers came from.), the second number represents the monthly salary, and the third number represents the percent of effort. The calculation shown is what I would hope to design a report to show, except the zero months which were included to clarify that in this case no current months would be applicable.

The whole long sequence that I described above is basically how I designed my excel sheet. I really don't know if there is a better way to do this with Access, but this is what I have done so far. Unfortunately, as you can probably imagine, it is a very large and unwieldy.

So to respond to your questions: yes, part of the process does require that every type of employee eventually be represented by monthly salary. With regards to your question about summer and AY months, the fields are necessary because sometimes you can have less than the full amount of months available. What I meant about 6,7,8 and always being considered summer is that this is how summer and AY months are separted. With regards to your statement about the fiscal year, you are correct, but the change date represents the beginning of the new fiscal year. In other words, change date and fiscal year are the same.

Wow, this was a really long post. I do apologize for that, but it seemed that I wasn't making some elements clear, and I hope this helps to better illustrate what I was trying to say.

Thanks as always.

[Rabbit]

1) I was under the impression that change date can vary for each employee type and so would not always be the same as the start of the fiscal year.

2) You said you always budget for the second scenario? But I think you meant to say the first scenario, i.e. the largest amount.

3) Why do you budget past one fiscal year? I assume the fiscal budget is redone every year anyways.

4) In your example you budgeted for 9 months, I thought you always budgeted for 12 months?

5) I still don't understand what the [Summer Month] and [AY Month] fields are for. Could you provide a few records for me to look at? It might give me a better idea of what is being stored in those fields.

[muhes]

1) I was under the impression that change date can vary for each employee type and so would not always be the same as the start of the fiscal year.

2) You said you always budget for the second scenario? But I think you meant to say the first scenario, i.e. the largest amount.

3) Why do you budget past one fiscal year? I assume the fiscal budget is redone every year anyways.

4) In your example you budgeted for 9 months, I thought you always budgeted for 12 months?

5) I still don't understand what the [Summer Month] and [AY Month] fields are for. Could you provide a few records for me to look at? It might give me a better idea of what is being stored in those fields.

Hello Rabbit,

1) Essentially what we have is multiple fiscal years. I use the term change date to designate these multiple dates of the fiscal year. For example, employee type 1 has a fiscal year of July 1, employee type 2 has a fiscal year of Oct 1. I know it is a little bit weird but that is what we have.

2) The first scenario assumes an immediate start date, recall the project was scheduled to begin on December 1st, 2007. This would actually be less money then the second scenario because if it starts on May 1st, it will have fewer months at the lower rate, only two, before the higher rate becomes active because of the change in the fiscal year.

3) The budget can't be redone after the fiscal year. We put together budgets for projects, and they are accepted based on the amount we budget for (or rejected). If a budget is accepted, these are the funds that will be available for the entire duration of the project. It is true that we create a budget only for the first twelve months. (For subsequent years we use the first year's figure and multiply it by the inflationary factor). However, those twelve months will always overlay another fiscal year unless the project happens to start exactly on the fiscal year, July 1st in this case, and has only that type of employee.

4) I'm not quite sure which example I budgeted for nine months. I think that you are referring to the fact that I used 9 as the denominator for my calculations, but this is because this type of employee is paid on the nine month year. Is this what you were referring to? Or it could be that one of my examples shows the complications of what happens when employees aren't scheduled to work for the full year, ie why we have to budget for the second scenario and the largest amount. Just to clarify, employees aren't always budgeted for full time work both by percent of effort or # of months. An employee could be budgeted for 10% effort for 12 months, or 100% effort for 1 month.

5) I will try to explain summer and AY time another way. I mentioned before that some employees are paid on a 9 month basis. In actuality the funds provided during the AY time go to repay the institution for the loss of effort that the employee would normally spend at work. However during the summer these employees are not paid by the institution, and the money for summer time goes directly to them. For this reason, this type of employee often wants to work a larger percent of time during the summer months, and also for this reason summer time and AY time must be broken into two categories. Now suppose this person has agreed to commit two months to the project: 1 month at 25% time sometime during the AY year, and 1 month at 50% time during the summer. In terms of the budget, we need to know how much money this person could potentially be paid. During the AY time, the institution is going to be paying the person whatever their salary is, but it will potentially be more if it is after the change date, ie the beginning of a new financial year. For this reason they want to budget the appropriate amount. During the summer, this employee can expect to be paid an amount equal to what they are worth during the AY year. Again, assuming a Dec. 1, 2007 project start date, we could assume that the employee works the month of January, but if the person works the month of September, it would be at a higher rate. The same is true for summer; he might work starting in June, but if it is August it is a higher rate.
Does this explain things more clearly?

I would be happy to provide a record; how do I do that?

Many thanks.

[Rabbit]

So you're saying that scenario two is used whenever an employee is budgeted for less than one full year? And that for an employee budgeted for 12 months, scenario 1 and scenario 2 would be the same?

This would mean then that the AY Month and Summer Month fields hold how many months they are scheduled to work.

[muhes]

So you're saying that scenario two is used whenever an employee is budgeted for less than one full year? And that for an employee budgeted for 12 months, scenario 1 and scenario 2 would be the same?

This would mean then that the AY Month and Summer Month fields hold how many months they are scheduled to work.

Hello Rabbit,

Yes, I believe that is correct with one caveat; you could potentially have an employee who was scheduled to work for three months (if they were all summer), or nine months (if they were all AY), and you would have a situation where both scenario 1 and 2 are the same. In essence summer and AY have to be treated as two separate categories, but yes whenever either category or both are "full" there is no distinction between scenario 1 and 2.

Yes, that is what the fields indicate.

Thanks.

[Rabbit]

Okay, I think I have all I need to write the code, give me some time.

[muhes]

Okay, I think I have all I need to write the code, give me some time.

Thanks,

If there is anything else I can do, please let me know.

[Rabbit]

1) I have not tested this code.

2) Backup your database before you try this.

Use this table structure:

Table Name=TblDate
Project Start Date; Date
# of years; Numeric
Budget ID; Numeric; PK
 
 Table Name=TblEntry
Entry ID #; Autonumber; PK
Name ID #; Numeric; FK 
Budget ID #; Numeric; FK
Salary; Numeric
Employee ID #; Numeric; FK
Position Name; String
AY Months; Numeric
Summer Months; Numeric
AY Percent; Numeric
Summer Percent; Numeric
 
 Table Name=TblEmployeeCategory
Employee ID Number; Autonumber; PK
Employee Type; String
Change Month; Numeric
Inflation Amount; Numeric
 
 Table Name=TblEmployees
Name ID #; Autonumber; PK
Name; String

And this Query SQL:

SELECT TblEmployees.[Name], TblDate.[Project Start Date], TblEmployeeCategory.[Employee Type], & _
    TblEmployeeCategory.[Change Month], TblEmployeeCategory.[Inflation Amount], & _
    TblEntry.[Salary], TblEntry.[AY Months], TblEntry.[AY Percent], & _
    TblEntry.[Summer Months], TblEntry.[Summer Percent], & _
    CalcSalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Employee ID #], True) As Spread, & _
    CalcSalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Employee ID #]) As [Budgeted Salary]
FROM (((TblEntry INNER JOIN TblEmployees ON TblEntry.[Name ID #] = TblEmployees.[Name ID #]) & _
    INNER JOIN TblDate ON TblEntry.[Budget ID #] = TblDate.[Budget ID]) & _
    INNER JOIN TblEmployeeCategory ON TblEntry.[Employer ID #] = TblEmployeeCategory.[Employer ID Number]);

And this code in a normal module:

Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer, Optional GiveString As Boolean = False) As Variant
 
Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
Dim SalFactor, Salary1, Salary2, Salary3, AYP, SummerP As Double
 
CalcSalary = 0
AY1 = 0
AY2 = 0
AY3 = 0
S1 = 0
S2 = 0
S3 = 0
ChangeMonth = DLookup("[Change Month]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
SalFactor = DLookup("[Inflation Amount]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
AYMonths = DLookup("[AY Months]", "TblEntry", "[Employee ID Number] = " & EmpID)
SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Employee ID Number] = " & EmpID)
AYP = DLookup("[AY Percent]", "TblEntry", "[Employee ID Number] = " & EmpID)
SummerP = DLookup("[Summer Percent]", "TblEntry", "[Employee ID Number] = " & EmpID)
StartDate = DLookup("[Project Start Date]", "TblDate", "[BudgetID] = " & BudgetID)
EndDate = DateAdd("yyyy", 1, StartDate)
Salary1 = DLookup("Salary", "TblEntry", "[Employee ID Number] = " & EmpID)
Salary2 = Salary1 * SalFactor
Salary3 = Salary2 * SalFactor
 
If ChangeDate1 >= EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 >= EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until AYMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
        Case Else
            AYMonths = AYMonths - 1
            Select Case CurrentFY
                Case 1
                    AY1 = AY1 + 1
                    CalcSalary = CalcSalary + Salary1 * AYP
                Case 2
                    AY2 = AY2 + 1
                    CalcSalary = CalcSalary + Salary2 * AYP
                Case 3
                    AY3 = AY3 + 1
                    CalcSalary = CalcSalary + Salary3 * AYP
            End Select
    End Select
    DateAdd "m", -1, EndDate
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
EndDate = DateAdd("yyyy", 1, StartDate)
 
Do Until SummerMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
            SummerMonths = SummerMonths - 1
            Select Case CurrentFY
                Case 1
                    S1 = S1 + 1
                    CalcSalary = CalcSalary + Salary1 * SummerP
                Case 2
                    S2 = S2 + 1
                    CalcSalary = CalcSalary + Salary2 * SummerP
                Case 3
                    S3 = S3 + 1
                    CalcSalary = CalcSalary + Salary3 * SummerP
            End Select
        Case Else
    End Select
    DateAdd "m", -1, EndDate
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
If GiveString Then CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
 
End Function

[muhes]

1) I have not tested this code.

2) Backup your database before you try this.

Use this table structure:

Table Name=TblDate
Project Start Date; Date
# of years; Numeric
Budget ID; Numeric; PK
 
 Table Name=TblEntry
Entry ID #; Autonumber; PK
Name ID #; Numeric; FK 
Budget ID #; Numeric; FK
Salary; Numeric
Employee ID #; Numeric; FK
Position Name; String
AY Months; Numeric
Summer Months; Numeric
AY Percent; Numeric
Summer Percent; Numeric
 
 Table Name=TblEmployeeCategory
Employee ID Number; Autonumber; PK
Employee Type; String
Change Month; Numeric
Inflation Amount; Numeric
 
 Table Name=TblEmployees
Name ID #; Autonumber; PK
Name; String

And this Query SQL:

SELECT TblEmployees.[Name], TblDate.[Project Start Date], TblEmployeeCategory.[Employee Type], & _
    TblEmployeeCategory.[Change Month], TblEmployeeCategory.[Inflation Amount], & _
    TblEntry.[Salary], TblEntry.[AY Months], TblEntry.[AY Percent], & _
    TblEntry.[Summer Months], TblEntry.[Summer Percent], & _
    CalcSalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Employee ID #], True) As Spread, & _
    CalcSalary(TblEntry.[Budget ID #], TblEntry.[Entry ID #], TblEntry.[Employee ID #]) As [Budgeted Salary]
FROM (((TblEntry INNER JOIN TblEmployees ON TblEntry.[Name ID #] = TblEmployees.[Name ID #]) & _
    INNER JOIN TblDate ON TblEntry.[Budget ID #] = TblDate.[Budget ID]) & _
    INNER JOIN TblEmployeeCategory ON TblEntry.[Employer ID #] = TblEmployeeCategory.[Employer ID Number]);

And this code in a normal module:

Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer, Optional GiveString As Boolean = False) As Variant
 
Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
Dim SalFactor, Salary1, Salary2, Salary3, AYP, SummerP As Double
 
CalcSalary = 0
AY1 = 0
AY2 = 0
AY3 = 0
S1 = 0
S2 = 0
S3 = 0
ChangeMonth = DLookup("[Change Month]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(Date))
ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
SalFactor = DLookup("[Inflation Amount]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
AYMonths = DLookup("[AY Months]", "TblEntry", "[Employee ID Number] = " & EmpID)
SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Employee ID Number] = " & EmpID)
AYP = DLookup("[AY Percent]", "TblEntry", "[Employee ID Number] = " & EmpID)
SummerP = DLookup("[Summer Percent]", "TblEntry", "[Employee ID Number] = " & EmpID)
StartDate = DLookup("[Project Start Date]", "TblDate", "[BudgetID] = " & BudgetID)
EndDate = DateAdd("yyyy", 1, StartDate)
Salary1 = DLookup("Salary", "TblEntry", "[Employee ID Number] = " & EmpID)
Salary2 = Salary1 * SalFactor
Salary3 = Salary2 * SalFactor
 
If ChangeDate1 >= EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 >= EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until AYMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
        Case Else
            AYMonths = AYMonths - 1
            Select Case CurrentFY
                Case 1
                    AY1 = AY1 + 1
                    CalcSalary = CalcSalary + Salary1 * AYP
                Case 2
                    AY2 = AY2 + 1
                    CalcSalary = CalcSalary + Salary2 * AYP
                Case 3
                    AY3 = AY3 + 1
                    CalcSalary = CalcSalary + Salary3 * AYP
            End Select
    End Select
    DateAdd "m", -1, EndDate
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
EndDate = DateAdd("yyyy", 1, StartDate)
 
Do Until SummerMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
            SummerMonths = SummerMonths - 1
            Select Case CurrentFY
                Case 1
                    S1 = S1 + 1
                    CalcSalary = CalcSalary + Salary1 * SummerP
                Case 2
                    S2 = S2 + 1
                    CalcSalary = CalcSalary + Salary2 * SummerP
                Case 3
                    S3 = S3 + 1
                    CalcSalary = CalcSalary + Salary3 * SummerP
            End Select
        Case Else
    End Select
    DateAdd "m", -1, EndDate
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
If GiveString Then CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
 
End Function

Hi Rabbit,

Wow, this is incredible. I really want to thank you for the work you put into this; this was much more then I expected.

I decided to create a new database with tables that completely match the ones you provided; it seemed easier this way. There was one thing that seemed different that I wanted to ask about. How are TblEmployeeCategory, and TblEmployees connected? In my old tables I had the field Employee ID as a FK in TblEmployees. This was necessary so that I could use the drop down box on my form, and when I selected the position name, TblEntry had a marking for Employee Type. Do I have the same thing here? Can I design the form the same way I did before?

As for the different sections of code, can you tell me where these should be entered? Is this a querry, a report, or a module of some sort? I apologize, but I don't have a lot of experience with VBA.

A great many thanks for all of your work.

[Rabbit]

TblEmployeeCategory and TblEmployees are not related in any way. You should still be able to set up the combo box although some of the settings might change a bit.

From what I could tell, there should be no relation between the employee and their category anyways. Any employee can be any category as long as they're not more than one per budget, correct?

[muhes]

TblEmployeeCategory and TblEmployees are not related in any way. You should still be able to set up the combo box although some of the settings might change a bit.

From what I could tell, there should be no relation between the employee and their category anyways. Any employee can be any category as long as they're not more than one per budget, correct?

Hello Rabbit,

There are three fields that are important to the employee "name": the name of the employee (which includes the first and last name, or TBN if the exact name isn't known), the employee type (which is related to all of the information necessary to calculate monthly salary--change date, 9 month or full, inflationary factor,etc...), and then there is the employee role on the project or the position name (which is selected by the person doing the budget). Of these, employee name is the least important. It is only the name used for the budget. Employee type and position name, are necessarily related. You can have multiple position names that all relate to one employee type. Here is an example: Dr. D is the employee name,(which is typed into the field) he might be a Principal Investigator (the position name), which is a type 1 employee (the employee type). A PI is always a type 1 employee, as is an Academic Associate). Every position name has an employee type.

Position name is for the combo box.

Thanks again for your help.

[muhes]

Hello Rabbit,

There are three fields that are important to the employee "name": the name of the employee (which includes the first and last name, or TBN if the exact name isn't known), the employee type (which is related to all of the information necessary to calculate monthly salary--change date, 9 month or full, inflationary factor,etc...), and then there is the employee role on the project or the position name (which is selected by the person doing the budget). Of these, employee name is the least important. It is only the name used for the budget. Employee type and position name, are necessarily related. You can have multiple position names that all relate to one employee type. Here is an example: Dr. D is the employee name,(which is typed into the field) he might be a Principal Investigator (the position name), which is a type 1 employee (the employee type). A PI is always a type 1 employee, as is an Academic Associate). Every position name has an employee type.

Position name is for the combo box.

Thanks again for your help.

Hello Rabbit,

I just wanted to follow up about how to input the code that you wrote for me. It seems that it is written in a few separate parts that should possibly be put into different places?
Thanks.

[Rabbit]

If Position Name is tied to employee category then you should move it to that table.

As for the code, you paste all of it into a standard module, which is a non-form, non-report module. In the visual basic editor, click Insert > Module and paste all the code except the SQL in there.

[muhes]

If Position Name is tied to employee category then you should move it to that table.

As for the code, you paste all of it into a standard module, which is a non-form, non-report module. In the visual basic editor, click Insert > Module and paste all the code except the SQL in there.

Hello Rabbit,

I have done as you said, but I'm still not quite sure what to do, or for that matter what the module does. How do I access or run it, or whatever I need to do? What do I do with the SQL code? I apologize but I really don't much about this side of access.

Thanks

[Rabbit]

Hello Rabbit,

I have done as you said, but I'm still not quite sure what to do, or for that matter what the module does. How do I access or run it, or whatever I need to do? What do I do with the SQL code? I apologize but I really don't much about this side of access.

Thanks

Click Insert > Module and paste the code there. That's all you have to do for the code.

As for the SQL, make a query, right click the title bar, select SQL View and paste that in there replacing whatever was in there.

[muhes]

Hello Rabbit,

I entered the SQL query, but when I clicked run, I got an error message:

"Syntax Error (missing operator in query expression '&_ TblEmployeeCategory.[Change Month]'.

Then it highlights the first & in the code (the first line).

Any ideas?

Thanks.

[Rabbit]

I guess that doesn't work with SQL.. & _ was supposed to tell it that the next line is supposed to be part of the same line. So delete all the & _ and bring the next line up so that they're all on the same line.

In the end it will look like:

SELECT .........
FROM ..........;

There will only be two lines. I broke up the lines so it's easier to look at.

[muhes]

I guess that doesn't work with SQL.. & _ was supposed to tell it that the next line is supposed to be part of the same line. So delete all the & _ and bring the next line up so that they're all on the same line.

In the end it will look like:

SELECT .........
FROM ..........;

There will only be two lines. I broke up the lines so it's easier to look at.

Thanks Rabbit. But I'm still getting a message: "Undefined function 'CalcSalary' in expression"

[Rabbit]

Where did you paste the Public Function CalcSalary?

[muhes]

Where did you paste the Public Function CalcSalary?

I put that into the first module again, and now I am not getting the error message, but I am getting a parameter prompt to enter the TblEntry.Employer ID #, and TblEmployeeCategory.Employer ID Number.

When I enter the numbers 1 for both, the query runs, but it doesn't have any data just a lot of headings.

I will see if I can add some changes to the tables so that it functions in the same way my old tables and forms did so that by inputting a position name, it can recognize the employee type.

Please let me know if you have any additional ideas.

Thanks.

[muhes]

I put that into the first module again, and now I am not getting the error message, but I am getting a parameter prompt to enter the TblEntry.Employer ID #, and TblEmployeeCategory.Employer ID Number.

When I enter the numbers 1 for both, the query runs, but it doesn't have any data just a lot of headings.

I will see if I can add some changes to the tables so that it functions in the same way my old tables and forms did so that by inputting a position name, it can recognize the employee type.

Please let me know if you have any additional ideas.

Thanks.

I figured out one thing, the field name is "Employee" (instead of Employer ID Number). When I changed that, It brings up blank fields which still seems incomplete.

Thanks.

[Rabbit]

Okay, so I decided to recreate your database on my side and test it with data. After I fixed all the bugs, typos, and errors of logic, I got it to work the way I understand you want it to work.

So, using the same table structure I outlined before, here's the new SQL code:

SELECT TblEmployees.Name, TblEmployeeCategory.[Employee Type], TblEmployeeCategory.[Change Month], TblEmployeeCategory.[Inflation Amount], TblDate.[Project Start Date], TblEntry.Salary, TblEntry.[AY Months], TblEntry.[AY Percent], TblEntry.[Summer Months], TblEntry.[Summer Percent], CalcSalary(TblEntry.[Budget ID #],TblEntry.[Entry ID #],TblEntry.[Employee ID #],True) AS Spread, CalcSalary(TblEntry.[Budget ID #],TblEntry.[Entry ID #],TblEntry.[Employee ID #]) AS [Budgeted Salary]
FROM ((TblEntry INNER JOIN TblEmployees ON TblEntry.[Name ID #] = TblEmployees.[Name ID #]) INNER JOIN TblDate ON TblEntry.[Budget ID #] = TblDate.[Budget ID]) INNER JOIN TblEmployeeCategory ON TblEntry.[Employee ID #] = TblEmployeeCategory.[Employee ID Number];
 

And here's the new module code:

Public Function CalcSalary(BudgetID As Integer, MainID As Integer, EmpID As Integer, Optional GiveString As Boolean = False) As Variant
 
Dim i, AY1, AY2, AY3, S1, S2, S3 As Byte
Dim CurrentFY, ChangeMonth, AYMonths, SummerMonths As Byte
Dim ChangeDate1, ChangeDate2, ChangeDate3, EndDate, StartDate As Date
Dim SalFactor, Salary1, Salary2, Salary3, AYP, SummerP As Double
Dim First As Boolean
 
CalcSalary = 0
AY1 = 0
AY2 = 0
AY3 = 0
S1 = 0
S2 = 0
S3 = 0
SalFactor = DLookup("[Inflation Amount]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
AYMonths = DLookup("[AY Months]", "TblEntry", "[Entry ID #] = " & MainID)
SummerMonths = DLookup("[Summer Months]", "TblEntry", "[Entry ID #] = " & MainID)
AYP = DLookup("[AY Percent]", "TblEntry", "[Entry ID #] = " & MainID)
SummerP = DLookup("[Summer Percent]", "TblEntry", "[Entry ID #] = " & MainID)
StartDate = DLookup("[Project Start Date]", "TblDate", "[Budget ID] = " & BudgetID)
EndDate = DateAdd("yyyy", 1, StartDate)
Salary1 = DLookup("Salary", "TblEntry", "[Entry ID #] = " & MainID)
Salary2 = Salary1 * SalFactor
Salary3 = Salary2 * SalFactor
ChangeMonth = DLookup("[Change Month]", "TblEmployeeCategory", "[Employee ID Number] = " & EmpID)
If Month(StartDate) < ChangeMonth Then
    ChangeDate1 = CDate(ChangeMonth & "/1/" & (Year(StartDate) - 1))
Else
    ChangeDate1 = CDate(ChangeMonth & "/1/" & Year(StartDate))
End If
ChangeDate2 = DateAdd("yyyy", 1, ChangeDate1)
ChangeDate3 = DateAdd("yyyy", 1, ChangeDate2)
 
If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until AYMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
        Case Else
            AYMonths = AYMonths - 1
            Select Case CurrentFY
                Case 1
                    AY1 = AY1 + 1
                    CalcSalary = CalcSalary + Salary1 * AYP
                Case 2
                    AY2 = AY2 + 1
                    CalcSalary = CalcSalary + Salary2 * AYP
                Case 3
                    AY3 = AY3 + 1
                    CalcSalary = CalcSalary + Salary3 * AYP
            End Select
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
EndDate = DateAdd("yyyy", 1, StartDate)
If ChangeDate1 <= EndDate And EndDate < ChangeDate2 Then
    CurrentFY = 1
ElseIf ChangeDate2 <= EndDate And EndDate < ChangeDate3 Then
    CurrentFY = 2
Else
    CurrentFY = 3
End If
 
Do Until SummerMonths = 0
    Select Case Month(EndDate)
        Case 6, 7, 8
            SummerMonths = SummerMonths - 1
            Select Case CurrentFY
                Case 1
                    S1 = S1 + 1
                    CalcSalary = CalcSalary + Salary1 * SummerP
                Case 2
                    S2 = S2 + 1
                    CalcSalary = CalcSalary + Salary2 * SummerP
                Case 3
                    S3 = S3 + 1
                    CalcSalary = CalcSalary + Salary3 * SummerP
            End Select
        Case Else
    End Select
    EndDate = DateAdd("m", -1, EndDate)
    If Month(EndDate) = (ChangeMonth - 1) Then CurrentFY = CurrentFY - 1
Loop
 
If GiveString Then CalcSalary = AY1 & ", " & S1 & ", " & AY2 & ", " & S2 & ", " & AY3 & ", " & S3
 
End Function

All of this should work as long as your table structure matches exactly what I outlined in the earlier post (except that I moved the position name field to the TblEmployeeCategory table and I made Budget ID an Autonumber but none of this should affect the code).

So, once you get it working, put in some test data and let me know if those results match what you're looking for. If not then I'll have to change the way it calculates the salary.

But other than that I got it to work.

There are 2 calculated fields: Spread and [Budgeted Salary].
Spread is a coded string that tells you how many months fall in each AY and Summer FY. Budgeted Salary is how much you should budget for.

Spread is in the form: AY1,S1,AY2,S2,AY3,S3
So: 1,0,8,3,0,0
Means: 1 Academic Month at FY 1, 0 Summer Months at FY 1, 8 Academic Months at FY 2, 3 Summer Months at FY 2, and no months for the rest.

[NeoPa]

I figured out one thing, the field name is "Employee" (instead of Employer ID Number). When I changed that, It brings up blank fields which still seems incomplete.

Thanks.

Just to prove I'm still around but not to interfere too much :
I notice you refer to 'TblEntry.Employer ID #' and 'TblEmployeeCategory.Employer ID Number' in your posts and I wondered, were you aware that, in SQL (and Access generally), any field name which contains spaces (not generally recommended btw) must be contained within [] when referenced, so that it can tell where the name ends.
'TblEntry.Employer ID #' ==> TblEntry.[Employer ID #]
'TblEmployeeCategory.Employer ID Number' ==> TblEmployeeCategory.[Employer ID Number]
It's helpful to use this syntax in your posts too, as this is what people are familiar with reading. It just makes life that little bit easier.
This also goes for table names if anyone creates tables with spaces in the name.

[Rabbit]

I normally don't use spaces either. Only did so this time to match his original tables as closely as possible.

[NeoPa]

It would not even have occurred to me to direct that comment at you Rabbit. I'd be very surprised to hear you'd done anything of the sort :)

[muhes]

Thanks NeoPa for your information about the brackets.

Thanks to you as well Rabbit. Unfortunately, there is stills something I am missing. When I run the querry, I am still coming up with blank fields. Is it possible that I need to create some joinings that I haven't? I saw that the query seems to do this for me, but I'm not getting it to do what it should. I know that you said that you were able to successfully create the tables and get it to perform, but for whatever reason something I have done is different.The only thing I can think of is that the forms I am using to enter data changes things. Is there anything that I can do to troubleshoot?

Thanks.

[Rabbit]

Thanks NeoPa for your information about the brackets.

Thanks to you as well Rabbit. Unfortunately, there is stills something I am missing. When I run the querry, I am still coming up with blank fields. Is it possible that I need to create some joinings that I haven't? I saw that the query seems to do this for me, but I'm not getting it to do what it should. I know that you said that you were able to successfully create the tables and get it to perform, but for whatever reason something I have done is different.The only thing I can think of is that the forms I am using to enter data changes things. Is there anything that I can do to troubleshoot?

Thanks.

Double check your table structures to make sure it matches exactly what I have. Spaces and everything. And also make sure the data types are the same. Also, did you put data into the tables? I'll post my test data soon.

Forget the forms for now and just put the data directly into the tables. After you get the query working, then worry about the forms.

[Rabbit]

Here's the test data that I used.

TblDate
[Budget ID]    [Project Start Date]    [# of Years]
1        5/1/2007        2
2        8/1/2007        2
 
 TblEmployeeCategory
[Employee ID Number]    [Employee Type]    [Position Name]    [Change Month]    [Inflation Amount]
1            Cat1        RA1        5        1.1
2            Cat2        PI        10        1.1
 
 TblEmployees
[Name ID #]    [Name]
1        Andy
2        Bob
 
 TblEntry
[Entry ID #]    [Name ID #]    [Budget ID #]    [Employee ID #]    [Salary]    [AY Months]    [AY Percent]    [Summer Months]    [Summer Percent]
1        1        1        1        100.00        9        1        3        1
2        2        1        1        100.00        9        0.5        3        0.5
3        1        2        2        100.00        9        1        3        1
4        2        2        2        100.00        0        0        2        1
 

[muhes]

Here's the test data that I used.

TblDate
[Budget ID]    [Project Start Date]    [# of Years]
1        5/1/2007        2
2        8/1/2007        2
 
 TblEmployeeCategory
[Employee ID Number]    [Employee Type]    [Position Name]    [Change Month]    [Inflation Amount]
1            Cat1        RA1        5        1.1
2            Cat2        PI        10        1.1
 
 TblEmployees
[Name ID #]    [Name]
1        Andy
2        Bob
 
 TblEntry
[Entry ID #]    [Name ID #]    [Budget ID #]    [Employee ID #]    [Salary]    [AY Months]    [AY Percent]    [Summer Months]    [Summer Percent]
1        1        1        1        100.00        9        1        3        1
2        2        1        1        100.00        9        0.5        3        0.5
3        1        2        2        100.00        9        1        3        1
4        2        2        2        100.00        0        0        2        1
 

Hello Rabbit,

I have compared my tables to the tables that it appears you used. Here is what I noticed. In TblEntry [Salary] is a currency field for me, not numeric (don't know if there is a difference or not).

For my table, TblEmployees has the field [Employee ID Number]. This is necessary to define what Employee ID Number each Position Name Corresponds to.

Going along with this, [Position Name] can't be part of TblEmployeeCategory, at least not to have normalized tables, (from my interpretation of what you told me). [Employee ID Number] should be a unique number that identifies each [employee type]. If [Position Name] is part of the table, then this isn't true.
For example [Employee ID Number] 1 [Employee Type] Research [Position Name] Professional Research Assistant, but then [Employee ID Number] 2 [Employee Type] Research [Position Name] Research Associate.

How can I keep my tables normalized but still work within the programming you provided? It seems important to have the appropriate links, otherwise extraneous information would be required on my forms.

Thanks.

[Rabbit]

Hello Rabbit,

I have compared my tables to the tables that it appears you used. Here is what I noticed. In TblEntry [Salary] is a currency field for me, not numeric (don't know if there is a difference or not).

For my table, TblEmployees has the field [Employee ID Number]. This is necessary to define what Employee ID Number each Position Name Corresponds to.

Going along with this, [Position Name] can't be part of TblEmployeeCategory, at least not to have normalized tables, (from my interpretation of what you told me). [Employee ID Number] should be a unique number that identifies each [employee type]. If [Position Name] is part of the table, then this isn't true.
For example [Employee ID Number] 1 [Employee Type] Research [Position Name] Professional Research Assistant, but then [Employee ID Number] 2 [Employee Type] Research [Position Name] Research Associate.

How can I keep my tables normalized but still work within the programming you provided? It seems important to have the appropriate links, otherwise extraneous information would be required on my forms.

Thanks.

Using currency for salary is fine, it's still stored as a number. In fact, that's what I set my field to for salary in my test run.

It doesn't matter if your TblEmployees has an extra field, although I was under the impression that Position Name was not related to who the person is but to the employee category.

Either way the Position Name field has no bearing on the code. It could be removed even and it shouldn't affect the code. I just deleted it and the query and code still ran fine.

None of what you brought up should affect the code.

In the visual basic editor, click debug > compile and see if there are any errors.

[muhes]

Hello Rabbit,

I have tried to run the debugger, but I can't click compile. (When I drop down the menu, it isn't selectable) Any suggestions?

Thanks.

Using currency for salary is fine, it's still stored as a number. In fact, that's what I set my field to for salary in my test run.

It doesn't matter if your TblEmployees has an extra field, although I was under the impression that Position Name was not related to who the person is but to the employee category.

Either way the Position Name field has no bearing on the code. It could be removed even and it shouldn't affect the code. I just deleted it and the query and code still ran fine.

None of what you brought up should affect the code.

In the visual basic editor, click debug > compile and see if there are any errors.

[Rabbit]

[Rabbit]

The various ID Numbers in the table TblEntry show text but it's actually a number being stored. I used a lookup field. So don't worry that it's not showing numbers.

[NeoPa]

Hello Rabbit,

I have tried to run the debugger, but I can't click compile. (When I drop down the menu, it isn't selectable) Any suggestions?

Thanks.

This happens when Access thinks the project (database) is already fully compiled. This is rarely a problem.
On those rare occasions where it is a problem, simply :

1. Add, then remove, an extra space at the beginning of one of your lines of code.
2. Move the cursor off that line and onto another one.
3. The Compile Project option should now be available.

[muhes]

The various ID Numbers in the table TblEntry show text but it's actually a number being stored. I used a lookup field. So don't worry that it's not showing numbers.

Hello Rabbit,

Thanks for showing me the table. I was able to duplicate your information, but I can't get it to work if I enter the info from my form.

Unfortunately, I ran the same numbers through my excel program, and the months are coming out wrong. A start date of 5-1-2007, with a change date of the same month should produce:0 AY1 months, 0 current s1 months, 9 AY2 months (May, September, October, November, December, 2007 and January, February, March, and April 2008), 3 S2 months (June, July, and August 2007), 0 AY3 and 0 S3. In spread format: (0,0,9,3,0,0)

Also, is salary based on an annual amount, or monthly, or hourly?

[muhes]

This happens when Access thinks the project (database) is already fully compiled. This is rarely a problem.
On those rare occasions where it is a problem, simply :

1. Add, then remove, an extra space at the beginning of one of your lines of code.
2. Move the cursor off that line and onto another one.
3. The Compile Project option should now be available.

Thanks NeoPa. I will try that.

[Rabbit]

Hello Rabbit,

Thanks for showing me the table. I was able to duplicate your information, but I can't get it to work if I enter the info from my form.

Unfortunately, I ran the same numbers through my excel program, and the months are coming out wrong. A start date of 5-1-2007, with a change date of the same month should produce:0 AY1 months, 0 current s1 months, 9 AY2 months (May, September, October, November, December, 2007 and January, February, March, and April 2008), 3 S2 months (June, July, and August 2007), 0 AY3 and 0 S3. In spread format: (0,0,9,3,0,0)

Also, is salary based on an annual amount, or monthly, or hourly?

Salary is in monthly format.

You were supposed to duplicate everything except for the ID #s in TblEntry. As I mentioned earlier these are lookup fields bound to an invisible column.

I am confused about how you calculate salary. This is the smallest part of the problem as I just have to change the algorithm to calculate it the way you want it. The hardest part is to get it to run.

About the calculation. Why would it be the case that it falls under FY2?
With a change month of 5 and a Start Date of 5/1/2007, would not FY1 = 5/1/2007 to 5/1/2008? And FY2 = 5/1/2008 to 5/1/2009?

[muhes]

Salary is in monthly format.

You were supposed to duplicate everything except for the ID #s in TblEntry. As I mentioned earlier these are lookup fields bound to an invisible column.

I am confused about how you calculate salary. This is the smallest part of the problem as I just have to change the algorithm to calculate it the way you want it. The hardest part is to get it to run.

About the calculation. Why would it be the case that it falls under FY2?
With a change month of 5 and a Start Date of 5/1/2007, would not FY1 = 5/1/2007 to 5/1/2008? And FY2 = 5/1/2008 to 5/1/2009?

Salary is complicated because each employee type has a different way that it is listed. I really appreciate what you have put together, but I don't know if you design one single querry to account for all the complications related to salary. Is it possible that you can design something that will give me a querry that just determines the number of months for each financial year based on whether there is full-time or part-time? I'm just thinking that I will have to use a different query for each situation for things to work as they should.

The reason you can't have an FY1 for 5/1/2007 is because you can potentially have a budget that starts before that date. 5/1/2007 can't be FY1 until 5/1/2007.

Thanks again.