Is that possible merging many Tables, many Dates in a yearly report?

[JReynolds]

Hi, guys;

I'm working on a company database but i have a problem.

There are many revenue tables and many expenses tables in my database and every record has own date.

I can create total revenue, expenses&profit query and report but i want create "yearly total revenue, expenses&profit" query and report.

For example:

Report

2017 Sum Of Revenue / Sum Of Expenses / Profit
2018 Sum Of Revenue / Sum Of Expenses / Profit
...

Any idea?

Thanks for your help!

Reynolds Junior

 

[isladogs]

First of all, welcome to AWF.

Create a query with a field to extract the year part of the date field.
For example:

InvoiceYear: Year([InvoiceDate)

Then run an aggregate query using that field

 

[arnelgp]

there are many ways you can do this.
one way is using Union of all Revenue table, and Union of all Expense Table.
First, create a Dummy (tblDummy) table with 1 field and one record.
the dummy table will be used in final query.



Example:


Select [date], [Revenue] From RevenueTable1

Union All
Select [date],[Revenue] From RevenuTable2
...
...
save the query (qryRevenueHistory)



do the same with Expense/Profit.


for the Summarize query by year:


SELECT 2017 As [Year], (SELECT Sum([Revenue] From qryRevenueHistory WHERE Year([Date])=2017) As [Total Revenue],(SELECT Sum([Expense] From qryExpenseHistory WHERE Year([Date])=2017) As [Total Expenses] From tblDummy

UNION ALL
SELECT 2018 As [Year], (SELECT Sum([Revenue] From qryRevenueHistory WHERE Year([Date])=2018) As [Total Revenue],(SELECT Sum([Expense] From qryExpenseHistory WHERE Year([Date])=2018) As [Total Expenses] From tblDummy

 

[plog]

There are many revenue tables and many expenses tables in my database

Why? All similar data should be in the same table. Do all these revenue/expense tables have the exact same structure? Ridders answer is the correct one for a properly structured database.

 

[JReynolds]

there are many ways you can do this.
one way is using Union of all Revenue table, and Union of all Expense Table.
First, create a Dummy (tblDummy) table with 1 field and one record.
the dummy table will be used in final query.



Example:


Select [date], [Revenue] From RevenueTable1

Union All
Select [date],[Revenue] From RevenuTable2
...
...
save the query (qryRevenueHistory)



do the same with Expense/Profit.


for the Summarize query by year:


SELECT 2017 As [Year], (SELECT Sum([Revenue] From qryRevenueHistory WHERE Year([Date])=2017) As [Total Revenue],(SELECT Sum([Expense] From qryExpenseHistory WHERE Year([Date])=2017) As [Total Expenses] From tblDummy

UNION ALL
SELECT 2018 As [Year], (SELECT Sum([Revenue] From qryRevenueHistory WHERE Year([Date])=2018) As [Total Revenue],(SELECT Sum([Expense] From qryExpenseHistory WHERE Year([Date])=2018) As [Total Expenses] From tblDummy

Thanks, everybody!

Thanks, ArnelGP!

Excellent work!...