Is that possible merging many Tables, many Dates in a yearly report? (1 Viewer)

JReynolds · Nov 29, 2017

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 · Nov 29, 2017

First of all, welcome to AWF.

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

Code:

InvoiceYear: Year([InvoiceDate)

Then run an aggregate query using that field

arnelgp · Nov 29, 2017

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 · Nov 29, 2017

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 · Nov 30, 2017

arnelgp said:
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!...

Is that possible merging many Tables, many Dates in a yearly report? (1 Viewer)

JReynolds

New member

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

plog

Banishment Pending

JReynolds

New member

Similar threads

Users who are viewing this thread