Hello, I'm sure somebody has had this same requirement, but after a long time trying I cannot get this right. Scenario:
Parent Table contains one record per Job Code
Lookup table 1 contains many records per Job Code with rate, quantity and value of work carried out on that job.
Lookup table 2 contains many records per Job Code with expenditure details for that job.
How could I present this data in a report that summarises by Job Code a field in each of the lookup tables, such that the summaries can be used in calculations.
The actual scenario is more complex than this, but this is the basic premise of my problem.
The report would look like this:
Job Code: 23560
Income:
Item Quant Rate Total
101 2 20 40
102 3 40 120
___
160
Expenditure:
Item Cost
1 50
2 60
3 10
____
120
Profit/Loss = 40
Next job........etc
I've tried using subreports, but in a much more complex scenario, the summaries of seperate subs cannot be used in a formula of the main report. I've also tried using queries to first summarise data, but depending on the linking, the detail lines get returned more than once.
Anyone dealt with this?
IP Logged
