transaction journal

[HelpUs]

Hi,

I am pretty new on creating report in Access. I am trying to print a transaction journal for a particular day or for a range of days. I have two tables:

1. Month end table
2. Transaction table

On month end the outstanding balance is posted on the month end table for each account. The transaction table contains Date, transaction detail, Debit or Credit, amount.

After printing my report should look like below:
------------------------[tt]
Report for May 21, 2004 - May 22, 2004

May 21, 2004
Account No Description Debit Credit
Balance Brought Forward 99999.99 99999.99
9999999999 XXXXXXXXXXXXXXXXXXXXXXXX 99999.99 99999.99
9999999999 XXXXXXXXXXXXXXXXXXXXXXXX 99999.99 99999.99
9999999999 XXXXXXXXXXXXXXXXXXXXXXXX 99999.99 99999.99
Outstanding Balance 99999.99 99999.99

May 22, 2004
Balance Brought Forward 99999.99 99999.99
9999999999 XXXXXXXXXXXXXXXXXXXXXXXX 99999.99 99999.99
9999999999 XXXXXXXXXXXXXXXXXXXXXXXX 99999.99 99999.99
9999999999 XXXXXXXXXXXXXXXXXXXXXXXX 99999.99 99999.99
Outstanding Balance 99999.99 99999.99
[/tt]
The Balance Brought Forward is the total of the field from month end table and total of the balance of previous days. Outstanding balance is total of Balance brought forward and the detail transaction. And finally at the end of the report the summary sheet is require.

Please help me, I am not able to make out how to proceed.

Thank you.

 

[HelpUs]

Friends,

Please help me. I am stuck.

Thanking you in advance.
BO

 

[OhioSteve]

I realize that you are seeking a quick solution. Unfortunately I suspect that you need to rethink your whole table structure. Generally, you should only store the individual transactions...don't store the results of calculations. Your month end table should probably not exist. Also, you probably don't need the debit/credit field. Just make the values in amount field positive or negative.

Your structural problems are the ultimate cause of your difficulties with the report. For example, if you just had a table of transactions, you could include a group footer for each month. That would take the place of your "balance brought forward".

 

[HelpUs]

Thank you very much for your reply. What I understand is you are suggesting for a single table (i.e transaction table, no month end table), remove the debit/credit field and store the values in amount field positive or negative.

Done. How I will able to show the "balance brought forward" figure while printing the report for a particular month.

Thanks
BO

 

[OhioSteve]

Yeah, I can walk you through an example that will be helpful.

1) Create a table called "transaction". It should have two fields, transDate (date/time datatype, short date format) and transAmount (currency datatype). Enter several records, including several in one month and some negative amounts.

2) Create a select query call transQuery. Transaction will be its data source. Include both fields from transaction, and do a sort on transaction date. Also, create a calculated field with this syntax~ "transMonth:month([transDate])"

3) Create a report called transReport. Make transQuery its control source. Put two text fields on the report, one for transdate and one for transAmount. Then, while you are in report design view, choose "sorting and grouping" from the view menu. In the field/expression column, choose transMonth. Make the sort order "ascending", and choose "yes" under "group footer". When you return to the report, you will see a new band.

4) In your new footer band, put a text field called "monthly total" its control source should be "=Sum([transAmount])".

That should be enough information to get you started on the project. If you have more questions, post again. I will be using these forums quite a bit today. After today, I may not log on for about 10 days.