Modules & VBA :: Running Total Based On Set Criteria

Oct 30, 2013

I have a query I am trying to build currently which creates a running total based on set criteria. I essentially have three columns (All times are in minutes). The visit ID, the duration of a visit and the time difference between the current and previous visit. e.g.

ID Duration Difference
1 30 0
2 45 10
3 15 20
4 60 10
5 15 10

What I would like to achieve is the following:

ID Duration Difference running total
1 30 0 30
2 45 10 85
3 15 20 15
4 60 10 85
5 15 10 110

So a line will have the calculation of the current time + the difference between the current and previous visit if the difference is below 20. I have the following:

Code:
Public Function Cont20(MyVal As Long, MyDif As Long) As Long
Static OldValue As Long
Dim NewValue As Long
If MyDif >= 20 Then
NewValue = MyVal
OldValue = 0

[Code]....

Which I put as Cont20([Duration],[Difference])

The problem seems to be on the return aspect, its not calculating correctly. I had the formula in Excel which worked great, but this is proving troublesome. As a note the Excel formula was such that it would add the previous running total and then add the current duration and current diff. e.g. In cell A7 the formula would be A6+B7+C7 and so on.

View Replies


ADVERTISEMENT

Modules & VBA :: Running Total With Criteria Using UDF

Aug 5, 2015

I have determined the solution to my problem lies outside of normal SQL queries and I need to create a UDF.

Here is the problem. I need to create a running total based on the sum of two fields [PTS_ISSUED] & [PTS_REDUCED] with one restriction. The total can never be less than 0. If it is less than zero, the totals reset with [PTS_ISSUED] as the new starting point.

I've attached an excel spreadsheet. Column"C" contains the formula logic I am trying to replicate into access.

I will be honest, writing the UDF is beyond my current knowledge but once I see it I am able to understand it and implement it.

I believe the solution needs to store the running total into a variable, make the necessary comparisons and determine if its okay to add the [PTS_ISSUED and [PTS_REDUCED] to the stored total of the previous calculations then advance. I just have no clue in how to go about writing it.

This calculation needs to be presented in Access because the results will drive several other functions within the database.

View 8 Replies View Related

Modules & VBA :: Total Based On Combination Of Criteria - Compound If Statement

Aug 15, 2013

I'm trying to get a total based a combination of criteria. Here's my issue:

If ([Vendor Billed Amount] > 650 $ and $ [Excess Fee Approved] = "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
If ([Vendor Billed Amount] < 650 $ and $ [Excess Fee Approved] <> "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
endif
endif

I think I'm missing an argument with the nested If statement.

View 3 Replies View Related

Problem Running A Query Based On Date Criteria Over Previous 7 Day

Mar 19, 2008

Hello All,

I do hope somebody can help this newbie :)

Please except apologies if I sound a bit ignorant with this but I'm complete novice with Access...

Basically I have a table with the fields Name - Date - Location I need to extract info from the table based on the location field.

i.e. return Name if a specified location exists and another specified location does not. This was real pain in itself and in the end I had to run two seperate queries which returned NAmes where each of the chosen locations exist. I've then created an additional query which compares the two sub-queries and iliminates Names that appear in sub query 2.

I do hope this make's sense I'm starting to confuse myself.....

Anyway, my problem is with the date field... I have to specify a date in order to get the correct info as the results may change on a daily basis... i.e. somebody may use both locations one day, but just the one location the next. However the eventual report which I need to generate is based on criteria over the previous 7 days, although if I use this criteria in my query in completely messes up the results.

So, what I was hoping that I could do is somehow automate my query to run 7 times (once for each of the previous 7 days) and then to combine the results of the 7 queries into a single query or report...

Failing that, could anybody think of any simple way of achieving this... or will be a case of having settle for a seperate report for each day?

Any help would be hugely appreciated... once again apologies for the explaination... It porobably makes no sense at all.

Regards,

Andrew

View 2 Replies View Related

Forms :: Running A Query Based On User Selection Criteria On Form

Jan 9, 2014

I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.

What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?

I'm thinking about utilising the check/tick boxes would this be possible? or another way?

I'm using 2003 and have some VBA ability.

View 5 Replies View Related

Queries :: Self-Referencing Running Total Used To Calculate Next Total In A Query

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

Queries :: Using Query To Place Single Grand Total On Row Based On Particular Criteria?

Dec 7, 2014

I have a very simple query on an accounts form to show a running transaction history.

Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.

Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.

Transaction 9 will have obviously have one total, as this is a single post.

11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.

View 4 Replies View Related

Queries :: Running Query Based On Form Text Box Criteria With Special Features

Dec 18, 2013

I am trying to run a query and display the results in a report (the report side of it is childs play and not a problem). The problem I am having is that I have a search form which should allow the user to search any one of 6 fields (text boxes) or a combination of each.

If the user enters something into a field then that search criteria must match. I wanted to have it so if all fields are left blank then it will show all entries in the database (but it isn't, it shows a blank report). I also wanted it to allow partial completion of boxes.

So for instance if I have 5 customers (Jones, Jonson, Jonus, jimjonkins, Janis) and I type "Jon" into the name field then I would like it to show the first 4 records as they all contain "jon" somewhere in their name but its not, its only allowing exact matches.

I currently have '[forms]![Search_Customer]![Search_Name]'.

View 2 Replies View Related

Running Balance As Opposed To Running Total

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

Modules & VBA :: Label To Display Total Of Column In Table Based On Two Checks

Jul 8, 2013

I am looking to generate a total number of a given column based on two criteria.I would like a label (or textbox if necessary) to display a given total of "Active" devices based on a given month.The table name is "Blackberry" and the specific columns I would like to check would be "Activated?" and "Registration Date". I'm not sure if I should be using a dsum or dcount and the error i keep getting is a type mismatch.an active device would have the text value of "Yes.

Dim advalue As Integer
advalue = DCount("[Activated?]", "Blackberry", "[Activated?]=yes" And "[Registration Date] > #01/01/2000#")
lblad.Caption = advalue

View 5 Replies View Related

Running Total

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

Running Total

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

Running Total

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

How To Maintain A Running Total

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

Running Total In Query

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

Percentage And Running Total

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

Trouble Getting A Running Total

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

Running Total Query

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

Running Total Per Employee

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

Running Total For Grants

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

Query With A Running Total

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

About Dsum Or Running Total.

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

Running Total In Forms

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

Running Total Question???

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

Running Time Total

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

Running Balance Or Total ???

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







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