Calculate beginning balance, but print details on report

[Dennis]

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.

 

[Duane Hookom]

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.

 

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

 

[Duane Hookom]

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?