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

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

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

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
> 
> 
>