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];
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
I posted this about a month ago, but at that time I was running myself ragged and through too many problems at once. I stepped back and made some good progess. I put this in the General forum because it could encompass VBA, queries, and reports
I have a main report (Percentage Report) that has 4 subreports in it. Each subreport is based on a query that's run from three other queries. Its a neatly tangled mess, but it works fine.
The queries all count and calculate percentages for a pass rate of inspections on maintenance. There's an over-all/basic percentage that simply totals everything and divides for a percentage. There's also a "maintenance" percentage that only takes into account inspections done on maintenance (as opposed to various programs and processes.) Those both work fine for any given time period.
The third (and final) percentage deducts 0.5 points for each of a specific list of inspections (safety and other violations.) This works fine so long as you're only looking at a month's worth of data. The problem comes when you want to view any time period larger than that (quarter, semi-annual, annual.)
Basically, you end up subtracting a sum from an average and you end up w/ totally inaccurate numbers. I just can't quite figure out how to effectively either group by month or how to average the deductions based on the months covered.
I just finished completing this whole thing, and I'm pretty much done for tonight. Any help would be great. ---------------------------------------- Key words: sum totals, report grouping, report conditional format, alternate row colors (greenbar), count, calculate, percent
My database has a crosstab query that counts the number of hits by month. I have two columns that are named "Count of Material"(Frequency) and "Sum of ordered Qty"(Total ordered). I need add a colum to find the Average Monthly Demand by dividing the Total ordered/Frequency/number of months in the query. I have tried everything to make it work but can't.
I am currently working on a small database to track my own investment records.
I would like to create the function which enable to count the number of records that has exceeds the average value during that period.
Following is the SQL that I have managed to produce:
SELECT [ED 2013].Code, Count([ED 2013].Start) AS CountOfOpen1 FROM [ED 2013] WHERE [ED 2013]![Date] Between #1/1/2013# And #2/1/2013# AND [ED 2013]![Start]>[ED 2013]![End] GROUP BY [ED 2013].Symbol;
This SQL does work. However, when I try to add the Avg function. The Query stop working.
Meanwhile, I am wondering if there is any existing Ms Access template (free or commercial - but must be customisable ) available?
I have a report that counts the number of monthly calls. What I would like is an average of the monthly calls in the report footer.
My total for a particular month is =Count([Date]) and I named the unbound control MonthlyTotal.
This is in the DateFooter section of the report.
I then put an unbound control in the report footer and used the expression = Avg([MonthlyTotal]).
Of course when I changed from design view to report view, it asked me for an input of [MonthlyTotal].
I then tried =Avg([Reports]![qryLetterWritersbyDate]![MonthlyTotal]) and while it didn't ask me for an input, there was nothing in the ubound control in the report footer.
I have search for an answer, but all I find is using a query. Is what I am attempting to do possible? If so, how?
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.
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:
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 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:
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.
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.
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.
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?
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.
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 )
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:
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?
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?
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:
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?
I created a report to show stock level and transactions. there are date, transaction, quantity and balance fields. the balance field show the total balance of the item at the moment. what I need is to show the balance at the transaction date.
