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 a form that currently adds services and products paid for by a client, via subform calculations, indicates payment, via cash, check or charge, and shows, finally, what is still owed. This is for a clinic and some old folks, for example, pay small amounts over time.
The subtotal, sales tax, total and amount still owed are all calculated controls that are functional, but only for the current record. Each transaction record has a date/time field. Each transaction record has the patient ID.
I need to carry forward that amount owed, so that the next time that person comes in, the amount owed control reflects any amount that was owed from the last visit. Is this a case where storing that amount in a field in the 'transactions' table would be justified? Or do I need to somehow incorporate ALL the code leading up to the amount still owed, SUMing it along the way? Very ugly...
Can someone, both, give me some clear suggestions on how to do this kind of running sum on calculated controls (since SUM only works on fields) AND point me to samples of calculated amounts carried forward, if necessary?
Once the selected item had been entered, i want the Current_Balance of previous entry of that item to be forwarded automatically in Previous_Balance field. Is it possible? I have at least 100 types of items.
I have searched the forum and just can't find an answer. I'm hoping someone can point me in the right direction.
I have a main form with a combo box that links to a table called tblSTC. There are however hundreds of records to choose from. To make the serach more simple for the user there is a button next to the combo called 'Search', When the user presses this a new form pops up with a text box and a list box (the list box contains all records in tblSTC). As the user starts to enter text into the textbox an AfterUpdate event limits the records displayed in the list box to match the criteria of what has been entered in the text box.
When the user identifies the entry he/she wants to select from the list box, I want the functionality of being able to double click the entry, the popup form close and the selection populated in the combo box.
I know that this is probably just simple code using the List Box double click event. I am however unsure how to go about it. Do I need to use some kind of Insert statement??
I need to use an update query to calculate the following 1st of April for each of 3000+ records, the only exception is if the Start Date is the 1st of April in which case the date will remain the same.
I have three questions, both im sure are easy just overlooking something.
1) I have a search form where you type the search parameters into 3 fields, and then your click search. I want the fields to automatically clear after the search button is clicked (but enough time for the query to get the info from the fields).
2) A combo box is used to select the marketing type, when first clicked.. a dialog box comes up and gives the user instructions for the next blank. After the field is changed to the right selection, i need it to automaticcaly forward to the next box.
3) I need to use a text box to sure information. The table records only 1 line of text, does that mean you cant have 2-3 lines? Really just 1 line wrapped in the window?
Looking for an automation solution currently I have a database for client progress and prescriptions. when the file is opened it is set for data entry so that a clean note form is opened. I have a button that pulls up past notes for their review. it has been requested that there be a way to pull the last narative field forward to the new record for editing as a number of the features of the narative remain the same. Currently the only way is for them to open the last seen note hightlight the field and past into the new note. to high level for some of them. Need to make this an automated feature where they would just click on a button and the process would all occur behind the scean and the note would apper in the new note narative box. I know that I could tell the form to not be for data entry but then they write over the last note. this needs to be a new record. Any suggestions greatly appreciated J
I used the code in the link below to get the login id returned on a form but I am having trouble now that I rolled out the database the code gives an error if the user has older than 2010 and even on a new machine that has access 2013 what is the best code to use? to return the log in id to a form?? without worrying about what version of access they have it wont be older than 2003. URL...
I have a table in MS Access naming "Salutation" and in the table I have 6 columns and each column named as Week No., Carry Forward records, new records, total records, processed records and pending records. Like below
Week No. Carry Forward records new records total processed pending 1 0 10 10 5 5 2 5 10 15 7 8 3 8 20 28 9 19
So now the requirement is every day pending records should get updated in the next row with in the same table under carry forward records of its next immediate day. For e.g. pending records of week number 1 which is 5 should get updated under carry forward records for week number 2 and so on.
I've got an access form, and all I want to do, is to be able to type a number into a text box, click a button and then for the record containing that number to be displayed (in that same form).
-And I dunno where to start.
I don't like using the navigation bar, although that is almost what I'm after.Also, search functions I've seen on here that use combo box lists to display results seem like too much for me, as each number typed into my search box will be unique.
I am a novice with Access and I would appreciate any help with two issues: 1. I am trying to get a value to forward to the next record in a different field so that I may keep a running total. For example: Record 1. A field named Today is added to a field named Forward to get a total in a field named Total. Record 2. I would like to get the value from record 1 in the Total field to carry forward to the the field named Forward in record 2.
The form looks something like this:
Today: 10:00 Forward: 2:00 Total: 12:00
I would like to get the value of 12:00 in record 1 carried forward to the Forward field of reocrd 2 so that the Forward field in record 2 would read 12:00.
2. I also need to add times together arithmatically but the problem I have is that when the total exceeds 24:00 hours it shows only the amount that exceeds 24 hours. For example, if I add 22:00 to 5:00 the result is 3:00 not 27:00. I need to show 27:00 as the total.
Any help with these two items would be GREATLY appreciated. Thanks, uspilot
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.
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.
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];
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?
