I have a query, and I want a field that shows the current balance as of that transaction, eg:
So far, I have this:
SELECT [Transaction ID], [Type], [Amount], (SELECT Sum([Amount]) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[Transaction ID] <= tblTransactions.[Transaction ID]) AS Balance
WHERE [Account ID]=1;
Although this does not look at the 'Type' field; it just adds the amounts; regardless of it being a deposit or withdrawal. I'm really not sure how to add this.
I have a report with a calculated field. This calculated field needs to pull the value or total from a field in another report. I think the formula is : =([AssmntC].Reports!totalreqamnt4) but I'm getting ?Name as the result. The report is AssmntB where I need to have the value copied. The original report is AssmntC and the field is totalreqamnt4 where the value is originally calculated. totalreqmant4 is also a calculated field which sums fields from a query.
I am new to this forum and relatively new to MSaccess, though I do have a limited amount of knowledge.
I have a created a DB in MSAcess (2000) that links to a table in a third party piece of software using the ODBC facility. I have replicated the linked table within the database (and named it tblNominal) to one that I can change properties and add columns. I have created a new table that links on a one to many relationship to the replicated table, (called tblGroup) that groups records together in another table.
Using the make table Query facility within MSAccess I am able to create a table that looks like below which summarises the tblNominal:-
Group Description Current Yr Budget Prev Yr 10.......Sales......................369........... .....1368.......2367 20.......Cost Of Sale.............196.................185.........1 83 30.......Overheads.................87............. ......84..........74
So Far so good
When I try to create a report based on the above table the Sales, Cost Of Sales, and Overheads appear in the detail section of the report but I cannot seem to add a text box on the report that calculates a sub total
I am (trying) to create a report that can perform the following Calculation (Sales - Cost Of Sales (10 - 20)) for Current Yr Budget Prev Yr. Desired Report layout:
Group.............Description........Current Yr..........Budget........Prev Yr 10.................Sales...................369.... .............1368............2367 20.................Cost Of Sale..........196..................185............ .183 [Calc field]......Gross Profit............173 ................1183.........2184 30.................Overheads...............87..... ..............84.............74 [Calc field].......Net Profit...............86.................1099...... ....2110
If someone offer advice on how to achieve a solution to the above I would be grateful.
Hi Although not new to UA, this is my first post and would appreciate any help I am using the Order Entry db template for my billing database. I have modified various fields and tables to suit my needs and all works great but I am interested to find out if a calculted total [Total Due] from a previous invoice can be shown on the latest invoice. As it stands a part payment can be assigned to a particular invoice and the total due will show the amount owing but the current months invoice won't show the unpaid amount. It can be got around by reprinting the old invoice and sending it out with the new invoice but I would rather have one invoice showing all that is owing. Thanks Ian
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.
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?
I have a report based on qryCollateralMerge that looks like a merged letter with a list - the detail - in the middle. I've been using textboxes, some with variables inserted, for the paragraphs, but I have a paragraph I'd like to exclude if a calculated total = 0.
I can calculate the variable ok, but when I've tried creating the if statement on a form, in the textbox on the report and in the On Open event and no luck. Even though the calculated total is a subset of the query, if I try and use grouping, then I get two addresses (one of the tables in the qryCollateralMerge).
Here's my paragraph without the if statement: ="Buyer and Assignee agree to an advance of " & CStr(FormatCurrency(DSum("[Price]","tblCollateral","[QuoteID] = Forms![frmAWF]!QuoteID and [CollateralType] = 'Advance'"))) & " payable to or on behalf of the Buyer. "
I've searched UA and googled to no avail. AHHHHH!!!
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.
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.
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]
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.
SELECT Sum([CountOfStartDate]-[CountOfLeftDate]) AS Total, Atest1.StartDate, Atest1.LeftDate, Sum([CountOfStartDate]-[CountOfLeftDate]) AS RunningTotal FROM Atest1 GROUP BY Atest1.StartDate, Atest1.LeftDate;
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.
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
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.
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?
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.
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.
We think our issue is that Field1 is text, where it is numeric in the example. When we change nothing else, but make Field 1 a number, it works like a charm. We have tried it with and without Format() as is give in the MS example.
Can anyone help me alter this expression so that it will work with an alphanumeric field? Thank you.
I have to impose a single monthly fee on a billing report for each month that transpires and capture this fee as a running total on this report, which will eventually span the course of years. Understanding that this rate is variable, what is the best way to accurately capture this information?
High everyone I've had a look around the FAQ's and other threads but haven't been able to find what I'm looking for.
I have a table that contains two fields, one being 'part number' and the other being 'quantity'. Unfortunately there are multiple duplicate 'part numbers'. I am looking at keeping only one instance of the duplicate 'part numbers' but at the same time I want to add the 'quantities' together to keep a running total.
Before Part Number Quantity a1 1 a2 2 a3 2 a1 1 a2 1 a1 4 a1 1
here is the info needed table name is employee time and fields are employee name, date, total hours. i need a running total until 2000 hours are reached and then i need it reset to 0. also any hours over the 2000 need to be added to the 0 in other words if on a wednesday the employee reaches 2006 then i need to start at 6 instead of 0 otherwise if it is exactly 2000 i need it go to 0. can anyone please help.
Hello, I have 4 input field boxes in a form: "basic hours", "overtime hours", "holiday hours" and "total hours" i would like it so that when the user enters number(s) into the other field(s) the total field will calculate (add togther the other fields). So for example if the user puts "10" in the "basic hours" input field and "5" in the overtime hours" field then the "toal field" will display the number "15". i would like it so that the "total hours" field calculates the total progressively after each number in the other fields is input (ie a running total is displayed). The user will always fill in basic hours but 1 or more of the other fields can be left blank. I think i need to do some VB on the after update procedure for each field entry but not sure what the code is?, any help would be brilliant.
I have a form that I am using to enter data from cost estimates….
Item Ctr1 Ctr2 Total Labor 123 222 345 Materials 555 122 677 Total 678 333 1122
All of the ‘Total’ Figures are calculated on the form in their own fields – ie TotalLabor – ControlSource =[LaborCt1]+[LaborCt2] And so on… with totals also being calculated for each column….
Should I have created these ‘TotalLabor’ ‘TotalMaterials’ etc by using a total query, or is it ok to have them calculated here? Beyond the ‘running total’ that I need below, I don’t need these numbers for anything else – right now.
What I am trying to do is get a total of Contractor1 Labor costs across all estimates, Materials across all estimates, total of totals…. I just want everything added up.
So, I thought that the way to do this was maybe using Dsum.. I created new blank form, with only one field to test the calculation, but I keep getting a #Name error. =DSum(Forms!FrmSingleContract!txtCalcGovtCosts,Forms!FrmSingleContract,[«criteria»])
the 'txtCalcGovtCosts' field represents the total costs of contractor1 Can you guide me in the right direction?
I've been reading over all the examples I can find and I am not making a lot of headway. In one table we have charges and payments. They can be on any of a number of accounts. Like the "checkbook" request I found, there can be several on the same day and it is likely that though "payment #5" on "account 2" might be dated for today, I might find an expense I have to charge on an account that is for a previous date.
In what I attempted, if I only have one account I could use the date condition and at least get by with it, or use the condition area for the account number and then everything would be the total for the account (where I really rather a true running balance for historical reasons), and if I dare combine the two conditions (account number AND date) I got complete nonsense.In my query the account field came before the date, so I thought I could throw in first an account condition and then using logical AND the date condition, but un-unh.
Does anyone know of an example that resembles one table for more than one checking-like account that a form can be pulled from which presumably based on a query provides the payments for one account sorted by date with a running balance, or do I have to attack this from another angle like somehow generating a dataset in memory for just one account number and then sorting it (which seems to me becomes more of a report than a table and would create more involvement if I had to edit items)?