Calculate beginning balance, but print details on report
in [Reports]
|
From: Dennis on 22 May 2010 00:36 Hi, I'm using Access from XP Office Pro running under Windows 7. Background ------------------------------------------- I'm trying to implement a simple check book tracking report (like that little book that goes in a personal check book). The check book report can be run for: 1. All months 2. Multiple months 3. Single month A pop-up form will appear before the user runs the report asking the user for which months they want the report. When the report runs, it needs to calculate the beginning balance by simply adding up all of the transactions prior to the beginning of the specified month. Then the report needs to print the checks and deposit for the specified time period. And then it needs to print the total. My table name is tblChecks. I have one entry for each check that was issues and one entry for each deposit that was made. There is an entry in the check table with a check number of zero, a check date of 12-31-09, and a check amount of the account balance as of 12-31-2009. Database --------------------------------------------------- My table tblChecks has the following fields: TransID - Automatic assigned number by access TransTypeId - Either check, deposit, bank charge, or adjustment CheckNo - The number of the check if the transaction is a check. If the transaction is a deposit, bank charge, or adjustment this field contains a sequential number assigned by my software. The number assigned starts at 1000000. Trans Date - Date of the check or deposit. Trans Amount – The amount of the check, deposit, bank charge, or adjustment. Question ----------------------------------------------- How do I compute the beginning balance and then show that beginning balance on the report a single line and then print all subsequent transactions in detail? Also, please remember that the beginning balance time period is variable depending upon what beginning and ending dates the user enters. Thank you for your assistance. -- Dennis
From: Duane Hookom on 22 May 2010 13:13 You should be able to create a query returning a single record of the beginning balance based on all transactions prior to the beginning date. Use this query with your tblChecks in a union query where you filter the tblChecks for dates between the beginning and ending dates. -- Duane Hookom Microsoft Access MVP "Dennis" wrote: > Hi, > > I'm using Access from XP Office Pro running under Windows 7. > > Background ------------------------------------------- > > I'm trying to implement a simple check book tracking report (like that > little book that goes in a personal check book). > > The check book report can be run for: > 1. All months > 2. Multiple months > 3. Single month > > A pop-up form will appear before the user runs the report asking the user > for which months they want the report. > > When the report runs, it needs to calculate the beginning balance by simply > adding up all of the transactions prior to the beginning of the specified > month. Then the report needs to print the checks and deposit for the > specified time period. And then it needs to print the total. > > My table name is tblChecks. I have one entry for each check that was issues > and one entry for each deposit that was made. There is an entry in the check > table with a check number of zero, a check date of 12-31-09, and a check > amount of the account balance as of 12-31-2009. > > Database --------------------------------------------------- > > > My table tblChecks has the following fields: > > TransID - Automatic assigned number by access > TransTypeId - Either check, deposit, bank charge, or adjustment > CheckNo - The number of the check if the transaction is a check. If the > transaction is a deposit, bank charge, or adjustment this field contains a > sequential number assigned by my software. The number assigned starts at > 1000000. > Trans Date - Date of the check or deposit. > Trans Amount – The amount of the check, deposit, bank charge, or adjustment. > > > Question ----------------------------------------------- > > How do I compute the beginning balance and then show that beginning balance > on the report a single line and then print all subsequent transactions in > detail? > > Also, please remember that the beginning balance time period is variable > depending upon what beginning and ending dates the user enters. > > Thank you for your assistance. > > > > > -- > Dennis
From: Dennis on 22 May 2010 17:28 Duane, Cool. I've never used Union queries before (guess that is why I asked the question). I'll do a little reading and give it a try. Thanks, -- Dennis
From: Duane Hookom on 22 May 2010 18:58 You might want to start with SQL like: SELECT TransID, TransTypeId, CheckNo, [Trans Date], [Trans Amount] FROM tblChecks WHERE [Trans Date} Between Forms!frmDates!txtStartDate AND Forms!frmDates!txtEndDate UNION SELECT -1 , Null, Null , Null , Sum([Trans Amount]) As Amt FROM tblChecks WHERE [Trans Date}< Forms!frmDates!txtStartDate This makes some assumptions regarding your form and control names. -- Duane Hookom Microsoft Access MVP NOTE: These public News Groups are ending June 1st. Consider asking questions at http://social.answers.microsoft.com/Forums/en-US/addbuz/threads? "Dennis" wrote: > Duane, > > Cool. I've never used Union queries before (guess that is why I asked the > question). I'll do a little reading and give it a try. > > > Thanks, > > > -- > Dennis > > >
|
Pages: 1 Prev: Format duplicate date in report Next: linking queries to create a report |