Using field from a another group within a report (1 Viewer)

[SkyGravel]

Hi there,

I am having an issue with a report where I am trying to link a field which I have named on a level higher up as follows

The issue I have is that the RefHours is a set figure from another table included within my query but what appears to happen is that figure is multiplied by the number of files contained within 'Detail' giving the wrong result. (In the example below i have hidden 'Detail' for ease of viewing. The blue total should have read 4,658.00 not 19,008.00

Any ideas on how to correct this would be greatly appreciated.

 

[arnelgp]

then don't include that Table (the one with RefHours) on your query.
instead use DLookup() to fetch the value from the table.

 

[Pat Hartman]

dlookups() are not the solution. They are extremely inefficient in queries.

Sounds like you need a main form and a subform. The main form shows the summary data and the subform shows the detail data. You may currently have a recordsource that joins the two tables and therefore any calculation on data in the parent table would be multiplied by the number of related rows.

 

[arnelgp]

dlookups() are not the solution. They are extremely inefficient in queries.

im not suggesting to use it in query. directly on report Textbox control.
lay your best card.

Main form and subform... On Report!?#@

 

[SkyGravel]

dlookups() are not the solution. They are extremely inefficient in queries.

Sounds like you need a main form and a subform. The main form shows the summary data and the subform shows the detail data. You may currently have a recordsource that joins the two tables and therefore any calculation on data in the parent table would be multiplied by the number of related rows.

Will have a look into a subform, thanks for the advice

 

[arnelgp]

why would you need a Subform on Report?

 

[Pat Hartman]

I meant subreport, duh!

 

[Pat Hartman]

im not suggesting to use it in query. directly on report Textbox control.
lay your best card.

Sorry, but how is that better? Don't the same number of queries get generated? If your detail has 100 rows, you run 100 queries whether you do it in the report or in the query. If the detail has 1000 rows, you run 1000 queries. A join is far more efficient.