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
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.
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.
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;
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:
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:
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])
i am trying to get a table to automaticaly calculate numbers entered into a field, for example field 3 allready has the number 10 inside it, if i then enter the number 15 i want the field to calculate the existing number with the new number and display 25?
currently when i update the field then i will see 15, so i need to tell it to do something else and that is where i am stuck!
In my table, a column contains different fac# like fac-0086, fac-6200, and fac-0049 (they can be duplicated). These fac# can be categorized into 4 groups. Each week I need to load a list of records from spreadsheets into my cumulative table. The original spreadsheets have fac# but no group code. If I want to group the records by their fac#, how should I deal with it?
I have one table which holds the answers to a medical questionaire. 300 questions to be exact. This table has a Primary Key which is just an autonumber.
The problem I have, is that the questionaire results will be updated from time to time. Not the whole questionaire but maybe a couple of fields. Is there a way that the user can go back to the questionaire make the changes needed and then save, but at the same time still keep the original results in the table, but assign a new autonumber? The reason is that they do not want to fill out the whole questionaire again just to make a small change, but I need to keep all the results going back in history!!!!!
Sorry if I havent explained this perfectly. I have looked at the appen query and the update query but not sure if this would be correct procedure.
Thanks in advance. You dont have to tell me exactly how to do it but a hint in the right direction would be nice.:)
I have a situation where i need to import a table in Access which is in Excel, After importing i need to know how can i break it up differently keeping relation of FK and PK intact: E.g.
Excel Sheet:
Name Biz ID Biz Name Address1 Address 2 Address 3 OrderNo Order Date
Person Record ID(PK) Name Biz ID Biz Name Address 1 ....
I want to export details from a table in access to a word document or any other document that will keep the current design and let me make changes afterwards.
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.
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.
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
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
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.
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN) SET TableA.HCC = TableB.HCC WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL ORDER BY TableB.HCC, TableA.CDN;
New user here so apologies if I post in the wrong place.
My colleague and I are trying to put together a database which automates a very time consuming process which is currently being done in Excel.
The purpose of the database is to pull together a load of actuals from SAP and then the forecasts we have put together and then chuck out a whole load of graphs and a summary spreadsheet/ report for the upper echelons;ons of management.
The data is in the database and so far it is all going well - but we have hit a stumbling block. We need Access to calculate some cumulative totals so that we can throw the whole lot into our excel graphs but we can't figure out how to do it.
We are using ye olde Access 97 so our options are a little limited.
Can anyone give us a starter for 10? If you need anything more technical, I can post whatever information you need.
I want to know the "cummulative sum of a calculated field".
Given Lookup table : A = U, V, W | B = X, Y, Z Field C = iif([A]="U",10, iif([A]="V",20, iif([A]="W",30, 0))) Field D = iif([B]="X",10, iif([B]="Y",20, iif([B]="Z",30, 0))) Field E = C+D Field F = Cummulative sum of "Field E". What would be the expression for the cummulative sum here ? ---------------------------------------------------------------------------------
I tried F = Sum(C+D), but it shows me error ---------------------------------------------------------------------------------
I used to to apply this logic in XL to do my job A-F = XL Columns & 1-9 XL Rows
ifA2=A1, E2+E1, E2
This is to have a cumulative figure for a month. Just bcoz the data is growing in large numbers, we were advised to use access for which I don't know how to perform this calculation.
I am looking for a way to get a progressive cumulative total from daily entries on Odometer records from multiple vehicles. (My current SQL query is not working) I will eventually run a report from these between two dates. I found the thread here, but am unsure of how to implement this - if this is indeed what I need in lieu of my current code..?
[URL]
My current problem is that, not all my previous entries are correct; Im not sure what is happening to the numbers about halfway through...
For clarification, here is my current code and an example of what is needed. The issue is highlighted in yellow. Excel shows the correct calculation
Code: SELECT qry_ODO_Table.ID AS OdomAlias, qry_ODO_Table.ODate, qry_ODO_Table.VehicleNum, Nz(DFirst("Odometer","qry_ODO_Table"),0) AS StartOD, Nz(DLast("Odometer","qry_ODO_Table","[ID] < " & [OdomAlias]),0) AS Previous, qry_ODO_Table.Odometer, [Odometer]-[Previous] AS Difference FROM qry_ODO_Table ORDER BY qry_ODO_Table.ID;
I have an excel report which I would like to run through Access to drive trend analysis and compare with other similar reports. The excel report has a cumulative spend figure each week and not the actual weekly spend numbers, the budget figure also can change depending on the actuals.
Excel report:
Week 1 Product ID Customer Yearly Budget Spend
1122 Sam 100 3
1123 John 200 4
[code]...
Will I need to create a new table each week or can I link the file and it updates automatically?Can Access store the weekly data and just update it one week at a time?
I need calculated field (in a query) that executes a running cummulative total for three conditions: "="&Project Name, "="&Service and "<="&Date.
I can do it in Excel using SUMIFS(), but have no clue on how its done in Access 2010 !
The "Balance" field should be the end result:
Project Service Date Amount Balance A Welding 1/1/2012 100 100 A Welding 1/2/2012 120 220 A Wiring 1/3/2012 150 150 B Welding 1/1/2012 103 103 B Painting 1/2/2012 124 124 B Painting 1/3/2012 155 278 C Welding 1/1/2012 106 106 C Wiring 1/2/2012 127 127 C Wiring 1/3/2012 159 286
