How To Calculate Cumulative Values Of This Query Fields?

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 Replies


ADVERTISEMENT

How To Calculate Cumulative Values Of This Query Fields?

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
Code: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.SalaryFROM 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)

Code: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 SalaryFROM dbo_Hours_worked;

View Replies View Related

Generating Movement From Cumulative Values

Hi,

I have a query that brings back data as follows (sample):

1 30/5/2006 £100,000
2 30/6/2006 £150,000
3 30/7/2006 £250,000

The currency values are all cumulative - is there a way to add a calculated column to calculate the movement using the previous record using SQL? i.e.

Item Date cumValue Movement
1 30/5/2006 £100,000 £100,000
2 30/6/2006 £150,000 £50,000
3 30/7/2006 £250,000 £100,000

Any help greatly appreciated.

Regards,

Simon

View Replies View Related

Keeping Cumulative Values In A Table

Hi I have a table that looks like this

ordered_equip--------------2008------------2009-----------2010
itemCode1-------------------0----------------1--------------0
itemCode2-------------------0----------------2--------------1
itemCode3-------------------0----------------2--------------1

As you can see in the year 2010 items 2 and 3 go down from qty 2 to 1. What I am trying to do is to keep track of everything that was ever shipped to the customer. So with that in mind the above table is showing that Qty-2 was ordered in 2009 and Qty-1 was ordered in 2010. I want to add these as I go along. So my desired table would look like the following

ordered_equip--------------2008------------2009-----------2010
itemCode1-------------------0----------------1--------------1
itemCode2-------------------0----------------2--------------3
itemCode3-------------------0----------------2--------------3

in this table 2010 shows Qty-3 which means 2 was present on site in year 2009 and 1 more was added in 2010 to make the qty 3. I want to write a storedProcedure or something similar to convert the first table into the second table. I said storedProcedure because I am used to doing this in SQL Server.

View Replies View Related

Calculate Yes/No Fields In A Query

In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return? If so, can you share how this could be accomplished. Thanks

View Replies View Related

Query Field To Concatenate Fields With Nil Values

I have an Access 2000 project and am trying in a query view to join 3 values into one.
2 of the values come from tables, the third comes from another query view (all linked in the query design screen)

Value 1 is always populated, but for each record either Value 2 or Value 3 will always be empty.
All values are strings.

I have tried this formula in the query design grid:

dbo.Value1 + '/' + dbo.Table2.Value2 + dbo.vieTable3.Value3


..in an attempt to give the result Value1/Value2 or value1/Value3 according to the
particular record.

Unfortunately it doesn't work! ...just returns blank results when the query view is run.


Any suggestions would be greatly appreciated

thanks....nick

View Replies View Related

Calculate Difference Between Values

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 Replies View Related

Store Calculate Values

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 Replies View Related

Can't Calculate Report Values

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 View Related

Reference A Fields Values In The Record Source Query

I have a query on a form that is providing all of the information I need for everything on my form.

The problem is I don't know how to refer to a field's value in the query in VBA without having a hidden text field on the form.

I know I can reference a combo box's query to include criteria such as [Forms]![cboSelectStudent] if the control is actually on the form, but how would I refer to a value in the form's query that doesn't have a control on the form.

I've been searching for everything I can think to call this but haven't been able to come up with anything.

Thanks.

View Replies View Related

Linking Values To Auto Calculate

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 Replies View Related

Number Of Query Values And Destination Fields Are Not The Same - Version: 2000 (9.0)

This error message is obvious enough. But ... I have a table, with no relations to other tables, a straightforward query getting all fields and a form made with this query.Just basic Access functionality.
One of the fields of the table is numeric. This number has decimals. Adding a record, filling in all fields, with a number like 1,23 (I'm using a comma as decimal point), is easy enough.
Now I'm using this form as a subform. The table still has no relations, but a relation between main and subform is maintained using VBA.
If you add a record now, like above, you get the error message 'number of query values and destination fields are not the same'. There is no problem when you're using a number like 1,00 or no decimals. After this it's no problem again to change the number to one with decimals, like 1,23.
When I change my regional settings to use a decimal point instead of a comma, it is working fine.
Why might have caused the (insert in the) subform to see the comma as some kind of field separator?

View Replies View Related

Calculate Fields.

Hi, can someone help me with this formula.

i have one field called notice_1 this field is a combo boxs multiple choice ( YES - NO )
Then i have 1 field called score_1 ( if notice_1 = "YES" then let score_1 = 5 else then let score_1 = 10 )


Does this make sense , I am an, amateur at access 2003,

Thank You For any help i can get...

John Calcitrai

View Replies View Related

How To Calculate The Fields From Two Queries?

I need to pull 2 fields from 2 different queries, then calculate them. Those 2 queries has the same structure, just one is last year's data, the other is this year's. :rolleyes:

I tried using the 3rd queries to combine them, then calculate from there, but then I had no clue where to go from there. I don't sql...I was wondering is there anything to do with sql? :confused:

I'm stucked so far, how can I solved it? :mad:

View Replies View Related

Having A Field Calculate Other Fields

I am trying to get one of my fields to calculate this expression which includes other fields:

If "Financing Type" = 1 or 2 THEN 20% * "Loan Amount" OR
If "Financing Type" = 3 or 4 THEN 100% * "Loan Amount" = 2,000,000.


I thought that this would work, but it only works in queries or reports.

=IIF([FinanceType]<3,[LoanAmount]*.2,[LoanAmount])

I would like this to work in the table.

View Replies View Related

Calculate The Field Value Using Other Fields In The Same Table - Version: 2003 (11.0)

Hi! I have one table and I need to calculate a new field called Formulae
Formulae depends on the value of other fields of the table, for example
if field1> 10 then formulae = 0
else if field2 + field 1 > 20 then formulae = 1
else if field3/field4 = 8 then formulae = 2

all the fields are numeric, I'm a newby and can't find out how to do this, I think that I have to use VBA but not sure..
Thanks in advance

View Replies View Related

Have Access Calculate Fields And Place Them In To Another Field - Version: 2000 (9.0)

can anyone tell me how I can have my database do two things.
1) have access calculate one field from lbs to Kgs
2) take 3 numbers from 3 fields and have access multiply the numbers and place them in another field.

Thank you
Vinny

View Replies View Related

Adding Fields Based On Values In Other Fields

Hi,

I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example.

When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.

View Replies View Related

Automatically Calculate Field Values - Version: Any Version

HELP!

I have created a table in access and I am trying to get it to automatically update some fields for me. I have a start date in one column and I need the next column to automatically enter the date ten days later. Is it possible to do this in access and if so how? I've been trying for a long time and have now given up - any help at all appreciated!!

Thanks

View Replies View Related

Cumulative Row Function?

How to make calculated field that represents cumulative row?
For example Ė table has fields "transaction_number", "trans_date" and "trans_amount".
In this table I register money transactions on my bank-account. I need calculated field that is going to show how much money is on my account after every transaction.

Thanks

View Replies View Related

Cumulative Totals

I have two tables, one for budget and the other for actual expenditure. I need a query or queries that will give me a cumulative figure on 3 groups based on the expenditure table. In my expenditure table I have the following fields.

ExpenditureID Autonumber
Period Text
CostCentre Text
CostCode Text
Value Currency
fldDate Short Date

I have managed to fnd a function that will give me a running sum for each individual group, but cannot seem to get it to work for more than 1 group. The function is this.
Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If

'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function

Is there any way I can see the results in one query or table? Any help appreciated.

View Replies View Related

Cumulative Totals - Please Help

Hi,
I'm having trouble trying to create an expression for generating cumulative totals by Month.

I believe this is the function I need to use -

DSum (expression, domain, [criteria] )

I need the layout to be as follows -

Months - Rows
Cumulative Monthly Despatches - Column

I have a field list with
Months
Despatches
Models

All of these are from another Query and 'Despatches' is an expression generated in the other query

Can anyone please Help?!

View Replies View Related

Auto-populating Fields Based On The Values Of Other Fields - Version: Any Version

This is probably an easy one:

I have a table which includes about 20 yes/no fields. I want to show, either as a field in the table, or as the result in a query, a field which would be "Pass" if all the 20 fields are "Yes", and "Fail" if at least one is "No".

I can think of convoluted ways of doing this, but is there anything simple that I might be missing?

View Replies View Related

Calculate Median In Query

Hello:

I have read the Microsoft article at http://support.microsoft.com/kb/q95918/ several times. However, I am looking for an expression I can place in the field name to calcluate the median of another field in the same query. Is this possible? If not, I don't understand how to call the function created in the Microsoft article.

Example (fields in the query)
Expr1
Expr2:Median(Expr1)

Thanks in advance.

View Replies View Related

Query To Calculate Busiest Day

i would like to run a query that works out the busiest day in a month but i also want to run a range from two dates and be able to convert them to

mon ? entries
tue ? entries

and so on

is this possible?

many thanks

michelle

View Replies View Related

How To Calculate Field In Query

Hi Friends,

I have made one form based on query. Its a invoice entry form. I have price, quantity and amount. I want when i enter price and quantity, amount should be calculated. I know its very simple for you. Any suggestion.

thanks

mithani

View Replies View Related

Calculate Balance Query

Hi All.
I have Request, Device and Receive table. All table has DeviceID. I create query to calculate total amount of each type of devices which was requested by link Request and Device tables. And query to calculate total amount of each type of devices which was received by link Receive and Device tables. I would like to create Balance query to calculate:
Total_Receive - Total_Request = Balance
If devices in Request and Receive table is the same result is correct. But if in Receive table add new type of devices I cannot see amount of new device in Balance query. How to fix that problem?
Thanks.

View Replies View Related

Cumulative Monthly Totals

Hi,

I'm trying to create an expression that will calculate cumulative monthly totals but my expression seems to only calculate totals for all months
e.g
MonthDirect DespatchesCum Direct Despatches
2006/031580 21867
2006/0410681 21867
2006/059606 21867

The expression Iím using is
Cum Direct Despatches: (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel)

The query should display the following results -
MonthDirect DespatchesCum Direct Despatches
2006/031580 1580
2006/0410681 12261
2006/059606 21867

I'm using Access 2002 on XP.
Can anyone please advise me where i'm going wrong?!
Thanks in advance for the response

View Replies View Related

Cumulative Monthly Totals

Hi,

I'm trying to create an expression that will calculate cumulative monthly totals but my expression seems to only calculate totals for all months
e.g
MonthDirect DespatchesCum Direct Despatches
2006/031580 21867
2006/0410681 21867
2006/059606 21867

The expression Iím using is
Cum Direct Despatches: (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel)

The query should display the following results -
MonthDirect DespatchesCum Direct Despatches
2006/031580 1580
2006/0410681 12261
2006/059606 21867

I'm using Access 2002 on XP.
Can anyone please advise me where i'm going wrong?!
Thanks in advance for the response

View Replies View Related

Cumulative Monthly Totals?

Hi,

I'm trying to create an expression that will calculate cumulative monthly totals but my expression seems to only calculate totals for all months
e.g
MonthDirect DespatchesCum Direct Despatches
2006/031580 21867
2006/0410681 21867
2006/059606 21867

The expression Iím using is
Cum Direct Despatches: (Select Sum([Direct Despatches]) from QRY_DirectDespatches_ByMonth_ByModel)

The query should display the following results -
MonthDirect DespatchesCum Direct Despatches
2006/031580 1580
2006/0410681 12261
2006/059606 21867

I'm using Access 2002 on XP.
Can anyone please advise me where i'm going wrong?!
Thanks in advance for the response

View Replies View Related

Running Or Cumulative Sum In Diagrams

Let's assume I have a 2-coloumns table. 1st: date, 2nd: some amount. I'd like to create charts. First diagram: amounts in the specified date ranges vs. date (for example weeks). That is ok. Second chart: cumulated amount vs. the same date ranges.

I know how to do it numerically in reports, but how to do it on charts or in queries?

View Replies View Related

Selective Cumulative Sums

Good afternoon,

I am trying to figure out how to create a cumulative sum field for a test report I am working on. On a very simple level, I can run queries to get my data into the following format:

Test # | Article # | Test Time | Test Parameters
1 | 1 | 8 | A,B,C
2 | 2 | 5 | A,B,C
3 | 2 | 7 | A,B,C
4 | 2 | 9 | A,B,C

For each test number, I want a separate report page showing the article number, test time, and test parameters. Also on each page, I want to display the cumulative test time for all test numbers up to the displayed test number, but only for the article used in that test.

For this example, page three would have test number 3, article number 2, test time 7, test parameters A,B,C, and cumulative test time on article 2 of 12 (5+7), while page four would have a cumulative test time on article 2 of 21 (5+7+9).

How can I convince Access to do this for me? So far I've only been able to have a cumulative test time for all test articles, not just the test article related to the test number. To make this more complicated, Access needs to be able to have another article added at any time without having to rework the code to create the test reports.

I appreciate any help you can give me that will get me on my way.

Thanks!
Erik

View Replies View Related

Calculate Median Using Access Query

How can i calculate median using access query. Is there something as simple as aggregate functions like SUM, AVG etc for calculating Median? Please help.

Thanks

View Replies View Related







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