It consists of a running total per vehiclenum. All data comes from one table.
It works properly only on the first vehiclenum of the query. After that, the first "previous" odometer reading of each subsequent vehiclenum starts at some erroneous number, throwing the remainder of each vehiclenum running total.
Here is the code for the query,
SELECT qry_ODO_TotalSub.ID AS OdomAlias, qry_ODO_TotalSub.ODate, qry_ODO_TotalSub.VehicleNum, qry_ODO_TotalSub.Odometer, Nz(DLast("Odometer","qry_ODO_TotalSub","[ID] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference, Nz(DFirst("Odometer","qry_ODO_TotalSub"),0) AS StartOD, [Odometer]-[StartOD] AS RunningSum FROM qry_ODO_TotalSub ORDER BY qry_ODO_TotalSub.ID;
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
I have a list of products that have a loan payment associated to them. To cover these loans, we have incoming revenue for each product at different dates.
The incoming revenue is a field of running sum of revenue for each product.
Desired output:
I want to how how much % of loan (jn total; for all products) is paid in October, november and december and as such (as cumulative). i.e. total of 40% in Oct, 70% in Nov and 100% by Dec etc.
I am creating a line graph from a running total query to show our income from items shipped for each month. Currently I have the following Code in my query which works but it displays the month as a number in my graph and I would like it to show the Month name.
Code: SELECT DatePart("yyyy",[ShippedDate]) AS AYear, DatePart("m",[ShippedDate]) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND DatePart('m', [ShippedDate])<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot FROM tblJobs WHERE (((tblJobs.ShippedDate) Is Not Null)) GROUP BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]) ORDER BY DatePart("yyyy",[ShippedDate]), DatePart("m",[ShippedDate]), DatePart("d",[ShippedDate]);
I tried this solution, but I get an error in the RunTot field, I'm assuming because Access can't use the month name in dsum.
Code: SELECT DatePart("yyyy",[ShippedDate]) AS AYear, MonthName(DatePart("m",[ShippedDate])) AS AMonth, DatePart("d",[ShippedDate]) AS ADay, Format(DSum("SalesPrice","tblJobs","DatePart('d', [ShippedDate])<=" & [ADay] & " AND MonthName(DatePart('m', [ShippedDate]))<=" & [AMonth] & " AND DatePart('yyyy', [ShippedDate])<=" & [AYear] & ""),"Currency") AS RunTot FROM tblJobs WHERE (((tblJobs.ShippedDate) Is Not Null)) GROUP BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]) ORDER BY DatePart("yyyy",[ShippedDate]), MonthName(DatePart("m",[ShippedDate])), DatePart("d",[ShippedDate]);
Is it possible to have a running total either in a query or using the Running Sum function on a text box on a report that will reset after a specific value. Here is what I would like to have happen:
The RunningTotalCube field to reset when it has reach 2.3 or whatever number comes closest to that number.
I am having trouble with this running total. Let's say I have a well. I am trying to create a running total that calculates the total Uptime (or hours operational) for each well every month.
I attached a PDF with an example of what I am working with.
I have a table ("tbl_idq_all") with a text field for product codes ("scode"), a date field (dd/mm/yyyy) and a quantity field ("po_qty"). This table therefore holds future receipts of stock for products.
What I am having trouble doing is create a running total of [po_qty] based on [scode] and [Date].
A good example is stock code 10254. This has a quantity of 40,032 arriving 01/06/2013 and a quantity of 30,096 arriving 01/09/2013.
I have a running total query that seems to run but when I try to total the query results then Access will be "Not Responding". I tried to change it to a Make Table query because I need to use the running total result in another query. So I created a table but when I try to run the make table query it just says "Run Query" at the bottom. Here is the query:
SELECT [OTMissing].[Employee], [OTMissing].[AsOf], [OTMissing].[HRsEarn], (SELECT Sum(OT1.[HRsEarn]) FROM [OTMissing] As OT1 WHERE OT1.[Employee]=[OTMissing].[Employee] AND OT1.[AsOf] <=[OTMissing].[AsOf]) AS RunningTotal, [OTMissing].[RemainPP] INTO OTGenerated FROM [OTMissing] ORDER BY [OTMissing].[Employee], [OTMissing].AsOf;
My OTMissing query is 47061 rows. Does that have something to do with it? The only other thing it might be is that most of the records have 0 although I'm not sure why it would be a problem I thought I would at least mention it.
I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:
As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:
Code: SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1 AS FYearExport FROM tblProject GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted HAVING (((tblProject.FPAccepted)=True));
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
I am having a problem, probably due to my inexperience with Access. Here is the seniero:
-I have a form/table set up for operators to enter numbers on a daily basis. -I am setting up queries/reports to display not only the entered data, but also calculated values from the entered values --I have no idea on how to subtract a previous day's value from today's value and report that value. - Since Access gives no "ownership" to any value I do not know how to reference yesterday's value in today's calculation.Any Ideas?
I have a table of hours that have been worked by employees for each day of the week
[moh] (Monday's hours),[tuh],[weh],[thh],[frh],[sah],[suh] data eg (this is how I would like it to be inputted into the table) 7:24:00,7:24:00,7:24:00,7:24:00,7:22:00,0:00:00,0: 00:00
This equates to 36:58:00
I have tried
Total Hours: [moh]+[tuh]+[weh]+[thh]+[frh]+[sah]+[suh]
but I am struggling to get what I want in the right format.
How to record the initial data or a formula to format the end result.
I have 4 fields I'm trying to combine, but I need to add spaces between field 1 and the rest of them. The total character count needs to be 22 including the spaces.
Example:
Field 1: THE
Field 2: 1234
Field 3: BOAT
Field 4: 0001
End Result: THE 1234BOAT0001
Need to add 7 spaces to equal 22 characters.
Fields 1, 2, and 3 can vary in number of characters.
Query 1: Field #1: User Name Field #2: CountOfUserName
Query 2: Field #1: User Name Field #2: CountOfUserName
Query 3: Field #1: User Name Field #2: CountOfUserName
Query 4: Field #1: User Name Field #2: CountOfUserName
I want to create a Query that will add [Query 1].[CountOfUserName]+[Query 2].[CountOfUserName]+[Query 3].[CountOfUserName]+[Query 4].[CountOfUserName]= [My Query].[TotalCountOfUserName]Group by UserName
Ahhhh this is doing my nugget in!!! I have a simple table with 4 fields ID (unique number) DATE (date) CAPACITY (number of SKU we can hold) ORDERS (number of SKU on order) the data looks like this
ID DATE CAPACITY ORDERS 1 01/01/2007 250000 250000 2 02/01/2007 250000 300000 3 03/01/2007 250000 300000 4 04/01/2007 250000 300000
So looking at the above table we can see that we have more orders than capacity in our factory, however they require to see this in graph form, so what I need is for each ID a running total of the CAPACITY and ORDERS so over a given date range i would produce a graph to find the "pinch points" where we could see if the capacity is less than the orders we have over time.
etc. which i would create my graph from. Ive looked at Dsum and some other methods but cant get my head around it so any help will be much appreciated. Thanks Steve.
I'm trying to create a database to keep track of invoices . on work that was done.is there any sample database that I could take a look at.Or can anyone help me on this I'm trying to capture price on parts + price on labor = total the order form in the tradewinds database looks good but don't know where the code is for calulations? can anyone help me out?
Im having a problem doing a running total on my form. I want the result to be displayed in a text box, with the figures being collected from a column of figures.
I have a table of employees, and dates they worked on. These are seasonal employees who want to get in as many days as possible before the season ends, but regulation states they must take a compulsory rest day after x days.So I need to create a query that can return the list of employees, with a count of consecutive workdays up until current date.
If today is 20/09/2013, and Johnny worked on, 19, 18, 17, 15, 14, 13, his count must be 3, because he was absent on 16. Therefore only from 17 through 19 is regarded as consecutive.
If Peter worked 19, 18, 17, 16, 15, 14, 13, his count would be 7, because unlike Johnny, Peter still worked on 16.
I've got a table with data that's been imported from Excel. I need to run a query that pulls the same values of field one and adds the values of field 2. For instance:
Field 1; Field 2 Jones; 200 Smith;150 Jones; 300 Smith;100
In this example, I need the sum of field 2 for the records where Jones is the value in field 1.
So the result in this case is that records where Field 1 = Jones, Field 2 = 500.
I use the output of a query (qryTally) to set as my values to a table (tblOrderCountDaily) which sets all the count of a product ordered during a cmdbutton was clicked. If cutoff wasnt clicked for that day, it would create a new field setting the field name as the date. Now, if i click again the the cutoff button, it would check again if the field exists, if yes, i would add the value to the previous value.
Code: Private Sub CutOff_Click() Dim db As DAO.Database Dim tbl As DAO.Recordset Dim strSQL As String Dim CheckOut As String
My friends, please help me figure this out. I am new to MS-ACCESS. I am trying to create a simple Leave system for my office. When a user requests a leave, the number of hours will be added to a table. I have created a form for this purpose. What I would then like to happen is, the next field in the Table is the sum of hours requested thus far. So that field would be Requested + Total requested Thus far. I can do it easily in Excel but I can't figure out how to do that in ACCESS. It is a very simple database and I can mail you my sample if you are interested. Thanks in advance.
Does anyon ehave any experience of running totals in an access query. I'm reporting the data through excel not access reports so need a query not a report solution..
What I would like is to have an additional column which keeps a monthly summary of spend based on running total month 1to 12. All items have months 1 - 12 and are ordered in that fashion.
