Use A Query To Do A Running Total & Comparison With Weekly Data Points
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 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
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
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
Oct 26, 2006
Hello my beautiful worldwide friends :D
I am trying to calculate employee Overtime hours from their recorded TimeIn and TimeOut if over 7.5 hours. So anyday they work past 7.5 hours should be calculated and totalled at the end of the week. And i can't seem to figure it out, maybe my maths is bad? Maybe it's my query? The section of my report? What could i be doing wrong here, i have spent hours and im getting no where.
I have attached my report.
Can you help me figure out why my daily total shows but the weekly total is not showing?
View 9 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
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 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
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
Jun 5, 2013
Query of daily activities spent hours
1) to be group as weekly total
2) then need to transpose it for reporting.
My table fields are Date, SpentHrs and Code (activity code description).
View 11 Replies
View Related
Dec 4, 2007
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.
so my new table would be:
ID DATE CAPACITY ORDERS CAPRUN ORDRUN
1 01/01/2007 250000 250000 250000 250000
2 02/01/2007 250000 300000 500000 550000
3 03/01/2007 250000 300000 750000 850000
4 04/01/2007 250000 300000 1000000 1150000
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.
View 14 Replies
View Related
Dec 27, 2004
Hi
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?
Thanks
Tom
View 9 Replies
View Related
Mar 28, 2006
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.
Any help is appreciated
View 2 Replies
View Related
Sep 11, 2005
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.
yallah.
aliyallah@yahoo.com
View 2 Replies
View Related
Feb 26, 2007
Hi guys
I am making a query that calculates how much costs i have per job. I'd like to create a function in the query that can calculate how big a percentage each job is. (need total for every job/ total for all jobs) but so far i haven't been succesful in this.
Anyone who could help me?
Cheers,
Takstein
View 3 Replies
View Related
Mar 2, 2007
I have a table called Team Standing that includes [TEAM] [PLAYER] [GAME DATE] [POINTS]. From this table I have a query that provides for each game date and team a count of the players, [GAME DATE] [TEAM] [PLAYER COUNT]. [PLAYER COUNT] is calculated using the total COUNT .
What I would like to add is a cumulative running total over the game dates. For example.
[GAME DATE] [TEAM] [PLAYER COUNT] [TOTAL PLAYER COUNT]
2007/01/05 --- 1 ------- 11 -------------- 11
2007/01/12 --- 1 ------- 8 -------------- 19
2007/01/19 --- 1 ------- 14 --------------- 33
I am having trouble figuring out how to get a cumulative running total. I have tried a number of different sub-queries and selects but I can’t seem to get something that works.
Any help or ideas would be greatly appreciated.
Thanks
View 3 Replies
View Related
Sep 16, 2007
I've been tasked to create a report that shows the date an employee hit a loss of $200.00 or greater for the company. Each day they work, they will either have an overage or a shortage in their till fund. They start out each day with a set amount of money in their till and at the end of the day they are expected to turn in that same amount; the tills are used for making change. If they are short, and the amount is fairly large, hence the $200.00 mark, then it becomes an issue that needs investigating.
I need to total these amounts up and when the total hits a negative $200.00 or greater, I need to display the date it occurred and then display the amount the company would have saved if the employee had been either terminated or transferred to a non-revenue department.
Getting the "saved" amount should not be a problem once I figure out how to get the above indicated loss amount.
Any ideas?
Kevin
View 3 Replies
View Related
Oct 10, 2007
Hello, I am new so if I am posting in the wrong board, please feel free to move this.
My question is as follows:
I have several tables set up to track the grants my office adminsters. These include Project Information, and Payments. I am attempting to keep a running total (in each funding source) that shows up in the Payments form. I am not sure how to build the query for this.
Example:
Grant A has the following funding:
$20,000 -Capital Projects FY04
$50,000-Federal Funds FY06
The following payments have been made (each payment would be a seperate record:
$1,000- Cap FY04
$2,000- Fed FY06
$4,000-Cap FY04
$8,000-Fed FY06
Suggestions?
PS-I am really not that great with VB so if you could explain it as if I were 5 that would be great
View 3 Replies
View Related
Apr 22, 2008
Hi,
i'm working on a report about firm's personal. i have to calculate a column as running total. But i could not.
Please help me.
i am sending my file.
best regards.
View 4 Replies
View Related
Oct 23, 2005
I have a form that calculates number of hours each month. So a little box for Jan, Feb, etc.
At the bottom I have another field that sums it up. Well Im using just basic + to add the fields and it only works where there is something in each box.
I can't go through and put zeros in for everyone since a 0 and a blank mean two different things. I tried Dsum and just now after searching the forum, I tried nz.
Am I overlooking a real nice function that would assume a blank entry in the sum is a zero? or perhaps some trick I could try? Also, is there anyway to store the calculated total in the associated table, so I can query it later? It already has its own specific field that up until now was hand calculated and entered manually...like the monthly hours.
Thanks in advance
View 3 Replies
View Related
Oct 31, 2006
I have a form that has a "Quanity" field on it as well as a "# Times Printed" field.
I'd like to have the "# Times Printed" be a running total of the "Quantity" field where everytime a "Quantity" is entered, it updates the "# Times Printed"
I've played around with a few idea's but haven't had much luck.
Thanks
Scott :)
View 1 Replies
View Related
Oct 13, 2004
I am trying to put together this report where I can see how many hours the printers were running in a month's time. The sum property for this is working up until it reaches 24 hours then it starts all over again. How can I get it to keep adding hours when it gets to 24?
For example: 20:35 + 6:25 = 3:00
learnasugo
View 3 Replies
View Related
Jan 11, 2008
I need HELP PLEASE!
I have created a table from importing the data from an Excel spreadsheet. Then I created a simple query using the wizard and adding all the fields that the table had into the new query.
Here is the problem...
I created a couple of expressions using the expression builder for a couple of the fields and I am stuck on how to create an expression for a running balance column.
For example: I have a QTY column (not calculated), a Price 1 column (not calculated), another Price 2 column (calculated with an "If" expression used to look-up a code in one column and if it meets the criteria then it would return a different price than what is in the Price1 column, and a Subtotal column. I need a Balance column (which would start with, for example, $10.00 and then deduct the subtotal amount.
QTY Price1 Price2 Subtotal Balance
- - - - $10.00
1 1.00 - 1.00 $9.00
2 1.00 2.00 $7.00
1 - 2.00 2.00 $5.00
I need very detailed, but very simple directions and fast. I have tried DSum (doesn't work) and every expression I could think of. I need this done in the query I started or in a new query. I don't have Access on this computer, so please sent directions versus a sample database.
Please Help...
View 10 Replies
View Related
Jan 27, 2014
what I think is a running total for each day. I have a form that collects data several times in the day. I want a report to show a total for each day and subtotals for each column and grand totals. I can add the dates and used a running total to the report but the report will show all the entries made for the day not a single total for the day. (I hope I said the correctly.) This needs to be done for all columns. I'm not sure what I'm doing wrong. I've attached what I have so far.
View 2 Replies
View Related
