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 Replies
ADVERTISEMENT
Jul 31, 2013
I have a query which is compiling monthly transaction of fee, the students paying through voucher. To every month the query is building records for students (Grouped), my problem is i have to show the arrears in the same query which would be on minus/plus of paid amount from actual fee both are in this query i can populate the arrears by simply subtracting both fields but the thing is that i am looking for a arrears which would be the adding total of previous arrears as to date transaction.
View 5 Replies
View Related
Jan 2, 2014
i am using this query to insert the previouse balance into arrears as shown in the figure. it will insert some record correct and some are not correct.
SELECT fees.[gr no], sum((Admission+Registration+Fees.Tuition+[Fine]+Fees.Transport+[Exam]+[Misc])-Fees.Paid) AS bala
FROM fees
WHERE fees.balance<=0 and [gr no]=[gr no]
GROUP BY [gr no];
View 2 Replies
View Related
Aug 9, 2013
Using Access 2010..I have form with a date on it. For this control I have show date picker set to "For dates" and lo and behold I get calendar! I can scroll through this calendar month by month. Great if I just want to go back or forward a month or three. What I'd like to be able to do is scroll through the calendar year by year. Can I do this with the method I'm using at the moment? If not is there a way round it that isn't over complicated?
View 6 Replies
View Related
Mar 15, 2015
I have a database tracking a budget. It is like a cash boom. I have deposit and make payments. Supplier, invoice# etc. I would like my form to show a running balance of my deposits and expenditure.
View 3 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
Dec 26, 2006
I rent out a property , on the rental statement I have a field [Datepaid], [RentpaidFrom] and [rentpaidTo]. I would to have a field that can calculate the rent that is paid to show if the rent is in arrears or in Advance. I guess the code would be something like (datediff ???? etc) but i have no idea what it should be. the calculation should appear on the query as i.e.2 days in arrears or 12 days in advance etc
your help would be much appreciated
merry christmas to you all
Jabez
View 4 Replies
View Related
May 5, 2014
I am trying to create a database that will keep track of the orders placed for a given part number by month. Currently, my table houses the part number, and the ordered amount for the past three years by month (there are thirty-five columns for every part). My column headings are ORDER_MAY_2013, etc. I would like to set a query up that will look at the column headings and pull the amounts ordered for each part for the past twelve months. In other words, I have three years of data in my table. In my query, I just want one year. However, I don't want to have to rewrite the query every month so that it will pick up the new data. Is there a way to accomplish this?
Is there a better way to build this database? I thought about just have four columns in my table - PART_NUMBER, ORDER_MONTH, ORDER_YEAR, ORDER_AMOUNT. The only problem there, is that every part (there are about 450 parts) would have to be listed 35+ times. That seemed too redundant to me, so I built the table this way. However, now I am having trouble querying against it.
View 2 Replies
View Related
Mar 19, 2012
I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.
View 10 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
May 9, 2013
I have a txt box on a form in which I type the month of the year e.g January, February etc. What I am having problems with is in another field I need to convert this month to it's corresponding value e.g. January will be 1, February will be 2 and so on.
View 14 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
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
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
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 1 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
Nov 21, 2005
I have a database set up with charts which plot information about support for "quality" purposes.
I have reports that work out the percentages but i would like to be able to take the percentage figure (say on the 1st of each month) and add it to a graph which will then be able to show the figures from the last 12 months so i can see how well the support is performing.
Any help would be greatly appreciated
View 3 Replies
View Related
May 11, 2006
Access 2000
I have an “unbound” form with a list box, a text box, and a combo box. The list box’s Row Source Type is a Query. The query’s criteria come from the textbox and a combo on the form. Once the information is entered into textbox and the combo box a macro is set to run on the combo box’s “On Change” event, which runs a Do Command Refresh Page. This expression [Forms]![Form46]![month] is an example of how I am setting the criteria in the query; which when I put the month “May” in the text box passes that information to the query’s design view “Month column. The combo also works the same way. What I need is to know what type of expression do I use so that I can use “All “ in the text box and combo box.
Thank You
Carbo
View 1 Replies
View Related
May 4, 2015
I'd like the user to be able to search a subform only by month and year. We have lots of reports with different days, but none of them are pertinent to searching.
I don't mind if it looks a little bit messy (such as using the calendar drop down and it discounting the day) but I'd like to have it all in one box, preferably where the user can just select ex. 05/2013 and it would filter the subform, showing only reports from that month.
View 6 Replies
View Related
Mar 3, 2014
I have a table that records a list of dates using the first of the month in short date format (01/01/2011) and I have the following query
Code:
SELECT Format([dateworked],"mmm yyyy") AS Monthyear, Table_dateworked.dateworked
FROM Table_dateworked;
This query is the source for a combo box so it stores the actual date but the formatted date is visible.An additional complication (if it has to be) is that I have two combo boxes and I want to make sure the finish date is later than the start date, so I have a similar query which only returns later dates than the start date.
What I would like to do instead is to remove the combo box and to get the user to type in the month and the year in the "MMM YYYY" format, default the day to the first of the month and make it invisible.How difficult would this be? Would it be easier to enter the date in short format and change the format using the after update event?
View 3 Replies
View Related
May 7, 2013
im doing my ict unit 7 edexcel ict exam atm and as one of the tasks we have been asked to generate the testid. the test id consits the year a underscore and then the number of the month so for example if a test happened this year and this month the test id should be 2013_5. i can get the year and the underscore into the code to generate the testid but im not sure how to get the month to change to a number so for example if i select may from a drop down the code needs to take that and generate it into a number.
View 6 Replies
View Related
Jun 17, 2013
I am trying to develop a form in the light of budgeting, therefore, I am designing a form with all possible options being an accountant I can think (it just a try nothing else)
Now here's the scenario
Fiscal Year
Period
Month
Working days
Period - Month - working (are my heading)
1 - Jan - 22
2 - Feb - 18 and so on
This is what I am trying to learn, if I select period 1 "Jan", application/code fill -out remaining months automatically, e.g. Period 2 "Feb"; Period 3 "Mar" .....
and if I select Period 1 "Mar", then period 2 "Apr", Period 3 "May" ......
Currently, I am using combobox, but problem is, if I selection period 1 = "Jan", then I am unable to restrict period 2 using "JAN" as both combobox are separate.
View 1 Replies
View Related