Queries :: Make Table Query Not Working Because Of Running Total?
Nov 12, 2014
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.
View Replies
ADVERTISEMENT
Jul 23, 2015
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..
View 9 Replies
View Related
May 20, 2015
I have a table with dates in field1 and an amount of seconds in field2.
field1 field2
01/01/2015, 1345
02/01/2015, -132
04/01/2015, 259
I would like to produce a query that performs a running total in the third column like so:
field1 field2 field3
01/01/2015, 1345, 1345
02/01/2015, -132, 1213
04/01/2015, 259, 1472
This is quite simple to achieve in Excel. (eg =SUM($B$1:B3))
What is the query formula for Access?
View 1 Replies
View Related
Dec 18, 2013
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]);
Do any of you know a way I can make this work?
View 2 Replies
View Related
Aug 5, 2014
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:
FYear BudgetedCostIndCont Commitment
2010
2011 8585643 4742000 3843643
2012 2297116511432165 11539000
2013 3618726216963282 19223980
2014 4457769020706644 23871046
2015 4963815023206644 26431506
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));
and the Aggregate query:
Code:
SELECT qryDashboardChart1.FYearExport,
DSum("[BudgetedCost]","tblProject","Year([DateContractSigned])<=" & [FyearExport]-1 & "")
AS RunTotBudgetedCost, ([RunTotBudgetedCost]-[RunTotTECTERRACommitment])
[code]....
I should also mention that I cannot implement the NZ() function, as Excel balks at this when trying to link to Access queries.
View 8 Replies
View Related
Jun 16, 2014
I have a database and one of the functions is to track the purchase of fuel. Part of purchasing fuel is registering what the current odometer reads. My query gets the Vehicle ID, Date of Transaction and Odometer reading. I want to show a report that shows how many miles the vehicle is has traveled for a certain amount of time. If it's for 2013 then I'll put the criteria for 2014 in the date. This will give me a list of all vehicles and their odometer readings. I then sort the date by ascending.
In the report I group on vehicle ID and then I want to show all their transactions and add up how many miles were driven.
Vehicle 1
1/2/201
1/5/2013
1/15/2013
ETC...
Total Miles Driven in 2014: XXXXXX
View 1 Replies
View Related
Jan 27, 2006
Hi all,
I am trying to update a table in a database (Lets call that DbNew). I want that when DbNew form is loaded it automatically updates its underlying table (lets call it TblNew) by running a make table query (called "coversheet") that is located in another database (call that DbOld). DbNew and dbOld are all on a network and I dont have total access on DbOld (but I have created the make table query "coversheet in DbOld).
I should also state that DbNew is bound to TblNew and I am not interested in keeping the old table in DbNew. Information is frequently updated in DbOld and all I am interested in is loading the most current info in DbNew so the current table can be deleted upon loading DbNew and a new one created. Here is what I tried doing and I have it in the form load event of DbNew. This however returns an error (Runtime Error 3010. That TblNew already exists)
Dim db As Database
Set db = OpenDatabase("\networkdbOld.mdb")
db.Execute ("Coversheet")
DoEvents
Set db = Nothing
End Sub
P/S: I am not good at access in anyway I should state.
Thank you for taking the time to read.
View 8 Replies
View Related
Mar 8, 2006
I've set up a query to calculate the difference between 2 time fields. I set the property to short time.
The thing is - when I run the query in a Select query, the times in the column which contains the difference, appear correctly : ie hh:mm, but when I run the same query in as a make table query, the time difference field (although I have set the property to short time) appear something like this:
0.664464564646
Can somebody please tell me how to make it that the time difference results field in my make table query appears as hh:mm??
Thanks in advance - nelld
View 1 Replies
View Related
Jan 30, 2007
I have data recorded using an input mask,
"RF">L-0000;;*
to display data in the style: RFA-0001, RFA-0002 etc.
I have a make table query to join this field to another, to create a combo box look up.
Unfortunately, after I run the query, the only data from the input mask that gets imported is A0001, A0002 etc.
What have I done wrong? I guess the error must be in my specification for the input mask...
Any and all help gratefully received.
View 4 Replies
View Related
Jun 24, 2013
I have qry with these fields: DateOfPayment and Ammount.
I would like to add another field with running total sum. I am trying this:
RunnTot: Format(DSum("[Ammount]";"qryCFSUM"; [DateOfPayment] <=#" & [DateOfPayment] & "#" );"0 000"" Kč""").
But It still does not work.
Example of my data in "qryCFSUM":
DateOfPayment
20.1.2013
31.1.2013
30.3.2013
Amount
1 2000 Kč
15 456 Kč
23 465 Kč
And what I would like to have:
RunnTot
1 200 Kč
16 656 Kč
40 121 Kč
View 14 Replies
View Related
Oct 12, 2013
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 attaching the database, with sample data.
View 1 Replies
View Related
Oct 5, 2013
I want to calculate running total and find out the date when that total is greater than a number.
My initial plan was to use Dsum and then use dlookup to find when that Dsum value > [Fixednum].
But when I try Dsum and use Totals in query, access shuts down. maybe because of 15000 rows.
I have attached a sample database that shows what Im working with and what I would like.
View 2 Replies
View Related
Apr 2, 2015
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.
Date Time Item Cube RunningTotalCube
4-2-15 12:05 15615 0.5 0.5
4-2-15 12:06 15918 0.8 1.3
4-2-15 12:10 98563 0.5 1.8
4-2-15 12:12 45268 0.4 2.2
4-2-15 12:15 25854 0.9 0.9 {reset}
4-2-15 12:17 75136 0.5 1.4
Is this possible either in the query or the report/in Access or in VBA?
View 3 Replies
View Related
Oct 23, 2013
I have a quick (hopefully) query about adding up consecutive values to get a running total.
Currently I have the following
ID Value
1 0
2 5
3 2
4 0
5 1
6 30
7 2
etc...
I am looking for a way to get a running total up to the point of a 0 then restarting. e.g.
ID Value Running total
1 0 0
2 5 5
3 2 7
4 0 0
5 1 1
6 30 31
7 2 33
View 12 Replies
View Related
Mar 21, 2014
I am having an issue with my running total query.
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;
View 4 Replies
View Related
Jul 1, 2013
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.
View 10 Replies
View Related
Jun 19, 2013
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.
Therefore the running totals should read:
scode | Date | po_qty | RunningTotal
10254 | 01/06/2013 | 40032 | 40032
10254 | 01/09/2013 | 30096 | 70128
As you can see from the attached DB I have 70128 repeated twice in the RunningTotal column.
View 8 Replies
View Related
Dec 11, 2014
I know a fair bit about access 2010. I have the following table
------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 1 ----- fly rod ---- 19/01/2014 ----
- 123 ------- 2 ----- fly rod ---- 19/03/2014 ----
- 123 ------- 3 ----- fly rod ---- 19/05/2014 ----
- 123 ------- 4 ----- fly rod ---- 19/06/2014 ----
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 1 ----- fly reel ---- 11/01/2014 ----
- 633 ------- 2 ----- fly reel ---- 11/03/2014 ----
- 633 ------- 3 ----- fly reel ---- 11/05/2014 ----
- 633 ------- 4 ----- fly reel ---- 11/06/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------
I need it to give me back, one row per product (product no) - the newest date (Date Last Sold)
So i am trying to find out the last time each product sold, regardless of where
Which i can do with MAX date, group by product num
SELECT SMKPLUDetail.[Product No], Last(SMKPLUDetail.[Branch ID]) AS [LastOfBranch ID], Last(SMKPLUDetail.[POS Description]) AS [LastOfPOS Description], Max(SMKPLUDetail.[Date Last Sold]) AS [MaxOfDate Last Sold]
FROM SMKPLUDetail
GROUP BY SMKPLUDetail.[Product No];
------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------
This is great, but ofcourse (last of is fine for description) as is does not change
But last of, always returns branch 11.. and i infact want the branch which is correct, the branch which the date last sold is from, the current record really
but i do not seem to be able to get it to show me, any other fields from the querry current row.
View 3 Replies
View Related
Mar 12, 2007
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..
I have a table which looks:
RegionCategoryTypeDesc Period_IDPeriod_YTDPeriodTotal
CanadaEventsWSOP Team67Budget15000
CanadaEventsWSOP Team78Budget0
CanadaEventsWSOP Team89Budget0
CanadaEventsWSOP Team910Budget0
CanadaEventsWSOP Team1011Budget0
CanadaEventsWSOP Team1112Budget0
CanadaEventsWSOP Team1213Budget0
CanadaEventsTOTALAll12Budget15000
CanadaEventsTOTALAll23Budget15000
CanadaEventsTOTALAll34Budget15000
CanadaEventsTOTALAll45Budget15000
CanadaEventsTOTALAll56Budget15000
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.
Any helpo really appreciated.
Simon
View 1 Replies
View Related
Aug 22, 2007
Hi,
Am trying to create a query for a chart where I can total the employees over time but am having real trouble creating a running total from the "Total" field within a query but cannot seem to get it at all.
TotalStartDateLeftDate RunningTotal
126/03/1957
121/03/1971
127/02/1986
115/02/1988
207/03/1988
007/03/198831/05/2007
Here is my current SQL query:
SELECT Sum([CountOfStartDate]-[CountOfLeftDate]) AS Total, Atest1.StartDate, Atest1.LeftDate, Sum([CountOfStartDate]-[CountOfLeftDate]) AS RunningTotal
FROM Atest1
GROUP BY Atest1.StartDate, Atest1.LeftDate;
Can anyone help please?
View 5 Replies
View Related
Mar 10, 2008
This should be easy! Right?
I have a series of dates with events that occured on those dates. Some events were extended, others were not how do I get a running total, cumulative total, for all records in the RunTotal column?
Opened DateOpen IssuesCountOfExtendedNotExtRunTotal
5/21/2007 1 10 1
8/6/2007 1 10 2
10/8/2007 1 10 3
11/1/2007 1 10 4
11/8/2007 1 01 5
12/5/2007 1 0 1 6
Thanks for your help.
View 6 Replies
View Related
Jul 2, 2015
I am trying to create a query that ONLY shows me the total count of each combo box choice for one field. It sounds very easy and I'm sure it is but everything I try tells me I am using the wrong syntax, or requires me to have rows when I only want column totals.
I have a combo box with a dropdown of 10 common places that people travel to. I want the report to ONLY show the total number of people who chose place 1, place 2, place 3... etc. But I always have to do according to date or time or something. I want it to look like this
Place 1 Place 2 Place 3 Place 4
13............36.........3........77
So essentially there would only be one row. But access will not let me. Is there something I'm doing wrong? Or is this not possible?
View 8 Replies
View Related
Jun 11, 2015
I have an access form (Customer) along with a sub-form (Work_done). On sub-form I have Running sum of Amount in Text box-1. I want that Total of Running sum be replaced with Main form's tables field total_amount. Is it possible that we can replace an amount of control of sub-form into main form's field?
View 1 Replies
View Related
Apr 16, 2013
I have now a database where I made a query that will show me all clients I can charge when there is a end date and an account number mentioned. I have also in a table where I put if the client is already charged. This is a check box that gives me yes or no. Is it possible to run the query and once those selected show up, to also add a yes to the table so next time I run the query the ones charged will not be charged again.
View 5 Replies
View Related
Apr 13, 2006
I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:
View 3 Replies
View Related
Mar 14, 2005
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!!
Many thanks in advance.
Peter
View 2 Replies
View Related
