Report Calculation Help
Sep 2, 2004I'm new to access, I have gotten far enough to understand the basics.
question is how do I add the auto sum function to my report to say 1,2,3 and so on and show the total on the bottom
ADVERTISEMENT
Using Calculation In REPORT
Nov 9, 2005I am trying to create a report (rptHBSummary) that has all the fields in one table (tblJob) and an extra field that calculates the maximum value of a particular field (Percent) in a different table (tblJobStatus).
More detail: in this database jobs are inputted into the system (into tblJob) and then user can add status info on the job/s (notes while working on the job). each job can have number of status notes (stored in tblJobStatus and these to tables are linked by JobID). In each status note there is a field called 'Percent', which is changed every time there has been a progress on the job. This basically shows what percent of the job is done!
Now in this report I want all the job detail fields plus a field showing the maximum Percent value in tblJobStatus for each JobID.
i.e.
Job ID||Entry Date|| ... ||Job Description|| Job Status
4 || 12/11/05 || ... || bla bla bla || 98 >>> (this will the max value of in Percent field in tblJobStaus for JobID 4)
6 || 13/11/05 || ... || bla bla bla || 100
I tried making a query to pull out this data for me but it didn't work, this is qryTest. I can get the max value of Percent for a given JobID using a query (done in qryMaxPercent) but I don't know how to do this in the report! I am new to Access and I would appreciate any help or suggestions, This is quite urgent and i'm starting to panic! Please help! Cheers (Database is attached!)
Calculation In Report
Sep 13, 2004Hi All
I am new to this forum and would like to say hello to all. The database I am attempting to build is for a charity for young people (unpaid of course). Being unemployed I have plenty of time on my hands to experiment but I do get bogged down often so i may need a bit of help as this is my 1st attempt at building a database. Hope it will not be problem just asking questions for now and maybe returning the favour in the future
Hope you can help. I need a expression to calculate how many males/females have attended/ notattended(yes/no tick box) counselling sessions in a report. Havebeen struggling with this for a couple of days now. I can get the number of Males or females offered appointments but am unable to take this further and break down the attendance figures. can anyone help please?
Thanks
Dave
Report Calculation
Nov 16, 2004Hi,
I have a report that is grouped by month, then a report total. I want to also have a total on the report footer that caluclates a specific project. THese are my fields:
Project
Forecast$
This is my calulation as it is now:
=Sum(Bonds![Forecast$])
I just can't figure out how to have it only calulate for the Project "Anaverde".
Any suggestions?
Reports :: Calculation For 16 Fields In A Report
Jun 23, 2013I'm using Access 2010 and creating a report that is summing 16 fields but the wizard says I have too many fields selected. I had tried creating the report from scratch adding a sum field in the group footer but the field appears blank.
Below is what I am adding together all from one table. I have another report that will need to sum up 20 fields from a query.
=Sum([S1_MaxProfSupportAmt]+[S1_RateAirAllow]+[S1_RateRailAllow]+[S1_RateHireCarAllow]+[S1_MaxPersonCarAmt]+[S1_RateTaxiAllow]+[S1_RateParkingAllow]+[S1_MaxAccomAmt]+[S1_MaxAbsentAmt]+[S1_MaxBreakfastAmt]+[S1_MaxLunchAmt]+[S1_MaxDinnerAmt]+[S1_MaxIncidentalAmt]+[S1_MaxRoomHireAmt]+[S1_MaxSecretaryAmt]+[S1_AddNoteMaxAmt])
Calculation In A Report - Subtract Calculated Number From A Constant
Aug 25, 2011I have a column with a sum total in the footer. I would like to subtract this calculated number from a constant (i.e., 20,000.00). Is this possible?
View 3 Replies View RelatedCalculation (Formula) On Report Will Change Every Year - Preserve Historical Date
Oct 21, 2015So I have a company where the bonus amount for a calculation can change quarterly - if a person accomplishes 50-100% of plan they get that % of their bonus amount.
I have that working on a variable detail DB where the historical data is correct for the report.
i.e. if I want to look at January - the report looks at the requested date: January and calculates using the bonus number from the last update made before January (year is also factored in)
So: January 2014 if they make 50% of plan and their bonus is $100 this month - they receive $50
Good - no problem
NOW: Every year the formula on the report Could Change - so next year if the person makes 50-100% of plan and 30% of secondary plan - they get 30%(% of Bonus)
So now: January 2015 if they make 30% of secondary plan and 50% of plan with $100 bonus the report would give .30*(.50*100) = 15
I can change the calculation on the report - BUT then how would I go back and accurately show what they got in January 2014
Would it require a different report per year?
IF Then Calculation
Oct 11, 2005Trying to calculate a few fields. 1st field is [delays] and 2nd field is [Planned]
The Plan scheduled for 60 and the Delays are an entered value. In the on update command button on the form I want code that will take the value above 60 and add then to the value in the [Delays] So if delays is 10 and the planned is 70 then planned will change to 60 and delays will = 20
any help
Jon
Q:Calculation
Oct 24, 2005Hi All.
I have 3 tables in Access:
tblCurrency (Fields: IDCurrency, CDate, CRate)
tblOrder (Fields: IDOrder, OName, ODate)
tblCalculation (Fields: IDCal, Rate1, Rate2, Rate3, COrder)
IDOrder is related to COrder with "One to Many" connection.
I have Form frmOrder, based on tblOrder. For Example Form has 1 record:
IDCal OName ODate
1 XXXX 2005.10.24
For this record related table has 2 records:
IDCal Rate1 Rate2 Rate3 COrder
1 10 3.5 15 1
2 6 4.2 12 1
tblCurrency for Exapmle have one record too:
IDCurrency CDate CRate
1 2005.10.24 0.2
In frmOrder I have textBox txtRate. Here I need to be calculated:
=Sum (Rate1*(CRate from tblCurrency Where tblCurrency.Cdate = tblOrder.Odate)+Rate2*Rate3 for Each record related to tblOrder).
By Exapmle: (10*0.2+3.5*15)+(6*0.2+4.2*12)=106.1
Please help with expression.
Thank You in advance.
Need Help With Calculation
Jun 9, 2007hey i hope some one can help me...
I am making a database for products and suppliers. Many suppliers can supply each product.
The product details are in a table called products, the suppliers details are stored in a table called suppliers and the suppliers offer for each product is stored in a table called prices.
the structure is as follows:
Table: Products
Product Name
Trade Price
Table: Suppliers
Supplier Name
Table: Prices
Product Name
Supplier Name
Supplier Offer
I have form that shows the product details and a subtable linked to the table:prices.
The problem
I need to add a field in the subtable called "Percent off trade" which is the trade price - supplier price / trade price * 100
i created a query showing the same records as the prices table plus the field "trade off price" and set the Field to:
Percent Off Trade: ([Forms]![Products]![Trade Price]-[Prices]![Supplier Price])/[Products]![Trade Price]*100
But this doesnt give me the correct percent :confused:
I hope i have explained this query and would really appreciate anyone who takes the time to suggest anything that could help
thanks in advance!
Calculation
Nov 21, 2007How can I multiplicate two numbers from two columns in a subform, show their result at the end of the row and then calculate sum of those results on the main form. This is something what I know how to do but the problem is following:
The total sum on the main form should be multiplicated wit 0,5 % and then reduced for a value of a specific row sum. It should be something like this: (Sum of rows sum * 0,5 %) - Sum of the specific row = Total sum on the main form. And all of this should be on a report too.
What Calculation To Use?
Jan 2, 2006What would I use for a formuls to find all product that are in stock.
like I have a price for all products that i stock, but some i don't have in stock. How would I query for the ones that I have in stock?
In my table I have it as UnitsOnHand, I have it as how many, like 1 or 3 or 4 etc. And also a field for Paper or Metal.
How would I query just for paper and not metal?
I am also going to need a query for the lowest price and the highest price.
Please understand I am New and I have allready search through all the post and can't seem to find anything close to what I am trying to do.
Any and all help would be great
Thank You
WackoWolf
Need Help In Calculation
Jan 20, 2008hi,
i'm working in a database and i face some problems in some calculation
this is my table which contain these fields:
NOC number
Company Name
Warning Letter
Note: each company has many NOC
Here what i need from the query
1- create a field which contain a total of warning for each month
2- create a field which contain a total of warning for each NOC
3- create a field which contain the total of warning for the company
Help With A Calculation!
Feb 26, 2008This is probably an easy calcualtion I am overthinking...
I am trying to make a query where I am calculating the "Total Value" of each customer's order of each item for the current week. The item "my company" is selling is fish, sizes small, medium and large all at different prices. I'm not exactly sure what they mean by "Total value", but I assume I take the cost times the quantity...but then what is the difference between Total Value and total ordered (in dollar amount)? Any suggestions?
Calculation
May 25, 2005I wonder if someone can point me in the right direction with a calculation problem. I have never done calculations in access before but want to make a balance sheet for a club account that I am designing. Very much in its trial stages, I have a form with three currency text boxes in it, set up as continuous. MoneyIn and MoneyOut are in the main form whilst Total is in the footer. All I want is to be able to put in a value in MoneyIn or MoneyOut and for the Total to update. Problem! I dont have a clue where to start! :rolleyes:
View 2 Replies View RelatedCalculation Help
Jun 20, 2006Here is a part of my database nicely zipped for you guys to help me with.
whats happening is when i click the totals button whatever the highest cost option that is selected with a "Y" should be displayed in the totals text box.
example:
Installation of heating system - "Y"
Replacement Kitchen and Bathroom - "Y"
Total should be "£90"
The code behind the button works fine with every choice but not the installation of the heating system. When this is selected "Y" the code seems to be ignoring it.
Any help will be much appreciated.
Payroll Calculation
Dec 6, 2005I am trying to type a code based on 1 & 2 criterias. However, I am having a problem on the first criteria because it is adding 8 hours when it shouldn't. Can someone help to either fix my code or write a new one? Thank you very much!
Criteria 1: If the day_of_wk is 1, calculate time-reg_time/60, If the day_of_wk is 1 and time is less than 0, calculate time-reg_time/60+24
Criteria 2: If the day_of_wk is 2, calculate time/60, If the day_of_wk is 2 and time is less than 0, calculate time/60+24
My code: pre: IIf([day_of_wk]="2",[time]/60,IIf([time]<0,([time]-[reg_time])/60+24,([time]-[reg_time])/60))
Time Calculation
Jan 25, 2006i have two date/time fields
sdate (start date) 01/01/2006 14:30 - format(dd/mm/yyyy hh:mm)
edate (end date) 02/01/2006 14:31 - format(dd/mm/yyyy hh:mm)
i need to get answer in HH:MM (24:01)
pls help me
thnx best regards
ibu
Time Calculation
Jan 25, 2006i have two date/time fields
sdate (start date) 01/01/2006 14:30 - format(dd/mm/yyyy hh:mm)
edate (end date) 02/01/2006 14:31 - format(dd/mm/yyyy hh:mm)
i need to get answer in a textbox as 24:01 (hh:mm)
pls help me
thnx best regards
ibu
Time Calculation
Feb 8, 2006Hi all,
I'm a beginner, and i want to know how to calculate time difference. For ex, if i substract 23:45 of 02/09/2006 with 00:10 of 02/10/2006 then i get a - "ive" value... Please somebody help me with a vba code for this...
thanks
Senthil.
Date Calculation
Feb 11, 2007I want to resolve date calculation problem.
I have one form field with users total sum of lenght of service (format of field looks like yy/mm/ddd) in another form field I have data with users date of employment in a firm.
In the third field I want to show total sum of lenght of service (previouse lenght of service and lenght of service in our firm) in format like in first field yy/mm/ddd.
What is a easiest way to calculate a third field in a form.
Thanks in advance !!
Speed Of Calculation
Apr 5, 2007Hey,
I noticed something strange in access 2000: sometimes it takes a long time to calculate a report and other times it goes rapidly. I don't see any process taking a lot of CPU %.
When i do the same thing in Access2003, it goes rapidly every time.
Can anyone help me?
Rik
Stock Calculation
Apr 19, 2007Hi All
I am seeking the solution to a problem with calculating stock adjustments
We use a job code which comprises several stock items and labour which = a $ value
I have a form "work in progress" where I enter all the job details including the job code, the aim is to reduce the individual stock items from a table "Stock" when the job code is entered.
JobCode 25WE = 1 x power point, 25metre cable , 9 x clips
I have tried several methods but to no avail, is there a way of doing this
Thanks in advance
GeoffK
Access Calculation
Jul 18, 2007i Want to make something in access for my job but i dont know how ... I have many years to use this software.
The idea is to have a FORM with many boxes .... http://img329.imageshack.us/my.php?image=58740895rq8.jpg
when i press product 1 and then product 2 i want to calculate, to give me the sum ....... i ll need to have many products i want to make calculations show i ve create one table from which i want to take the prices ...is it possible or it can be with an other way ?? :confused:
http://img525.imageshack.us/my.php?image=90673797oy7.jpg
Does anyone have a similar to saw me? :o
I need ideas !!!!
thanks!!!!
Simple Calculation Help
Jul 20, 2007Well, I hope this is simple, but for some strange reason, very difficult for me.
I'm having issues setting up a calculation between 2 tables. for example:
Table1 ID Quantity
Table2 TransID Used
The "Quantity" from Table1 is a set number but the "Used" will change. all I want to do is have a Form and Report that will subtract the "Used" from Table2 from "Quantity" from Table1.
I've tried: =Table1.Quantity - Table2.Used
=Quantity - Used
what am I doing wrong? all my attempts have failed, even though I have setup a relationship between the 2 tables.
any idea?
thx
Calculation Problem
Oct 20, 2004see this thread...
http://www.access-programmers.co.uk/forums/showthread.php?t=69237
hi everyone,
can we please start this thread again...
- since may'04 (when we started this thread), i have tried many of the suggestions in the replies to this thread, and as i am no expert in access i repeatedly ended up at the beginning.
- if look at the history of this thread, you will notice that i need just a very small and simple db.
- since may'04 i did alot of reading and 'self-study' on access, and i'm sort of "getting the picture" now, but could find nothing to sort out my problem.
- most of you guys seem to know alot! about programming in access, and i have great respect for you... but if anyone could please suggest something SIMPLE! to sort this problem i'm having. (or is this wishfull thinking on my part?) :)
have a good day !
Regards,
Piet.