Fakhruddin:
I depends on how you want to present the data in the report. If you want
to
present the detail data from each table data independently, but use the
aggregates values form each to compute the profit and loss figures then
you
could create three separate reports and embed them in a single report as
subreports. This would give you the details from each and you'd be able
to
lay out each one differently to suit the characteristics of each set of
data.
For the parent report in which the subreport's are embedded I'd suggest
you
create a Calendar table. This simply a table of all dates over a range
(e.g.
10 years) in a column CalDate say, and is a very useful auxiliary table in
various situations. A n easy way to create the table is to serially fill
down a column in Excel and then import it into Access as a table. You
could
base the parent report on the Calendar table with a query which lists all
months and includes parameters to enter the year and month for which you
want
to report:
SELECT DISTINCT
YEAR(CalDate) As CalYear,
MONTH(Caldate) AS CalMonth,
FORMAT(CalDate, "mmmm yyyy") As CalMonthYear
FROM Calendar
WHERE YEAR(CalDate) = [Enter year:]
AND MONTH(Caldate) = [Enter month as a number:];
Simailarly base the subreports on queries which include computes columns
for
the year and month, e.g.
SELECT *,
YEAR(TransactionDate) AS CalYear,
MONTH(TransactionDate) AS CalMonth
FROM MiscTransactions;
Better still would be to make the parameters references to a dialogue form
in which you can select the month and year from combo boxes, and which has
a
button to open the report.
Each subreport would then be linked to the parent report by making the
LinkChildFields and LinkMasterFields properties of each subreport control;
that's the control in the parent report which houses the subreport:
CalYear;CalMonth
For the aggregate values you can use the DSum function in unbound controls
in the report e.g. to sum an Amount column in MiscTransactions for the
relevant month:
=Nz(DSum("Amount", "MiscTransactions", Year(TransactionDate) = " &
[CalYear]
& " And Month(TransactionDate) = " & [CalMonth]),0)
The Nz function caters for there being no data for the month and the DSum
function returning a Null.
To get the profit and loss its simply a question of combining such
expressions, each referring to the different tables, in the ControlSource
of
another unbound control, add and subtracting them as appropriate. If
MiscTarnsactions contains the credits and the other two tables the debits
for
instance:
=Nz(DSum("Amount", "MiscTransactions", Year(TransactionDate) = " &
[CalYear]
& " And Month(TransactionDate) = " & [CalMonth]),0) - Nz(DSum("Amount",
"TripExpenses", Year(TripDate) = " & [CalYear] & " And Month(TripDate) = "
&
[CalMonth]),0) - Nz(DSum("Amount", "ServiceExpenses", Year(ServiceDate) =
" &
[CalYear] & " And Month(ServiceDate) = " & [CalMonth]),0)
If you only want the aggregated values in the report, and no detailed
data,
then just leave out the subreports.
Another way would be to compute the monthly aggregated values in a query
which joins the three tables on the year and month and base the parent
report
on this query. The aggregated values could then be in bound controls.
The
query should use outer joins as its theoretically, even if not
realistically,
possible than one or more tables might have no data for the month. This
is
one reason why I suggest using a calendar table as this always returns
data
even if any of your three tables don't have any for the month.
Ken Sheridan
Stafford, England
Fakhruddin Zavery said:
Greetings,
I have a small fleet system that I've created and the problem right now
I'm
facing is that I want a report that would bring out all the expenses
incurred in a month against the profit made so that one would know what
was
the Profit and Loss for that particular month.
Currently my data is stored in about 3 tables (MiscTransactions,
TripExpenses, ServiceRepairs) Most of the fields are the same but there
are
certain fields that differ from table to table.
What would be the best option for me to create such a consolidated report
that would show me everything from the 3 different sections.
Thanks and Regards
Fakhruddin Zavery
