Master Detail Report

[Fakhruddin Zavery]

Hello,

I have 2 tables which are on a One to Many Relationship amongst them (Master
Detail). The 2 tables are for Trips that a particular Truck would make and
TripExpenses Table which would hold any expenses related to any particular
trip.i.e Allowances, Fuel etc

The structures of the 2 tables is as follows
Trip (Date [PK], TripId [PK], TruckID, FareAgreed etc etc
TripExpenses (Date [PK], TripID [PK], IncExpID [PK], Amount etc etc

Now I need a report that would give me a detail of every trip made within a
particular month with all the related expenses that the particular trip
incurred.

The aim being that at the end of the report I can get totals of Total
FareAgreed (sum) less the Total Expenses (sum) and get a figure which would
show how much did one make in a particular month.

Right now if I do a report I get multiple lines from the Trip Table in
relation to every expense line that the trip incurred making it difficult
for me to work out the totals.

All the help will be appreciated

Thanks and Regards
Fakhruddin Zavery

 

[Marshall Barton]

I have 2 tables which are on a One to Many Relationship amongst them (Master
Detail). The 2 tables are for Trips that a particular Truck would make and
TripExpenses Table which would hold any expenses related to any particular
trip.i.e Allowances, Fuel etc

The structures of the 2 tables is as follows
Trip (Date [PK], TripId [PK], TruckID, FareAgreed etc etc
TripExpenses (Date [PK], TripID [PK], IncExpID [PK], Amount etc etc

Now I need a report that would give me a detail of every trip made within a
particular month with all the related expenses that the particular trip
incurred.

The aim being that at the end of the report I can get totals of Total
FareAgreed (sum) less the Total Expenses (sum) and get a figure which would
show how much did one make in a particular month.

Right now if I do a report I get multiple lines from the Trip Table in
relation to every expense line that the trip incurred making it difficult
for me to work out the totals.

That's normal when you use multiple tables in the report's
record source query. The standard report design for such
situations is to use Sorting and Grouping (View menu) to
group on the one side table's id (TripID?). Then you can
place the one side data in the group header section and only
the many side data in the detail section.

The group and/or report footer sections can total the many
side fields using Sum expressions in the usual way. The
group header/footer does not need to total the one side data
fields.

To calculate a report footer grand total of a one side
field, first add a text box (named txtRunfield) to the
group header/footer section. The text box is bound to the
field you want to total and its RunningSum property set to
Over All. Then a report footer text box can display the
total by using the expression:
=txtRunfield