Calculate Balance Query
Aug 22, 2007
Hi All.
I have Request, Device and Receive table. All table has DeviceID. I create query to calculate total amount of each type of devices which was requested by link Request and Device tables. And query to calculate total amount of each type of devices which was received by link Receive and Device tables. I would like to create Balance query to calculate:
Total_Receive - Total_Request = Balance
If devices in Request and Receive table is the same result is correct. But if in Receive table add new type of devices I cannot see amount of new device in Balance query. How to fix that problem?
Thanks.
View Replies
ADVERTISEMENT
Sep 3, 2012
my details input as per below;
1) table 'Receiving' (Part#,Qty received,date received)
2) table 'Issuance' (Part#,Qty issued, date issued)
How to calculate current stock on-hand?
*previously this report are using ms excel table. i just tried to convert into access since i will have a better view, will a separate form to fill up, will have a report to pull in(hopefully... current on hand )
View 11 Replies
View Related
Mar 3, 2014
I'm currently stuck on trying to create a worksheet which calculate the balance remaining at the end of each Monthly Payment Period.
Monthly Payment Period: 1 to 300
Initial Balance: balance outstanding at the beginning of the Monthly Payment Period.
Interest: calculated by multiplying the Initial Balance by the APR (5%) divided by 12
Balance Remaining: calculated by adding the Interest and subtracting Total Monthly Payment.
The final Balance Remaining should be exactly £0.00.
View 1 Replies
View Related
Mar 3, 2014
I'm currently stuck on trying to create a worksheet which calculate the balance remaining at the end of each Monthly Payment Period.
Monthly Payment Period: 1 to 300
Initial Balance: balance outstanding at the beginning of the Monthly Payment Period.
Interest: calculated by multiplying the Initial Balance by the APR (5%) divided by 12
Balance Remaining: calculated by adding the Interest and subtracting Total Monthly Payment.
The final Balance Remaining should be exactly £0.00.
View 6 Replies
View Related
Mar 13, 2006
I have a query that have three columns invoice number, tran number and amount. What I would like to do is to sum the invoice number if they are equal. example:
new column
Invoice #: 1345652 50.00 50.00
Invoice #: 1345652 65.00 115.00
I have the same invoice multiple times, but the transaction number is different. so I need to SUM all the invoices that are the same.
Please help!!!!!!!
View 5 Replies
View Related
Nov 6, 2007
I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT
What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
View 5 Replies
View Related
Feb 17, 2008
Can someone help me please???:rolleyes: I have to query for the avg balance from customer table, custbal field from a certain state... I've tried everything and cannot seem to get the expression correct. How do I do this???? any help is appricated :D
View 2 Replies
View Related
Nov 6, 2007
I am trying to run a query to select the most recent balance from a table that lists balance by date and account number. Basically I need to select the most recent balance for a given account number. Here are the details:
Table: CBALANCE
=========================
ACCOUNTNO
BDATE
AMOUNT
What works: This gives me the most recent account, but I cannot get the amount
=========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO;
What I want to work: This returns results which should not be in the result set (older balances)
==========================
SELECT Max(CBALANCEMIB.BDATE), CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT
FROM CBALANCEMIB
GROUP BY CBALANCEMIB.ACCOUNTNO, CBALANCE.AMOUNT;
Am I trying to do something too complicated for the query language?
View 6 Replies
View Related
Feb 22, 2015
How to build query to give daily balance across bank accounts? (to then plot in a graph)
Assumptions:
* There is a table TRANSACTIONS which includes columns TRANS_DATE, AMOUNT and BANK_ID. It does NOT include a column for balance. So current balance for a bank account is the sum of the AMOUNTs for that BANK_ID for example. Balance on date XX will be the sum of all AMOUNTS for that BANK_ID for all TRANS_DATE's prior and including the date XX.
* Table BANKS which has BANK_ID and TITLE
Would like a query that gives: Supply StartDate and EndDate for the query:
Date Bank1Balance Bank2Balance Bank3Balance TotalBalance
1/1/15 $100 $200 $100 $400
1/2/15 $200 $200 $100 $500
.
.
.
etc
View 1 Replies
View Related
Jul 25, 2013
I have an update query for tGLCashAccount where it adds a value from another table with the BeginningBalance to arrive at CurrentBalance.
Here's what it looks like in design view:
Field: CurrentBalance
Table: tGLCashAcct
Update to: [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
Here is SQL code:
UPDATE tGLCashAcct, tMakeNewCashBal SET tGLCashAcct.CurrentBalance = [tMakeNewCashBal].[TotalPrice]+[tGLCashAcct].[BeginningBalance]
WHERE (((tGLCashAcct.GLCashAcctID)="102"));
I get the error: data type mismatch in criteria expression when I run it.
View 3 Replies
View Related
Jul 15, 2014
I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?
View 9 Replies
View Related
Aug 7, 2005
Hi
I am creating rent software and I would like to do a running balance in a report.
In the table there is a field called amountPlus and amountMinus
Record one might have $10 in amountPlus making the balance $10
Record two might have $30 in amountPlus making the balance $40
Record three might have $20 in amountMinus making the balance $20
Having both fields is critical as they are displayed in the DR and CR columns respectively on the report.
(yes I know I could have called the fields DR and CR but I am financially stupid).
The report is displayed in date order, but the transactions are not necessarily entered in date order.
How can I do a running balance on a report?
It needs to take the previous balance, and either add or subtract the value depending on if the value is sitting in the DR column or CR column.
Please give ideas.
Thank you in advance
View 2 Replies
View Related
Jul 30, 2005
I have a table of three columns (although there are other non-currency columns in this table) but I would like the balance column to show the balance of buget column less total expenditures:
Budget Total Expenditures Balance
Thanks.
View 10 Replies
View Related
Jun 17, 2005
hi all,
i m having a problem with a simple inventory system. The problem is that i have a query which calculates sum of 'Debit and credit' as 'Balance1' for each row. Now what i want is to add every 'Balance1' to next sum of 'Debit and credit'. e.g.
Debit Credit Balance1 Balance2
a b a+b (a+b)
c d c+d c+d+(a+b)
e f e+f e+f+c+d+(a+b)
I would to generate column 'balance2
Any help would highly be appreciated.
Thanks
View 4 Replies
View Related
Apr 11, 2006
I have a banking system, and I need to be able to show the balance as each transaction is made. This is what I have so far:
SELECT tblTransactions.lngTransactionID, tblTransactions.lngAccountID, tblTransactions.datDate, tblTransactions.txtType, tblTransactions.curAmount, (SELECT Sum(Iif([txtType] = "Deposit", [curAmount], 0)) - Sum(Iif([txtType] = "Withdrawal", [curAmount], 0)) FROM tblTransactions AS tblTransactions2 WHERE tblTransactions2.[lngTransactionID] <= tblTransactions.[lngTransactionID] = curAmount) AS Balance
FROM tblTransactions
WHERE (((tblTransactions.lngAccountID)=[AccID?]));
Although the WHERE clause is just for testing, in the real form, I am using a filter to only show records for a particular account.
The problem with this code (I think) is that the subquery is carried out before the main query's WHERE clause, so the balance is calculated on all records, not just the ones for a particular user - so if User A deposits £10, then User B deposits £10, User B's balance would show as £20, which is not the case. I have done some research, and I think the soloution might be in 'Correlated Subqueries', although I am finding learning about them a bit out of my depth.
Please help, thanks.
View 6 Replies
View Related
Nov 12, 2007
Hi
I haven't been on for a while. so hello everybody.
I am trying to keep a subtotal in a table. there is an index (actually its formatted text, but its a number and i can converted i.e. cdbl()). I was thinking that i should create one query with dmax in one column and and then use another one to show the value plus the previous value.
can anybody help me out please?
thanks,
Sam
View 6 Replies
View Related
Jul 2, 2005
Hi,
ive created a balance shee for a football club as a continuous form with a balance being displayed in the footer along with total income and expenditure.
Because this is for a Football Club, I want to be able to do the balance sheets every season. To that end I have a included a date in the query.
Is there a way that I can carry a balance forward to the nesxt season, (next record). I want to display Last Seasons closing balance in the header of the new season and have the new balance include that?
View 12 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
Feb 28, 2015
On the below data i am trying to get average monthly BALANCE, based on the following query ....but i only getting all records with actual balance and not month wise average balance of the customers......
-----------------------------------
SELECT [customer profit].[Customer Number], AVG ([customer profit].[VAL_BAL]) AS average, Month ([customer profit].[Balance Date])
FROM [customer profit]
GROUP BY [customer profit].[customer Number], [customer profit].[VAL_BAL], [customer profit].[Balance Date];
[Code] .....
View 4 Replies
View Related
Jun 12, 2013
I want to add arrears(balance into next month arrears when i insert new month fees.
INSERT INTO Fees ( [GR No], Class, Tuition, FMonth, TMonth, [year], Transport,arrears)
SELECT [Fee Student].[GR No], [Fee Student].Class, [Fee Student].Tuition*2 AS Tuition, [fee insert].FMonth, [fee insert].TMonth, [fee insert].year, [Fee Student].Transport, Arrears
FROM [fee insert], [Fee Student];
For Example:
May-May: Total fees=10000 Paid=9000 Arrears = 0 Balance=1000
Jun-Jul: Total fees=10000 Paid=8000 Arrears = 1000 Balance=3000
Now want to insert that month and want to insert arrear with total fees.
Aug-Sep: Total fees=10000 Paid= Arrears = 3000 Balance=
Slight changes in that query:
INSERT INTO Fees ( [GR No], Class, Tuition, FMonth, TMonth, [year], Transport, Arrears )
SELECT [Fee Student].[GR No], [Fee Student].Class, [Fee Student].Tuition*2 AS Tuition, [fee insert].FMonth, [fee insert].TMonth, [fee insert].Year, [Fee Student].Transport, (admission+registration+security+annual+id+exam+st ationary+tuition+fees.transport)-paid AS Expr1
FROM [fee insert], Fees INNER JOIN [Fee Student] ON Fees.[GR No] = [Fee Student].[GR No];
View 3 Replies
View Related
May 3, 2006
Hello:
I have read the Microsoft article at http://support.microsoft.com/kb/q95918/ several times. However, I am looking for an expression I can place in the field name to calcluate the median of another field in the same query. Is this possible? If not, I don't understand how to call the function created in the Microsoft article.
Example (fields in the query)
Expr1
Expr2:Median(Expr1)
Thanks in advance.
View 14 Replies
View Related
Jun 29, 2006
i would like to run a query that works out the busiest day in a month but i also want to run a range from two dates and be able to convert them to
mon ? entries
tue ? entries
and so on
is this possible?
many thanks
michelle
View 1 Replies
View Related
Jun 5, 2007
Hi Friends,
I have made one form based on query. Its a invoice entry form. I have price, quantity and amount. I want when i enter price and quantity, amount should be calculated. I know its very simple for you. Any suggestion.
thanks
mithani
View 2 Replies
View Related
Jul 23, 2007
In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return? If so, can you share how this could be accomplished. Thanks
View 5 Replies
View Related
Aug 24, 2004
I am trying to create a suitable expression, either on the form itself or in the underlying query, that calculate the balance after each transaction, like a bank statement. What is the best way to do it?
Paul
View 2 Replies
View Related
Nov 22, 2012
I am new to Access. I am creating a simple order/payment database.
I have tables for the orders and payments and have a relationship setup.
I created an order form with a subform for the payments.
[URL] .....
View 2 Replies
View Related
