Bank Statement Report with Sub-reports

[Furqan Iqbal via AccessMonster.com]

Hi,

This is my first post here, I'm new to Access Reporting. I have a Table
called Bank, it has the following fields

BANK TABLE :
date, description, debit, credit, flag
flag is 1 = Deposit 2 = Check Issued 3 = Electronic Withdrawl 4 = Fees
=======================================================================
I have 4 Queries each query shows the result with each flag.
What I want to do is to Create a Bank Statement, when someone chooses a
Month in the VB front End it displays all the Deposits, Checks Issued,
Withdrawls and Fees for that Month.
What I have done is created a main report with 4 subreports Linked the
Child Master Fields of all 4 sub reports to the main reports date field.
I had the sub reports in the Detail Section of the Main report which prints
each sub report with each days transactions, then I read a post somewhere
where they said that I should try moving my sub reports to the Main Report
Footer Section. I tried that too but I don't get any results.

If I place the main DATE field in the header section of the main report
then it keeps asking me to enter date. if I place the date field in the
details section then it doesn't ask me for the date but doesn't give the
right details either. What I would like is that each Subreport has the
complete Months details.

I would appreaciate all the help.

Thanx

Kinara

 

[Guest]

Sorry, but you are going about this the hard way. You do not need 4 queries,
you need only 1. Your sort order and grouping in your report will get you
what you want. As to your date, it depends on where the date comes from.
Assuming you have the date in a text box on your form, you can set the
control source of the date textbox on the report as [forms]![MyForm]![MyDate]

 

[Kinara via AccessMonster.com]

Thanx for your replay, so do I still need 4 sub-reports in a main report?
and how do I avoid the 4 queries? I would appreciate if you could
ellaborate a little bit.

Thanx

 

[Guest]

No, you do not need the sub reports. Your recordset should contain all the
transaction types. Then in the Sorting and Grouping dialog box, group by
your transaction type. I did not get from your original post how you choose
dates and where the date exists. Is it a date range and the dates are in the
recordset? If so, then you may need to group by date if you want to show
transactions for a specific date grouped together. If all the transactions
should be grouped together, then you only need to group by transaction type
and probably sort by date and transaction type.

 

[Kinara via AccessMonster.com]

Thanx for your reply, I have a VB Front ENd which passes a date range to
the report. The following is how I would like the report to look like...

=========================================================================
BANK STATEMENT from 04/01/2005 to 04/31/2005

DEPOSITS
--------
DATE DEPOSIT DESCRIPTION AMOUNT
------------------------------------------------------
04/05/2005 Check received from Customer 1 $5,000.00
04/07/2005 Check received from Customer 2 $8,000.00
04/09/2005 Check received from Customer 3 $9,000.00

Total Deposits $22,000.00


CHECKS PAID
-----------
CHECK NUMBER DATE CHECK DESCRIPTION AMOUNT
----------------------------------------------------------------------
1111 04/05/2005 Phone Bill $200.00
1122 04/11/2005 Car Payment $600.00
1133 04/13/2005 Electric Bill $300.00

Total Checks Paid $1,100.00

=========================================================================

Now the way I tell if this is a deposit or a check paid is through the flag
field which is
(1 = Deposit 2 = Check Issued 3 = Electronic Withdrawl 4 = Fees)

Without Subreports how do I go about displaying these sections of a bank
statement in one report based on the flag field?

I would appreciate all the help

Thanx

Kinara