Calculating Across Fields
Dec 20, 2007
Hi,
I am creating a form which has many fields which should have values in them. I need to know how to total all the fields across each field.
I have managed to do this(almost!) by creating a text box, and "on lost focus", putting the VBA:
TextBox = [SumOfConnection Bonus] + [sumofspice bonus] + [sumofvolume bonus]
(When I get this working I'll make a field in the table so there is no need for the text-box.)
This is basically, three types of commision that I want to total at the end in a "total". The amounts of commision are obviously different for each record but when I click in the "TextBox" it totals the values for the record that box is in ONLY, and all records show the same value in this field. I will click in a different box, and it will change, but ALL of them will change. How do a make it so that all "TextBox" field will show their own value.
Thanks,
View Replies
ADVERTISEMENT
Aug 4, 2005
I have two tables - Hours and Employees. Employees table has these fields:
EmpID, and First, and Last name.
Hours table has these fields:
EmpID, Hours, Funding Source, Nature of Work, Pay Period
I need to produce a report or a query that will display these results:
A column with a list of all the employees and all the Nature of Work categories going across with the sum of hours for each Nature of Work. I'm attaching an Excel spreadsheet that shows kind of what I need. Also, these reports need to be produced for each Funding Source and
Pay Period, so something like [Enter Funding Source] and [Enter Pay Period]
My question is that I don't know how to make a report that will just list a column with all the names and count the sum of hours for each Nature of Work.
Hope this is not too confusing, and any help would be greatly appreciated. thank you!
View 8 Replies
View Related
Sep 25, 2006
Ohh Stress. Need Help....I have a mainform that contained two subforms in it. MainForm called "Employee", the two subform called "vac", and "Prevac". There is a field in my mainform called "Hours", that I'd like to use to Add with a field in Vac subform called "VacA" and a field in Prevac called "FHours". I'd like to display the total (sum of all three field on the same mainform. Is there anyone have any idea? Thanks in advance...
View 3 Replies
View Related
Nov 10, 2005
i have two text boxes in a form:
1) Todays Hours
2) Total Hours
when i enter a number in text box todays hours i want the value in the text box total hours to increment by that amount. this operation will be done on a daly basis for a internet cafe where the user comes and i put in the hours. it seems simple but i really do not know. could someone help me please :confused:
View 1 Replies
View Related
Feb 22, 2007
I'm trying to create a database for a Cattery business.
One thing I'd like to include is a query that calculates how long a cat has been staying at the cattery and then calculating the amount owed by the owner.
I don't have a cost field in any of my tables, so I need to create this on every run of the query.
So for example
A Cat resides in the cattery from the 22nd of Feb to the 28th of Feb. So 6 days (not including the day the cat is brought into the cattery) at a cost of 4.95 a day equals 29.7
Heres what I have so far
http://www.tdh1987.co.uk/pics/query.JPG
Bear in mind I'm from the UK, so the date field is in a different format to yours.
Any help will be greatly appreciated.
View 3 Replies
View Related
Oct 3, 2006
I have a table for invoices that need to show Nett, VAT & Total. I have set up a form and have included a calculation on both VAT (Nett*17.5/100) and Total (Nett+VAT) which works well in the form. Because these calculations are in the ControlSource, I do not know how to have these fields bound to the original table.
Am I missing something really obvious???
View 1 Replies
View Related
Jan 4, 2005
I am new to Access and have made a databse using Access 2000. This is used for a dart league.
In one of the Tables called Members each "record" is made up of the persons details
(Name,address, etc). Included in the Row record I have "fields" (columns) that keep track of
the points each person hits on a nightly basis. I also have a Total Field.
Exampleof fields)
ID#,First Name, Last Name, Address, Phone #, Week 1, week 2, week 3, Total
How do I get the Total Field in the record to add up the nightly points in Week 1 + Week 2 + Week 3.?
In Excel it would be a simple calculation (e.g =sum(a1+b1+c1) etc.
As I said I am new to this database and it is very frustrating at the moment. I have used the help function
but it mostly double dutch to me.
ANY HELP in plain english would be appreciated
View 1 Replies
View Related
Nov 20, 2007
I am working on a system to store financial information. I will be extracting our accounts payable journal entries on a monthly basis into Excel. I want to import that data into an Access table. I have several calculations to perform to create new datafields that I need to store in my Access table. Is it possible to perform these calculations as I load the data? If so what the best method to do this? Here is an example. I will be downloading our monthly hardware depreciation and need to split it among departments based on % stored in another Access Table.
Thank you for any help you can provide me.
Teresa
View 1 Replies
View Related
Dec 21, 2005
I have a table with products and their costs per unit of measure.
some products are costed by weight and others by piece.
table looks something like this:
product
pcs
weight
unit of measure (EA or KGS)
cost
Query should look like this:
product
pcs
weight
Total Cost
Is it possible to write an expression that, depending on the unit of measure uses the pcs or weight field to calculate the total cost ??????
View 5 Replies
View Related
Jul 30, 2007
I've imported an Excel file into a table and now I've created a Query from it.
I now need to Add Fields (names are not in the table) and calculate totals for these renamed fields some of the answers are going to be the result of two or three fields.
Any help would be greatly appreciated.
thanks
View 8 Replies
View Related
Jan 27, 2008
Below is an example of my table "Pricedata" include 03 fields:
Stock Name ___Price________Status Date
A________________10________ __1/6/2008_
A________________11___________1/7/2008_
A________________12___________1/8/2008__
B________________22___________1/6/2008_
B________________25___________1/7/2008_
B________________29___________1/8/2008__
C________________2____________1/6/2008_
C________________2____________1/7/2008__
C________________3____________1/8/2008_
D________________56___________1/6/2008_
D________________45___________1/7/2008_
D________________27___________1/8/2008__
I want to create a query like this: can calcualte the price change and % price change in certain period, and then sort in % change.
This result should be like the table below:
StockName Change in three days % Change in 03 days
C 1 50%
B 7 31.8%
A 2 20%
D -29 -51.78%
Thanks all,
View 6 Replies
View Related
May 6, 2005
I have created a form with a subform and I want to subtract the total of the subform from the total of the form to get a balance; i.e. I have a total of expenses on the form and the subform is a record of payments against the expenses and I want to show a balance.
I have been all over this one and can not make the expression work. I have been working in Access for some time but this one has me stumped, I am probably over thinking it or just can't see the forest for the trees.
I have gone into the expression builder and created the formula but when I complete the process I get the #name? error statement in the field.
Can anyone help me with this problem? Thank you in advance. You can send responses to sharon.dunn@newmont.com.
View 2 Replies
View Related
Sep 6, 2005
Morning All,
Just wondering.
When a form needs a calculated field. Say something simple, like
TotalCost = Quan*Cost
Is it better to create a query?
Use a table and create the calculated field in the query builder?
Or use a table and create a new unbound field on the form with the formula in it?
I never really know which one to do.
Lately I’ve been using saved queries for almost everything and now I have several dozen in this data base and its only half way done. Soon I’ll have to create a data base just to keep track of the queries in this one.
View 7 Replies
View Related
May 27, 2014
I am trying to create an expression that determined by text fields. The field is a PASS for FAIL field. The result needs to =PASS if 70% of the total column is PASS.Can this even be done using an expression?
View 4 Replies
View Related
Jun 15, 2013
In this table I have two fields Seats Available and Seats Remaining
When I register a student to a Course in the Courses table I would like the corresponding Seats Remaining field for that course to decrease automatically.
I can, of course, run a report or a query to see how many are enrolled in the course and then manually update the Seats Remaining field (which is what I currently do), but I would like this process to be automated each time I register a new student.
View 4 Replies
View Related
Oct 2, 2014
My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:
RecordStatus: IIF([DateReceived]<=[DateDue],"Compliant","Delinquent")
would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent).
View 5 Replies
View Related
Jul 8, 2015
I have been trying for about 8 hours to calculate elapsed time between two fields into a third field in Access 2007. Unfortunately, the expression
IIf(IsNull)([Final Suspense]),DateDiff("d",[Routing Status Date],Date()),DateDiff("d",[Routing Status Date],[Final Suspense]))
is not working. Image enclosed....
View 6 Replies
View Related
May 29, 2005
In my Query I have a Date Received field and a Date Approved field.
I would like to calculate the number of work days, which excludes weekends, between Date Received and Data Approved fields.
It would be more desirable to calculate the number of work days, excluding weekends and governmnet obsereved holidays.. but I don't know if that can be done... If not I can stick with the number of work days excluding weekends.
How would I go about doing this? I would need to add a new column to my query?
What would I put for the field and criteria? What kind of expression do I use?
Any help will be appreciated, thanks
View 14 Replies
View Related
Apr 24, 2013
I have created a make table query and using expressions for two of the fields. I have set the field properties (format) to "Percent". The expressions are calculating rates based on two other fields. The expressions are currently as such:
PTD_CM_PGM_RATE: IIf((IsError([PTD_CM_REPT_PGM]/[PTD_CM_REPT_ER])),"0%",[PTD_CM_REPT_PGM]/[PTD_CM_REPT_ER])
I'm using the IIF function to avoid potential errors should one or both of the values ("PTD_CM_REPT_PGM" or "PTD_CM_REPT_ER") be zero.
However, when running the query, I am getting the "...type conversion failure is caused when the data in one or more fields doesn't match...." error.
View 1 Replies
View Related
Apr 23, 2013
I am using calculated field as a data type in access 2010.
They are working fine.
However, I added a new field and now the final calc won't work.
I have Subtotal adding loads of fields together. Works fine.
Then I have a VATunit field which is a double integer, so enter 20 and my next field is VATTotal calculates the SubTotal + the VATunit by doing (Subtotal/100)*VATunit. This calculation is fine and gives me the correct amount.
The next field is a Total field. Which adds Subtotal and the VATTotal together. Howver, the Total is the same as Subtotal. It is not adding the VATTotal to it?
View 2 Replies
View Related
Feb 19, 2007
I have a table with 2 coloums with nuberin them. I would like to add those 2 number together and display them in a 3rd coloum. I need to automatically change the 3rd coloums value should any others change. Is this possible?
View 4 Replies
View Related
Jan 19, 2007
age: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))Dob is stored yyyyddmmI thought this above function looks ok. When I run it, I get a data type mismatch in criteria expression.What am I missing?Any help would be appreciated.Thanks
View 6 Replies
View Related
Sep 20, 2005
hi all,
i have a mainform with a subform.
in the subform i have 2 fields: duur and subtotaal
in duur i enter a number.
in subtotaal a calculation has to occur: =20*([duur]/60)
i tried to do this calculation in VBA but then i got the same value on every record in the subform.
But now the problem:
in the mainform i have a control 'totaal' that needs to do a DSum of subtotaal.
And here i am getting into troubles since subtotaal is not stored in the table but calculated.
The formulla for DSum would be:
Me.Parent!totaal = DSum("subtotaal", "tblFacturenDetail", "[FactuurID] = " & Me.Parent!FactuurID)
but as said, subtotaal is not stored.
How should i proceed?
Thanks!
View 2 Replies
View Related
Jan 6, 2005
If I want to work out how old all my patients will be on the 01 Feb 2005 then why doesn't the expression (in a query)
=(01/02/2005 -[tblPatient]![DateOfBirth]))/365.25 work?
How would I get the answer expressed in months?
View 1 Replies
View Related
Jun 28, 2005
I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.
Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.
Below are the 12 scenarios:
1.) Reduces 75% at Age 70
2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000
3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.
4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.
5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%
6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.
7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.
8.) Terminates on the first of the month following the 65th birthday.
9.) Terminates Age 70
10.) Terminates on date of retirement or 1st or month following 70th birthday
11.) Terminates Age 65
12.) Never Reduces
Thanks for your time, Brian
View 2 Replies
View Related
Nov 5, 2006
I have a database containing values in 16 fields. the fields are filled in over a period of three years. I would like to be able to calculate the average of the last four values entered, regardless of when in the cycle the value is required. I have tryed to use quereies but connot find away to assign the four fields to the expression so that it is the last four values and if four don't exist, avearage what values there are.
View 2 Replies
View Related
