Link two tables (table and a query) in a Report

[mromyn]

Hi -

I have a report that lists monthly bugdet data by Category and SubCategory.

The report has a totals by Month for category, subcategory and an overall
total.

I need to include what has been spent per category, per month in the report.

Is there a way to include this data (from a table or query) so the report
will list out all of the budget data and all of the expense data as it is
entered?

The data would need to be linked by category and month.

It's been suggested by everyone that I do this in Excel. But the expense
data is in Access 2003 and not in Excel. So I need to work with Access.

Thanks!

 

[Allen Browne]

So you have 2 sets of data you want to display on the one report (budget and
expenditure), and these come from different tables? Use a subreport to
display the 2nd set.

To link the subreport to the main report, you will need the year and month
in the source queries. For example, if expenses have a field named
PurchDate, type an expression like this into the Field row in query design:
PurchYear: Year([PurchDate])
Then in the next column:
PurchMonth: Month([PurchDate])

You can now link the subreport by the category, year and month.