One to Many 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

 

[Hotshots]

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

I guess sorting and grouping would solve the problem..... before that
Assuming the relation between the tables are joined at the field
'TripID' it would only create a One-One Relationship as both are
defined as Primary Keys.(there exists a flaw in your
relationships).....

To get a one-many relatiosnship i believe the primary key of the
'expenses' table - tripid field should be removed...then there would be
a one-many relationship possible between the two TripId fields...

After that it is fairly simple....jus create a report using wizard
...... Select all fields from the 'tblTrips' table as well as all fields
from 'tblExpenses' except TripId and TripDate ......In the next screen
where it asks how u want to view ur data...choose 'by tblTrips'....

U have ur report before adding ur total fields.... in design view of
the report , in the report footer add a textbox and set its control
source to " =SUM([Amount])".... where amount is the amount of
expense....and add another text box with control source
"=SUM([FareAgreed])" . This would get you the totals of the entire
report..... and if you nedd the total of expenses of each trip add
textbox with controlsource set to "=SUM([Amount])" to the TripId
Footer....But in order to activate the TripId footer go to Grouping and
Sorting and set the value of 'Group Footer' to 'Yes' under TripId. And
if you need the difference also to be shown add another calculated
field wherever neccesary.


Now to restrict the data source of the report to dates given by the
user then go to the properties menu of the report and build the query
of the 'record source' under the 'Data' tab.
and in the criteria field of the TripDate enter - between "Enter
the Starting Date" and "enter the ending date" - This would create
a parameter query asking the user for input before the report is opened
and the report is filtered based on the input !!!!...


Thats it u've got ur Report !!!!!!! ...nd btw if you feel you
classify your expenses into three or four major heads then i think
there isn't a necessity of a related child table...unless your expenses
keep varying a lot and there are many kinds of expensesi think u can
jus add the expense heads as fields to the 'trips' table and be done
with..... you just have to add the total fields into the report !!!!