Calculated and send to totals. Question

Hello I working in a database in MS Access 2003 to know the Employee effort that dedicated to a project. I want when the user type in the subform the percent of dedication in the Field Effort:, select pending or current and click yes in one of the 3 option academic,calendar or summer field automatically send to the respective Totals that are divided into current or pending. I think that i need use the boolean if or else but i not know how.Please download my project a see where is the solution to PendingTotals and Current Totals.
I think that the example for one of the six totals syntax is something like this:

if Status = Pending
and academic = Yes
Then Pending_Academic Total = Effort

This vary by Proyect title.
Can download in 4shared:
I hope this help
4shared.com/file/110839010/c0d432bb/database_effort.html]

I tried to download your project but the link did not work. I suspect that you are saving data unnecessarily. Normally, calculated values should not be saved. Save the raw data and perform calculations such as Sum or Average on a report. Are you saving the choices of 'Current', 'Pending', 'Summer', etc., and the Percentage?

i try to download again and is working. Just add http://www. to the url.
4shared.com/file/110839010/c0d432bb/database_effort.html

I did download your project. A revision is available at http://www.box.net/shared/1lzpfi3bqi. I modified the project structure to comply with database design principles: normalized data and no calculations take place in the forms and none are saved to tables. I created a report to do that. I can understand why you felt the need to save these calculations as they are based on employee salary and that value could change from project to project. (If it were to change during project period then that is another issue.) I met this situation by saving the salary in effect at the time of creating the work record into that record. This would appear to duplicate data but the alternative would be another table to document employee pay history. I believe this would make creating the report I designed very complex. If employee pay NEVER NEVER NEVER changes then this duplicate entry would not be needed and the calculation in the report could be based on the salary field in the Employees table. You may want to ‘pretty things up’ a bit, but believe everything is functional as is. I used AutoNumber fields to generate a unique identifier for each record. If you have some standardized ID data (such as SocSecNum or a project number) you prefer to use, then delete the AutoNumber field and replace with a text field with the same name. And on the forms to enter these values modify textboxes to be unlocked to allow input.
To answer your original question of saving the calculations to a table, if you still think you should. Don't think is practical with your original design of a subform showing all records. Would have to be a data entry form for one record. Then include a button on the data entry form and in that button’s Click event include code to calculate (and possibly to immediately close the form also). Make sure the form’s RecordSource query includes the table fields for the calc values and when the form closes the table will update. Something like:
Good Luck.

I thinks that the form that i create is the right form for was I want.
1.The Salary never change, is only part of the form i not need calculated nothing about salary.
So what I try to do?
i NEED TO sum the effort of the different project title for that employee (to know how effort free he have)clasificated in Pending or Current Labels and Academic,calendar or summer totals.
How i think that i can do that?
Using something like this in the different 6 textboxes:
sumif Status = pending
and academic = Yes
Then Pending_Academic Total = Effort:
Only i want to sum the effort that can be clasified as academic ,calendar or summer and send to the respective totals below Pending or Current Labels

The employee cant excess the 100 of effort in academic (10 months)if the Projects are current. The same in calendar(12 months).
A employee cant excess the 100 of effort in a project that are current and academic and the other is current and calendar.He can have 100 in academic and 100 in summer but not 100 academic and 50 percent in calendar if the two projects are current.

Hope this help .
any question send me a reply i and working 24/7
Again thanks for all June7 i post my problem in 3 forums and in this is where i receive a reply.
SORRY FOR MY ENGLISH. I am from Puerto Rico, my first language is spanish

Okay, my misunderstanding about salary. It is not part of the calculation you want to do. However, using the database as I modified, what you want should be possible with calculated fields in report query and in calculating textboxes on the report. With your original design, even if you have the correct calculation in form textboxes, you must have code that will save the result to the table because textboxes with a formula cannot be 'bound' to the source table. This is only one reason why database design principles do not advocate saving calculated results. I will look at it again next few days.

I have edited the modified project and replaced it in the same file share site (http://www.box.net/shared/1lzpfi3bqi). I think the form now presents the effort information in layout you described. This provides an analysis tool to review the effort records and determine where changes must be made. If you do not take advantage of the features of a relational database, might as well use Excel spreadsheet. Because that is what your original design basically is, one large table into which you want to save calculated results. Would probably even be easier in a spreadsheet. However, if you still want to continue the path of pain and suffering, following is the only means I can envision to make your original form work. Put a command button in the header or footer section of the Main form (because subform is in datasheet view, can’t put it there). Set Properties: Name 'btnSave' and Caption 'Save Calculations for Current Record' and Click event to '[Event Procedure]' and put code behind the Main form:Alternate Solution might be to create a DAO 'clone' recordset of the form's recordsource. This would require more testing on my part and at this point I don't think it is worthwhile. This is all I will be able to offer.

Thanks i going to check. This is the only forum where castellano is posting replys. the other 2 are closed and linking to this forum .

Thanks is working.

1.Where i enter the code that you give, in my form or in the form that you created? The calculation that i want in a button can be in my form? I see that the report do the sum but the fuction of the report can be in the form or a way to simplified all the form that you created to only one form.
Why you do not use relationship?
The report was be created in design or wizard mode?
Can you please recommend a book?

Sorry for my english.

Not sure why you are asking where to put code. My last post explains where it would go: "However, if you still want to continue the path of pain and suffering, following is the only means I can envision to make your original form work..." My modified version of your project does not need more coding. I would not recommend one form for the four I designed. But do what you want.

I did not get around to setting up relationships but you may do so. When I first downloaded your project I tried to delete a field in table but wouldn't let me because it said the field was in a relationship even after I eliminated all relationships. Had to create new project and import objects. My biggest project here at work does not depend on relationships because the number of tables that would be involved exceed the limit for a relationship structure. Code is used to control data integrity.

I do not use the wizards.

The only books I have on Access are for versions 2000 and 2002. And one has only a very short introduction to VBA programming. Suggest you explore book stores (Amazon.com is one) to view selection and then buy used or the latest edition of one you like. I bought a bunch of used VB programming books very cheap on-line. That way, chances were I would get at least one fairly good one and I did. And if one didn't have the answer maybe another would.

when I'am using the effort form sometimes i receive a error whe i enter the name on the personnel.What is the problem.


Message:
The current Field must match the join key '?' in the table that serves as the 'one'side of one-to-many relationship.Enter a record in the 'one' side table with desired key value, and them make the desired join key in the 'many-only' table.

Are you using the project as I modified? Did you add relationships? Did it work before that? I just added some relationships to my copy and data entry is fine. Suggest you back up and try the version without relationships and see if there is an error. If not, then you know you set up something wrong in the relationships.

Question 1
Answer: Yes
Question 2
Answer: No
Question 3
Answer: I populated the Add/Edit &Project Record, Add/Edit &Employee Record and the Effort &Report but when i use the Add/Edit &Work Record I receive a error saying the mesage above when i try to select the names of the Personnel created in the other form.I do not change nothing, I download again your project but is the same problem. Sometimes i select other name in the list and then select the name that i want and error dissapear.

I was able to recreate your problem and here is my assessment and solution. You must have had the Effort form already open when you added the Employee and/or Project records. Several ways to handle this.
Open only one form at a time.
Or
If all forms open, after adding new Employee or Project record be sure to leave that row to commit record to table. Then when you go to the Effort form, click the Refresh button. Don't remember where exactly it is on the 2003 toolbar but should be prominent.
Or
Write code behind the form to automatically refresh the comboboxes when you make the Effort form active. So won't matter if all forms are open. You will have to learn more about VBA programming or Macros to incorporate this kind of enhancement. The only form I installed any code behind was the Menu form.

Back to your question about setting relationships. Even without project level relationships there is some control on data integrity because of the linking on the autonumber key field. Cannot delete from Employee or Project tables records that have been referred to by the Effort table. Just to be sure, I removed project relationships and tried to delete records and Access would not allow it.

With the creation of a button on the effort Form. The error disapears but can be this the solution.


I are beginer with reports
I trying to create another report changing the order to display like a table with the status and category. Left Pending and in the right Current. My problem is that when i try the report i receive #Name? #Error. You know about this?
You say:
I removed project relationships and tried to delete records and Access would not allow it.
I say:
Is this a problem or ignore and continue working in the project that you created.
Sometimes i have problem undertanding english.
Thanks June7.