Report Consolidation

[Fakhruddin Zavery]

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

 

[Allen Browne]

The best solution would probably be to combine the 3 tables into one. You
probably need an extra field to determine the expense type (Misc, Trip, or
Service), and there will be some fields that apply to one type only. You can
still use 3 different forms to enter this data if you like, but at least it
is easy to query.

If you can't do that, you will have to create a UNION query to combine the
records from the 3 tables into one long list. This kind of thing:
SELECT TripDate AS ExpenseDate,
TripCost AS ExpenseAmount
'Trip' AS ExpenseType
FROM TripExpenses
UNION ALL
SELECT TransactionDate AS ExpenseDate,
TransactionAmount AS ExpenseAmount
'Misc' AS ExpenseType
FROM MiscTransactions
UNION ALL
SELECT RepairDate AS ExpenseDate,
RepairCost AS ExpenseAmount
'Reapir' AS ExpenseType
FROM ServiceRepairs
ORDER BY ExpenseDate;