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

How To Calculate Current Balance On Hand

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

Modules & VBA :: Monthly Payment - Create A Worksheet Which Calculate Balance

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

Forms :: Worksheet That Calculate Balance Remaining At The End Of Each Monthly Payment Period

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

Running Balance In A Query

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

Query To Pull Most Recent Account Balance

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

Can't Query For Average Balance Sytax Errors

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

Query To Pull Most Recent Account Balance

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

Query To Give Daily Balance Across Bank Accounts? (Then Plot In A Graph)

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

Queries :: Balance Update Query - Data Type Mismatch In Criteria Expression

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

Queries :: How To Insert A Prompt For Number In A Query To Calculate Against Another Field In Query

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

Getting A Balance On A Report

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

Balance Of Two Columns?

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

Adding Last Balance To Previous One

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

Calculated Balance Field

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

Running Balance / Subtotal

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

Balance Carried Forward

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

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

Get Average Monthly BALANCE

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

Forms :: Add Balance Into Next Month Arrears?

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

Calculate Median In Query

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

Query To Calculate Busiest Day

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

How To Calculate Field In Query

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

Calculate Yes/No Fields In A Query

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

Expression To State Current Balance After Each Transaction

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

General :: Order Payment Database - Balance Due

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







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