Pull a value from a separate query into a report, using a field in the report as a parameter

[ExcessionOCP]

Apologies for the long-winded question.



I am capturing how much our company spends within specific cost categories (Capital expenditure, materials, resource etc.) and then reporting on it. I have a report that shows a summary of the spend in each category, grouped by the category, with the total for each category shown in a footer. My report is reading values from a form that allows the user to specify a particular project and work package within that project to report on.

Each category has a budget, and that budget differs between projects, and between different work packages within a project. I have a separate table that records each budget for each category in each work package and project.

I would like to show the budget for each category as a field in my report, so that I can then create a calculated field which displays the amount left to spend, using the summary shown in my report.

So, for each grouped category in the report, I would like to have a field that queries my budget table and returns the relevant budget for that category, using the category field in my report as a parameter in my query - is it possible to reference a report field in a query in the same way that you can reference a field in a form? (using the [Forms]![...] syntax)?

Hopefully someone can a) understand my question and b) offer some advice

Thanks very much in advance

[Welshgasman]

Best to join in the recordsource if you can.
Else use DLookUp().

[Bob Fitz]

Can you post a copy of your db