Can't Calculate Report Values

Feb 3, 2005

Hi all -

I need to create record totals and grand totals on a report where I count up the # of Yes's and No's across 10 fields. I've already created calculated text boxes that come up with the record totals. What I can't figure out for the life of me is how to create a text box calculating the grand total based on the previous calculated fields I created. It seems that access isn't letting me sum a field I created on the report. Do I have to create a query first? Thanks.

View Replies


ADVERTISEMENT

Calculate Difference Between Values

Jul 17, 2006

How do I calculate the values between two columns and populate a third column with those new values? I have an ''Actual Cost'' column and ''Budget Cost'' column and I would like to create a third column ''Margin.''

Is this something that needs to be done with a query or can it be handeled in the design view of a table? I'm starting to learn that Access works nothing like Excel.

Thank you in advance for any help. I scanned some of the threads in this forum, but many were beyond my comprehension. Any walk-through of this procedure would be much appreciated.

View 1 Replies View Related

Store Calculate Values

Sep 29, 2005

I searched the archive for how to store a calculated value and found a lot of controversial dialogue about the pros/cons but not really a solution on how to do it.

I have a form based on a query. The database behind the form and query is our ERP database and is connected though an odbc connection. The form allows the user to enter some shipping dimensions and freight rates. The data is automatically stored in the ERP database and any user can view the data from the ERP software.

Here is the problem. There are also some freight calculations that occur on the form that our business teams want the results stored in the ERP database. They can view the results from the calculations using the ERP software instead having to open a 2nd application (Access form) to view the calculated data.

I have determined which fields in the ERP database will hold the data. I only need the vb code or other suggestions on how to update the calculated values into the database.

I apologize for the long message. Thanks for your help,

Jeff

View 3 Replies View Related

Linking Values To Auto Calculate

Aug 6, 2007

I have parts that go into and out of inventory. Each time a part is removed or returned, the user inputs the number into the computer. I was wondering how I could link the numbers so that as parts were removed or added, the total number in inventory and the total number on the floor automatically changed. Thanks so much.

View 1 Replies View Related

How To Calculate Cumulative Values Of This Query Fields?

Jul 12, 2005

Hi everybody. I got a access 2000 query that lists :

1)weekno
2)year
3)project (project number )
4)QweekylyReportHeader (project description )
5)customer (customer that requested this project)
6)department (department number and name that implements this project)
7)Projectleader ( project leader name and number that is responsible for this project)
8)Task (Task number that is done for this project )
9)task description (description of task )
10)employee ( employee number who is working in this project )
11)name (Employee name and initial and last that works for this project )


12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this
13)salary (amount of salary given to this employee) ===>i want cumulative for this


I want to create another query that lists :

A)cumulative value of hours worked on particular project task up that point.
b)cumulative value for wages given for that project task up that point.

http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample)

The above query ONLY lists hours worked and wages gives for particular project task only during
each week.But i want hours worked and wages give for particle project task up to that point in week. For
example a project task might have implemented last week but not this so i want to take that in calculation as well.
I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular
project task.



Notes:


- There is a possibility that during a particular week no task been implement for particular project.
- One employee can work in more then one project
- One employee can have more then one salary (amount) for the same
project because he might get raise in salary!
- Only tasks carried this week will be printed in the weekly report

http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database)
http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population)
http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view)


query that display hourly wages of certain project during each week


SELECT
querythisweek.weekno,
querythisweek.Year,
querythisweek.Project,
QweeklyReportHeader.Customer,
QweeklyReportHeader.Department,
QweeklyReportHeader.description,
QweeklyReportHeader.ProjectLeader,
querythisweek.Task,
dbo_Task.description,
querythisweek.Employee,
[lastname] & ' ' & [initials] & ' ' & [insertion] AS Name,
querythisweek.hours,
querythisweek.Salary
FROM
dbo_Task
INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task;




code for querythis week( calcualte the salary and hours worked)





SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours
from dbo_Hourly_wages a
where dbo_Hours_worked.Employee = a.Employee
and dbo_Hours_worked.Project = a.Project
and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno)
from dbo_Hourly_wages b
where b.Year < dbo_Hours_worked.Year
or (b.Year = dbo_Hours_worked.Year and b.weekno <= dbo_Hours_worked.weekno))) AS Salary
FROM dbo_Hours_worked;

View 5 Replies View Related

Queries :: Calculate Multiple Values Same Field

Feb 22, 2014

I have a table where there are multiple vehicles, each identified by their vehiclenumber. Each record holds the vehiclenumber, date and odometer reading. I need to figure out how to calculate records in this table per each vehiclenumber.

Below is a code that works, but only when i have each vehicle with the same vehiclenumber.

SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, tblOdometer.Odometer AS OdomAlias,
Nz(DLast("Odometer","tblOdometer","[Odometer] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference
FROM tblOdometer;

View 4 Replies View Related

Moving Average - How To Calculate Values From Column

Aug 13, 2013

Is there any formula or any way to calculate moving average on access? What I need is to calculate a value based on data from the past 3 months:

I have following structure for example:

Column A ----- Column B -------- Column C
ValueA1 ------- Value B1 ---------

ValueA2 ------- Value B2 ---------
ValueA3 ------- Value B3 --------- =average (ValueB1, valueB2, valueB3)
ValueA4 ------- Value B4 --------- = average (valueB2, valueB3, valueB4)

My main point id how to calculate values for column C.

View 1 Replies View Related

Tables :: Auto-Calculate Field From Other Table Values

Aug 10, 2015

How to set up my tables as I'm just starting off with setting my database up.

I'm doing a simple database to track the purchase orders (PO) I am managing. Each PO has a PO Number and an Original Value. POs may have multiple amendments which would change the PO value. I would however like to keep the history of the PO original value and all different amendments.

So I created two tables:

tblPO:
ID
PO Number (Number)
PO Original Value (Currency)
PO Sum of Amendments (???????????)
PO Current Value (Calculated = PO Original - PO Sum of Amendments)

tblPOAmendments:
ID
PO (Lookup from tblPO)
PO Amended Value (Currency)
Amendment Date (Date/Time)
Amendment Desc (Text)

Now the two, million dollar questions are:
1.) Is this the right table structure to use.
2.) How do I go about calculating the Sum of Amendments field?

View 4 Replies View Related

Queries :: Calculate Cumulative Values Stored In Field

Oct 7, 2013

I have several projects with different tasks for each. I have 3 fields [fkProjectsID], [TaskOrder] and [Duration] in a table for project tracking with that structure:

[fkProjectsID] [TaskOrder] [Duration]
1 /1 /5
1 /2 /8
1 /3 /15
1 /4 /6
2 /1 /8
2 /2 /30
2 /3 /25

I want to calculate cumulative values stored in [Duration] field (represent a number of days). I'm using the field [TaskOrder] to order different tasks within each project. With some testing, I was able to calculate cumulative [Duration] with 1 project using the DSum fucntion as following:

CumulDuration: DSum("[Duration]", "[tblProjectTracking]", "[TaskOrder]<=" & [TaskOrder])

I was having the sequence: 5, 13, 28, 34 for respectively Task 1,2,3,4. However, when I add a second project (and then a third...), I need to be able to filter based on [fkProjectsID] as well (i.e. a specific DSum by ProjectsID). I add this criteria but I get the sum of [Duration] on each row instead of the cumulative:

CumulDuration: DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] And "[fkProjectsID]=" & [fkProjectsID])

View 3 Replies View Related

Forms :: Calculate Difference Between Two Query Table Values?

Nov 27, 2014

I am struggling with calculate difference between two query table values, I first created a make a table query(current meter reading) which contains one column called "meter read" , and I created a second make a table query(previous meter reading) which also contains "meter read" column, I linked those two make table queries to calculate the value difference between two date, how can I create a form to calculate diff between any two date?

View 3 Replies View Related

How To Calculate Maximum Values From The Table And Assign To Textbox

Nov 18, 2011

I have a problem in doing a task with my form. Actually I have a button to add a new record which opens a new form there i enter the values to the record. But when I press the Addnew record button I want to calculate the maximum of the Identity field +1 and open the new form with that new number which i have calculated. How can i do this....

View 1 Replies View Related

Reports :: Report In Which A Textbox Generates Numerical Values And Letter Values

Aug 6, 2014

I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.

View 9 Replies View Related

Calculate Time On Report

Oct 5, 2007

I have one table, Leopard Test Scores 2007, that has only two fields, employee and TotalSeconds. The totalseconds column has a "Number" data type with Long Integer. All entries in this column represent the number of seconds and individual spent to take a test.

All I need to do is calculate the total minutes for ALL records in this table for a report. I want the total to come out in this format: HH:MM:SS - so I've added the following three fields to my query:

Hours:[TotalSeconds] 3600
Minutes: ([TotalSeconds] - Hours * 3600) 60
Secs: ([TotalSeconds] - Hours * 3600 - Minutes * 60) Mod 60


However when I add those three fields to my report, I get an "enter parameter value" for hours and minutes. I must be missing something? any suggestions??

View 10 Replies View Related

Select Only A Certain Data To Calculate In Report

Oct 26, 2006

I have a database that contained the following fields. VacEarned,VacUsed,VacPlan (Yes,No). In my report, I grouped VacPlan field (which is yes/no field) together and sum it up by VacUsed. However, I'd like to put another formula in my report to subtract the the Sum of the VacPlan from VacEarned...I have such a hard time doing that..Is there way that I can put if statement in the report option that would do the following: If VacPlan is Yes, than Sum VacUsed and use it to subtract from VacEarned..is this possible..
I hope this make sense...

Thanks in advance..

View 1 Replies View Related

Reports :: Calculate Percentages Based On Criteria In Report?

Oct 22, 2013

I created a database to record time logged per workorder for each employee on my job. Each time log has a specific "Trade" attached to it along with a number of hours the employee spent on that workorder. I've created a report to display how much time the selected employee spent on each workorder (within a date range) and now I want to see what percentage of their time was spent on a particular "Trade" (for instance, during September Employee "name" spent "percentage" of their time on Electric, "percentage" on HVAC, "percentage" on Plumbing...[and so on])

I have trades listed in the table and in the time log, the form writes to the trades area of the table (probably very elementary for this discussion) and the report lists the name and grand totals with percentage of total time on each workorder, but does not list any trade information.

How can I add this into my report, preferably at the end (Report Footer?)

View 4 Replies View Related

Calculate Variance Of Payment Month On Month - Values Comparison

Mar 19, 2012

I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.

I need to calculate variance of payments month on month and identify set of customers who have made huge payments.

View 10 Replies View Related

Sub Report Changes Values When Placed In Main Report

Aug 28, 2014

In Access 2010, I have a simple sub-report I use to count a field. Works perfect by itself. However, when I place it in the main report the results change. And it doesn't matter where I place it in the Main report, it still returns a low count. Is there some filter I am missing?

View 5 Replies View Related

Calculating Values Vis-à-vis A Report

Aug 30, 2004

Just wondering if anybody could help me on this. I have encountered a problem in producing a report based on my main form (recordsource: tblStudents) and subform (recordsource:tblStudentsClasses), which are joined through their respective StudentID fields. My problem pertains to the fact that I just couldn’t seem to find a way to make my report produce a calculated value using an unbound control. I need it to show the average grade a student gets every semester by multiplying his individual class grades against its corresponding credit units and then add them together, and divide them against the sum total of that particular semester’s credit units. For example, a grade of 1.0 for psychology 101 multiplied by that subject’s 3 credit units + a grade of 2.0 for chemistry 101 multiplied by that subject’s 5 credit units and then divided by the sum total of those two subject’s credit units of 8.

I kinda find it complicated because each semester is made up of between 1 to 10 classes, so it would be inappropriate to just put the one average computation on a footer since each semester has to have an average grade of its own. Tried lots of procedure already and couldnt make anyone of them work. Another fact is that a credit unit of –3 and grade of 5.0 shouldn’t be included in the computation at all. And some of my data are supplied by combo boxes in the subform – cboCreditUnits and cboGrades (rowsources: tblClasses and tblGrades). How should I go about this all? Any ideas will be highly appreciated.

Thanks!

View 1 Replies View Related

Blank Values In Report?

Oct 30, 2014

I have two issues with the report generation from a query:

(i) Sometimes blank values appear in the report in the serial number column generated dynamically (setting the control source property =1 and running sum = overall), but when clicked inside the particular box, the correct serial number appears

(ii) When grouped on a field, the particular field value appears sometimes more than once in the report inside the same group.

How to get rid of this?

View 1 Replies View Related

Report Showing Num Due To Zero Values

Jan 2, 2014

I have a report that is returning percentages based upon other calculated fields. Some of the percentage fields are showing #Num on the report. I've found this is due to one of the calculated values being zero.

3 Fields used:

Field: Qty
Total: Sum

Field: InitQty
Total: Sum

Field: OrderQty: Sum([Qty]-[InitQty])
Total: Expression

I need two more fields to return percentages based off of those 3 fields. This is what I started out with:

Field: InStock: [SumOfInitQty]/[SumOfQty]
Total: Expression

Field: Ordered: [OrderQty]/[SumOfQty]
Total: Expression

This works great until the query returns one that has sum of qty equal to zero. So this is what I've tried:

Field: InStock: IIf(Nz([SumOfInitQty],0),0,[SumOfInitQty]/[SumOfQty])
Total: Expression

Seems like it should work but its still showing #Num when I run the query.

View 2 Replies View Related

Count Exclusive Values In A Report

Jul 13, 2006

Dear all,

I'm having problems with a count expression in a report. I have a series of items listed, with subdata against each one, in some cases this subdata is one rows, in others it is many rows.

Anyways, having listed these items and there subdata sets, I want a count of the number of items listed.

Unfortunately, a count on Equipment ID returns the total number of sub data items listed, as for each sub data item, the Equipment ID is associated. Is there a way of making the COUNT expression count exclusive values?

Thanks,

Stephen

View 3 Replies View Related

Count Exclusive Values In A Report

Jul 13, 2006

Dear all,

I'm having problems with a count expression in a report. I have a series of items listed, with subdata against each one, in some cases this subdata is one rows, in others it is many rows.

Anyways, having listed these items and there subdata sets, I want a count of the number of items listed.

Unfortunately, a count on Equipment ID returns the total number of sub data items listed, as for each sub data item, the Equipment ID is associated. Is there a way of making the COUNT expression count exclusive values?

Thanks,

Stephen

View 1 Replies View Related

Adding Text Box Values In Report -

Feb 8, 2007

Am I missing something simple here? I have several text boxes, which I want to add the values. I have tried these formulae, but dont work:

=Sum([Postage],[ProgTotal])

=Sum([Postage] + [ProgTotal])

=DSum([Postage],[ProgTotal])

Any help would be greatly appreciated!

View 7 Replies View Related

Report - Show Only Fields That Have Values Above 0

Mar 8, 2005

I am trying to generate a report, or even a word merge doc, that will print only the fields containing values above "0". I have to print a one page benefits report for each employee, yet not everyone has values in each available field. On each report, I only want to show the fields that actually pertain to the employee. Is this possible?

I have tried doing this as a Word merge, but the field names do not show up, only the values...I have been struggling with this for over a day...please help!

View 4 Replies View Related

Not Showing Blank Values On A Report

May 3, 2005

Hi,

Each client has either an entry with a correspondence number or a notes number. So, the table would look somewhat like this:

Client: 333, Correspondence: 1, Notes: Blank
Client: 333, Correspondence: 2, Notes: Blank
Client: 333, Correspondence: Blank, Notes: 3
Client: 333, Correspondence: Blank, Notes: 4

How would I make a report that will make it look like this

Client: 333, Correspondence: 1, Notes: 3
Client: 333, Correspondence: 2, Notes: 4

Thanks! Hope that was clear.

G

View 4 Replies View Related

Reports :: Calculating Values In A Report

Jul 29, 2015

we have a field in a report, and currently, there is a text box with a control source of

=IIf([Flag1]="Capital",[Reserve]-Sum([Text52]),"")

They want me to add to it, where if [Text106]="0",[Reserve]-[Text52], otherwise it's [Text106]="0",[Reserve]-[Text106].I am able to do this second part OK with

=IIf([Text106]="0",[Reserve]-[Text52],[Reserve]-[Text106])

But then I lose the [Flag1]="Capital" part.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved