473,545 Members | 2,567 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Form to perform calculation

77 New Member
Hello,

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

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

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

Thanks for your help.

James
Feb 6 '07
133 11599
muhes
77 New Member
Thanks again for your response, Rabbit.

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

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

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

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

James
Feb 7 '07 #11
Rabbit
12,516 Recognized Expert Moderator MVP
Thanks again for your response, Rabbit.

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

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

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

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

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

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

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

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

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

This could be a possible way to organize the database.
Feb 7 '07 #12
MMcCarthy
14,534 Recognized Expert Moderator MVP
Not at all, the more specific your examples and details, the better we can gear a solution to your needs.

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

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

Then in the table, you would have 2 records for Dr. Jonas:
Expand|Select|Wrap|Line Numbers
  1. Dr. Jonas     Category1     BudgetA
  2. Dr. Jonas     Category3     BudgetB
If you think of this as a join table. You could have a main form based on Dr. Jonas and it could contain any single record information on Dr. Jonas.
Then you could have a subform based on this table which would show each category and budget record for Dr. Jonas.
Feb 8 '07 #13
NeoPa
32,564 Recognized Expert Moderator MVP
Just posting to register this thread in my list so I can keep an eye out.
Feb 8 '07 #14
muhes
77 New Member
Hello Rabbit,

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

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

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

Thanks
Feb 8 '07 #15
Rabbit
12,516 Recognized Expert Moderator MVP
Hello Rabbit,

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

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

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

Thanks
We'll start off with a Main Menu form.
In this form there will be a list box with two options: Create New Budget and Edit/View Budget. In the On Click event of the list box, there will be code to open a form depending on what they click.

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

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

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

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

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

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

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

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

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

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

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

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

Thanks as always for your help.

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

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

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

Thanks.
Feb 9 '07 #20

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

Similar topics

2
3896
by: Del | last post by:
Thanks in advance for any help. I have a database that was created in Access 2000. Several users have been upgraded to Access 2003. Since upgrading to 2003 we have noticed that some of the calculated fields are not being populated. The database is a samll invoicing database on the form and report we have columns call Unit Cost. This is the...
2
1863
by: jquest | last post by:
Hi Again; I am improving my database and have to overcome some original design mistakes. I have 3500 records in the table and I have had to insert new fields to track things I originally didn't forsee. The new fields in the table now show nulls where there is no data. When I run a query based on the table and including the field that...
2
2341
by: DebbieG | last post by:
I have no idea how to start with this one. I have a subform where records could look similar to: Infraction Date Points 01/01/2000 3 06/01/2002 1 Somehow, I've got to calculate the points the driver has as of the current date. For instance, in the above example:
0
1719
by: gavo | last post by:
Hi. using A2K; i have a form containing a continous subform. The question is, how can i call a calculation for one of the fields in the continous subform from the main form. At the moment i have a button on the main form that will call a public code containing the calculation i need, the problem i have is that it will only perform the...
2
1835
by: RICHARD BROMBERG | last post by:
I have a form with thirty text boxes. Each of them has an input mask of 99. In each text box the user enters a numeric score in the range from 0 to 99. As each text box loses focus I want to perform a calculation involving all the numbers (ok the string representation of the numbers) . It doesn't matter which text box loses focus, the...
4
3748
by: vg-mail | last post by:
Hello all, I have identical design for form and report but I am getting calculation error on form and everything is OK on report. The form and report are build up on SQL statement. The calculation is very simple. The calculation is done in an underling query if I can call it a query or I should call it a SQL statement. It looks like a query...
1
2309
by: David | last post by:
Hi, I have a continuous form with 'x' amount of records. 1 field on each record is a number, of which I have a field at the top of the form which just shows the running sum. If I enter a new record, after I loose focus of the number field, I perform a save record and then test the data with a calculation, but the problem I have is that...
2
2380
by: campos | last post by:
Hi all, I ran into a headache problem. I have a windows form with a progress bar on it. Then I new a thread to do calculation for a long time. I want the progress bar to show the calculation progress. So I use a shared variable in calculation thread to allow main thread read it periodically in order to show the progress. Quesion comes out....
24
8392
by: MichaelK | last post by:
Who knows how to prevent submitting a form on the press Enter button before all fields on the form are filled up. People just enter the first field hit Enter and it submits the form and doing validation, of course flushing with the bunch of messages because rest of the fields are empty. I remember I had some pages where wrote the code to go...
11
6462
by: rajeevs | last post by:
Hi All I have two issues to put forward. First is bookmarking / or highlighting a particular record in a form. The form is continuous and the records are from a query result. One of the record in that form will be always the last added record from the table. The form display the records in a sort order from the query. What i need is to...
0
7490
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7425
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7682
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7449
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6009
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5351
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3479
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3465
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1037
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.