LINKING 2 UNCONNECTED SUB-REPORTS

[Brian]

i have a monthly sales report and a monthly expenses report - both totally
unconnected.
In order to show them side-by-side, I have created an unbound report with 2
sub-reports which display only the Date Footer and Report Footer from each
report so in effect it looks like this -
EXPENSES SALES
October 100 .00 October 200.00
November 100.00 November 200.00

Is there any way of gettng the two subreport to inter-act so I can show
Sales v Expenses on each month line or at least just a bottom line result.

I have been wrestling all day with different arguments and syntax and using
different sections of the report. Maybe it can't be done at this stage
without a full re-write (which may have been quicker!) but I am basically
trying to merge two previously separate databases.

Hoping someone can put me out of my misery

 

[Duane Hookom]

Can't you combine your two record sources into a single query for displaying
in a report?

 

[Brian]

Hi Duane
Tried that but becasue each underlying query has separate date fields
(unrelated) it returns a blank data sheet. I messed around for ages trying
different join options but with no joy. Some community member did offer me a
solution which involved creating a 3rd date table, populating for 10 years
then a very complex union query for all 3 but I could'nt get it to work and I
don't have the skills to correct the syntax errors that Access kept lobbing
at me.

 

[Duane Hookom]

Make up your mind if you need "separate date fields" or "monthly sales report
and a monthly expenses report". I expected you needed only monthly totals
which should allow combining into a single query.

 

[Brian]

You are quite correct, it is the monthly totals but I assumed a combined
query needed to be based on the two separate table's dates fields.
The two separate monthly reports have these underlying record sources
tbexpenses
from which [Date] and [Debit Amount] - to Produce Monthly Expesnes Report
tbSales
from which [Date] and (Sales Amount] - to Produce Monthly Sales Report

The 2 reports are merged into FINAL Report with these controls
EXPENSES REPORT (Date Footer]
=Format$([Date],"mmmm yyyy",0,0) and =Sum([Debit Account])
SALES REPORT (date Footer)
=Format$([Date],"mmmm yyyy",0,0) and =Sum([expr3])

If I could trouble you to walk me through the method of combining the two
underlying tables I would be extremely grateful.

 

[Duane Hookom]

I would probably start by creating a union query of both tables. The query
SQL might look like:
SELECT [Date] As TransDate, [Debit Amount] as Amt, "Expense" as DebExp
FROM tblExpenses
UNION ALL
SELECT [Date], [Sales Amount], "Sales"
FROM tblSales;

You can then create a crosstab query with Year([TransDate]) and
Month([TransDate]) as the Row Heading, DebExp as the Column Heading, and
Sum([Amt]) as the Value.
Set the Column Headings property of the crosstab to "Expense","Sales".

Base your report on the crosstab query.
--
Duane Hookom
Microsoft Access MVP

You are quite correct, it is the monthly totals but I assumed a combined
query needed to be based on the two separate table's dates fields.
The two separate monthly reports have these underlying record sources
tbexpenses
from which [Date] and [Debit Amount] - to Produce Monthly Expesnes Report
tbSales
from which [Date] and (Sales Amount] - to Produce Monthly Sales Report

The 2 reports are merged into FINAL Report with these controls
EXPENSES REPORT (Date Footer]
=Format$([Date],"mmmm yyyy",0,0) and =Sum([Debit Account])
SALES REPORT (date Footer)
=Format$([Date],"mmmm yyyy",0,0) and =Sum([expr3])

If I could trouble you to walk me through the method of combining the two
underlying tables I would be extremely grateful.

Make up your mind if you need "separate date fields" or "monthly sales report
and a monthly expenses report". I expected you needed only monthly totals
which should allow combining into a single query.

 

[Brian]

Done It!
It took me a while with more than a few attempts but eventually I have
achieved the result I wanted.
Impossible without your help & advice.
Regards
Brian

I would probably start by creating a union query of both tables. The query
SQL might look like:
SELECT [Date] As TransDate, [Debit Amount] as Amt, "Expense" as DebExp
FROM tblExpenses
UNION ALL
SELECT [Date], [Sales Amount], "Sales"
FROM tblSales;

You can then create a crosstab query with Year([TransDate]) and
Month([TransDate]) as the Row Heading, DebExp as the Column Heading, and
Sum([Amt]) as the Value.
Set the Column Headings property of the crosstab to "Expense","Sales".

Base your report on the crosstab query.
--
Duane Hookom
Microsoft Access MVP

You are quite correct, it is the monthly totals but I assumed a combined
query needed to be based on the two separate table's dates fields.
The two separate monthly reports have these underlying record sources
tbexpenses
from which [Date] and [Debit Amount] - to Produce Monthly Expesnes Report
tbSales
from which [Date] and (Sales Amount] - to Produce Monthly Sales Report

The 2 reports are merged into FINAL Report with these controls
EXPENSES REPORT (Date Footer]
=Format$([Date],"mmmm yyyy",0,0) and =Sum([Debit Account])
SALES REPORT (date Footer)
=Format$([Date],"mmmm yyyy",0,0) and =Sum([expr3])

If I could trouble you to walk me through the method of combining the two
underlying tables I would be extremely grateful.

Make up your mind if you need "separate date fields" or "monthly sales report
and a monthly expenses report". I expected you needed only monthly totals
which should allow combining into a single query.

--
Duane Hookom
Microsoft Access MVP


:

Hi Duane
Tried that but becasue each underlying query has separate date fields
(unrelated) it returns a blank data sheet. I messed around for ages trying
different join options but with no joy. Some community member did offer me a
solution which involved creating a 3rd date table, populating for 10 years
then a very complex union query for all 3 but I could'nt get it to work and I
don't have the skills to correct the syntax errors that Access kept lobbing
at me.

:

i have a monthly sales report and a monthly expenses report - both totally
unconnected.
In order to show them side-by-side, I have created an unbound report with 2
sub-reports which display only the Date Footer and Report Footer from each
report so in effect it looks like this -
EXPENSES SALES
October 100 .00 October 200.00
November 100.00 November 200.00

Is there any way of gettng the two subreport to inter-act so I can show
Sales v Expenses on each month line or at least just a bottom line result.

I have been wrestling all day with different arguments and syntax and using
different sections of the report. Maybe it can't be done at this stage
without a full re-write (which may have been quicker!) but I am basically
trying to merge two previously separate databases.

Hoping someone can put me out of my misery