a Report with Subreports and calculated fields

[Guest]

i have a database where i am trying to track transactions that happen in the
businesses petty cash. recording the transactions is easy, i have a table
with the following fields:

transactionID
InvoiceNumber
Description
Date
TypeID
linked to a table that stores 2 states of the transaction, IN or OUT
CategoryID
linked to a table that stores any number of "expense" types
Subtotal
GST
PST

i am trying to now create a report that shows the transactions of each type
and the total for all the "IN's" and "OUT's", and finally the report needs to
show the current "value" of the account.

basically this is accounting. all the "IN's" need to be totalled, all the
"OUT's" totalled and then the "IN's" total minus the "OUT's" total is the
current balance.

i have no problem creating a report to list the transactions. i even have 2
sub reports that show the total of the transactions based on 2 separate
queries. i even managed to create a report that had both subreports and the
totals i needed based on each subreport. but i have no idea how to take show
the transaction list, separated by type, and have the appropriate subreport
show after the type of transactions they should follow.

basically it should list all the "IN's" then the "IN's" total subreport,
then the "OUT's" and its subreport and then the balance of the account. i got
the "IN's" done but i have no idea how to hide the "OUT's" subreport from
showing under the "IN's" and visversa.

if i havent confused the issue, can anyone help? i am assuming that i need
to put some kind of formula or code into the subreports, but i dont know
where or what.

 

[Duane Hookom]

I'm not sure why you have an INs subreport and a separate OUTs subreport.
If your main report is grouped by TypeID then place both subreports in the
TypeID group footer. Set the Link Master/Child properties to the TypeID
field.

 

[Guest]

Hello

k, that worked. had to add a id field to the query for the subreport, but it
worked. here is an interesting tidbit, i placed one subreport on top of the
other and only the subreport for that type group shows. cool.

thanks for the help. you comment made me think about it from a different
angle and helped.