(I realise this is in the wrong section but it wasnt allowing me to start the thread in the report section)



Hi forum,

I have two input tables which are SALES and COSTS. Both have an invoice date in the form dd/mm/yyyy. Both these tables input based on a common field which is the Job Number. So to clarify I have Table 1 which is the sales table. The user enters the Invoice Date, the Job Number and then the Sale Cost of the sale. On Table 2, the user enters the Invoice Date, the Job Number and the Work Cost.

I need to create a report which cross-references these two tables, so I can get a Profit and Margin %..however..the problem is this report needs to be in a date range, but this date range needs to be for BOTH the sales' invoice date and the costs' invoice date.

So the user will enter a date range and the report will show the cost and sales for each of the Job Numbers based on the range..from this I can then use calculation fields to get the Profit and the Margin.

How can I do this?
As the problem I'm having is that if there is a cost for 10/10/2010 and there is no sale for that date, then the report automatically just puts a value in

I have tried making a cost report and then using the sales report as a sub-report and then using the sales field in the main report but it only pulls across the last Job Number. So somehow the Job Number needs to be linked to the cost field so the main report can see the difference and use the correct value. So Job 1 is using the correct Sales value for Job 1 from the sub-report but then Job 2 is using the same value (not the Sales Value for Job 2 in the sub-report).

I'm sure this is very confusing to read lol as I have had a complete headache with it for the past 3 days but I hope someone super clever can point out my mistake and get me back on track. If ya need any more info I'm happy to help and if you want a copy of the structure I'll send it over if it will help understand my problem.

Thanks

Matt