I am trying to creating a database for attendance issues for employees. I have the following text boxes on my form:
First Date Absent, Last Date Absent, Reason for time off, Disiplinary action, point value, comments
Our attendance policy requires certain disciplinary action be taken when the point values total a certain amount in a specified amount of time.
With all that said, I am wondering if I can create a text box on this form to calculate the point value within a 30 day period and a second text box to calculate the point value within a 12 month period. I can sum the total points, but I am unsure how to sum the points within the date ranges needed. Any ideas?
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error; and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
Dear All, i have a very simple database with only one table.
The table has three values [Start], [end] and [Total].
The [start] & [end] fileds are numerical.
I am trying to get it so that i can enter the values onto a form, the two values in [start] and [end] need to be added together to give the [total] value.
I can get it to do it on the form but i don't know how i can get the record in the table to be updated.
Does anyone know if this can be done and if so could you advise me where i'm going wrong.
I have a problem with main form and two subforms in it. Both subforms are in "continuos" mode, and each of them has field in footer, where it calculates total sum for all field that are in it.
Then there is main sum below both subforms (all this is in detail section of form) and it calculates total for both sums of each subform.
Whenever i fill values in first subform and in second subform, i have results everywhere, but if i fill values in first subform but leave second subform empty, the "main sum" below both subforms is empty also....
The control source for main sum field is "=Forms!frmMainForm!frmSubform1!Text1 + Forms!frmMainForm!frmSubform2!Text2" where Text1 is "total sum" in first subform and Text2 is "total sum" in second subform...
I set default value = 0 for all fields in both subforms, but it won't help....still shows empty fields in that subform, which isn't filled....Actually when i open form for the first time, it shows "0" values in all empty fields and then immediately clears them and shows empty fields...
I have created a form in Access to enter data into a Table. But one of the firelds I need to enter into the Table is calculated from some of the data being entered in the form. I currently use an Update Query to go back and do the calculation and then update the appropriate field. What I would like to do is to do the calculation and update the calculated field while the user is entering the data. (Boy I hope this is making sense!)
If it helps understand what I am trying to do here is an example: The user is entering data for a job we produced. Some of that data is NumberOfPages, PaperSize and JobType. But I also need to take those three pieces of info and calculate the number of square inches. Currently I run a function I wrote via an Update Query that updates the SquareInches field in the table. I was wondering if there was some way to do the calculation on the fly and enter the calculated value into the table while all the other data is being entered. I tried writing a little code to attach to one of the field's OnExit event but I seem to be having a problem getting the values from the fields I have already entered.
If anyone has any suggestions I would sure appreciate hearing them. Thanks!
performing calculations in a form in an Access 2007 database.The form is used to generate invoices and sales receipts. At the bottom of the form there are 3 text boxes: "Sub-total" (Text128), "VAT" (Text130) and "Total" (Text132). Each text box is bound to the Control Sources "Sub-Total", "VAT" and "Total" respectively in a table called "Orders".
I would like the "VAT" text box to automatically calculate the VAT on an order and enter it into the VAT field in the table "Orders". To do this I have tried using the formula:
Code: =[Text128]*0.2
I would also like the "Total" text box to automatically calculate the total cost of the order and enter it into the Total field in the table "Orders". To do this I have tried using the formula:
Code: =[Text128]+[Text130]
I know that these calculations are correct as they work in an unbound text box however I cannot get this to work with bound text boxes.Is it actually possible to do this or do I need to use another method to perform these calculations?
What I am trying to do is hard to explain but I am going to try and explain it as best as possible. I have a form that I enter football stats into for my FFL league. For the sake of simplicity I am going to focus on the completions(comp) field. I want to add a text box/label to the bottom of the page that will calculate bonuses; if comp<25=0 if comp<50=1 else 2. I also want to have the CompBonus field then be entered into a new table so I can track bonuses for each player as well.
I hope you can help - I have two simple problems and one slightly more complicated problem (bearing in mind I'm quite an Access Novice!)
Problem 1. I have set up a simple form for a Timehsheet where the user can select a project and then enter hours worked in 7 text boxes for Mon-Sun. Then I have create a Total Text box at the end of the row. I need the Total box to Sum up the hours from the 7 boxes dynamically. How does one do this?
Problem 2. How do I then ensure this calculated field populated the field hoursID in tblHours? (I am able to do this with a normal field, but with a calculated field, doesn't the formula go into the Controlsource field??)
Problem 3. I plan to put this form as a sub-form into a main form 4-5 times so that a user can select 4-5 projects to enter hours against. I would like to have a SUBMIT button on the main form, so that all the calculated hours and selected projects populate the respective tables ONLY after this button is pressed.
I am currently working on a form that calculates the sum of counts and charges for various categories. The form sums the number of items in each category, then the sum of the charges. Where I am stuck is trying to break out the "per unit" charge for each category. The calculation works fine (sum of charges/sum of items) when there is a number greater than 0, but if the count or charges are 0, the fields display errors. I attempted to compensate for this by setting the text box value on the form to iif(iserror(sum of charges/sum of items),0,(sum of charges/sum of items), but it still shows the error!. I could probably write nested iif statements to evaluate the values of each sum, but you would think this would be simpler. Here is a sample of the actual ControlSource field on one of the text boxes:
I have a dashboard that opens with my main form and it contains many textboxes with Dcounts, Dlookups and Dsums in it. I am hoping that I can put these textboxes on a separate form that is hidden unless a command button is clicked to make it visible.
I know how to do this, but my question is whether or not all of those queries will run whether or not the form is visible. Obviously if the Dsums etc.. are going to run anyway, then I won't bother.
I've got a data entry form that is used to enter incoming waste consignment data. (I've attached a screen grab of the form) Each screen represents one record and the data is recorded in a table called "IncomingWaste". The top of the form (light blue) refers to the customer/producer of the waste. Most of that detail is held in another table on the db. The bottom (Dark Blue) part is used to record the consignment data. It gives the user a grid to enter the individual waste components within the consignment (choosing from Drop down menus for the various descriptive elements) Most crucially, the weight of each component is recorded.
I have a calculated field at the bottom which gives a Total weight for that consignment. It simply adds the weight fields together and delivers a total in the box at the bottom of the screen which displays the total weight in that consignment using "Nz([weight1])+Nz([weight2)]+ ...etc - which works fine on the form. However, I can't seem to get this calculated total recorded and appended to each record in the table and I assume it must be re-calculated each time the screen loads (?)
The main issues is.... I need to create a report which adds the total weights from each customer over a monthly/quarterly period and I simply cannot seem to do it.
I have created the report which groups all the consignments from each customer and lists them in date order, but I can't identify or define the method I need to use to collect that Total Weight calculation from each consignment record and add them up to give a grand total for that customer for a particular period. I'm assuming if I can crack the first part and get the total recorded in the table, then I can call the value into the report.
I would like my table to show the result of a calculations (or a formula). I surmise that you can not have a column cell calculate data, however if I can do this in the Form View and on a Report - can it just SHOW on the Table so I do not have to keep switching back and forth to see the information.
I am new to Access and think the forum will be a big help to me. Thanks
All I'm after is, to sum in all records "where" reconcileDate (on the sub=form) = today in both the credit and debit fields and minus one from the other. I have a simple form and sub-form. On the sub-form I have a query based calculation that returns the following..."if ReconcileDate (on sub-form) = today, then show Debit value in yndebit" textbox..Likewise with the credit box, and all works perfectly fine.
However, I can't seem to sum these two boxes. Because the circled textboxes are query based, I've used the query as the control source (and not the text boxes) and all I get is an #ERROR? when placed in the form footer. I've tried to do this using the textboxes as the control source, and still nothing. I've tried to add a calculated control on the form itself, and still #ERROR? or #NAME? despite knowing exactly how to reference subforms within forms (this I've become quite an expert at as I simply use query design to make sure I have the correct path and control)anyway.
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
I have a form with a subform in a navigation menu. Something like:
Navigation Form NavigationSubform ParentForm (header data and some unbound calculation fields) Subform (Multiple lines tied to query)
I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).
My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.
Parent Form PF1=10 PF2=3 Subform QTY ADJ SF1 SF2 Line 1 5 .05 10.6 112.89 Line 2 8 .14 10.38 105.15 ... Line N SF1=PF1+(PF2/QTY) SF2=SF1*(SF1+ADJ)
So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.
I'm hoping this is a no brainer...I am a complete newbie and self taught so apologies for not knowing the correct techie terms!!
I have a db with products...
Each product has a price (to customer) and cost (to me the seller)
What I want to work out is the margin i.e. price minus cost divided by proce multiplied by 100 to give a margin in percentage terms.
The data is held in a table with a record per product with the price and cost..
I want to build in another fieled per record that calculates the margin...
Thanks very much
Jessica
(I would also like to be able to work out decreasing margins if I was to extend say a 10% or 20% discount to the customer but I guess I shoudl walk before I run!!)
Hi, I have a database and on one table I have dates stored for certain jobs. I was wondering if anyone knew how to automatically calculate an end date if the start date and duration is entered!
Ok I'm really new at using access so go easy on me. I am trying to write a database to keep 30 measurements of a part grouped by lot number and then calculate the standard deviation, mean and a few other calculations, however I am unsure about how to do the calculations in access.
I would like the database operator to be able to enter the data then print out a report per lot, with the entries and the calculated answers.
I'm just unsure where and how to do the calculations... any help or suggestions?
I'm trying to a create a report to list results for a running club based on series-to-date. To do this, I allocate points to the first 100 runners to cross the line in each race. The winner gets 100, second place 99, and so on with the 100th person getting 1.
In the database i have 1 table holding member details, then one table holding races. I also have a table linking the 2 called race_entry so i know what members have entered for which races. I have one other table called race_result that links the runner to a race and holds the position they finished in the race.
How could i use the data in the race results table to add up all the points they earned by looking at their finishing position in the races they run and then listing the points in order in a report.
I assumed it wouldnt be necessary to have a point field in the table as its deriveable.
This is more than likely simple for a lot of people out there but it has me stumped. I have a form that has a two combo boxes with dates in both ( so the user to pick from and to dates) now I am always going to be adding more dates as time progresses. I need the next form to add the info with the dates no mater what the user picks. For example I am going to (once a week) be adding new info to the table.The end user is going to pick 06/24 (to indicate the end of the week) in both forms or 06/24 in the from box and 07/01 in the to box. In all situations I need the next form to add the data together. Sorry for the long post but didn't know how to state it any simpler.
I am a beginner with Access and was wondering if someone would be so kind to give me some pointers
I have created a form where the user can select which dates they require a hotel for the evening. I was looking for some advice on how I can go about putting an area on the same form which calculates a sub price for the days they have selected. If I am not being clear then ask for more details.
I need to be able to use some functions available in excel in an access 2000 database. These include SUM and IF functions. If any one can help me figure out how to do this any help help wouold be greatly appreciated
