Subreport and Main Report Linking Problem

[rnutts]

Hi

I have a report which shows sales, labour costs etc by date and project number.
The main report summarises the labour cost by project and I have a sub report summarising Sales values by project, which then links to the main report on the Project ID.
The problem I have is that we may have sales, but no labour cost and also vice versa, which because of the way the report is configured means that the sales values dont show for these projects.
If I make the sales figures the collated item on the main report and the labour the subject of the subreport then the project with labour costs and no sales doesn't show the labour costs for that project.
I have tried combining the two in a query, but I get multiplication on the number of lines the query returns.
Is there a way of telling the report to show all items the subreport shows regardless of whether there is a line on the main report. I am thinking of simething like the 'Join Properties' in a query where you can do this
You may ask why would we have sales and no labour cost and vice versa. One of the points of this report is to highlight this problem as well as showing the Gross Profit for a project.
If this is not clear please let me know

Many thanks

Richard

 

[stopher]

I think you should have ProjectID only on you main report. Then have two subreports - one for labour and one for sales. Then it doesn't matter if one is missing.

hth
Chris

 

[rnutts]

This now works, thank you
However I now have the problem that I receive a row for every project, not just ones with values in, this makes the report 56 pages long rather than 1 or 2.
I have put the VBA code below in as an event to stop irrelevant rows appearing, however it does not remove the lines just leave blanks, is there a way to not show the blank lines.


Private Sub GroupFooter2_Format(Cancel As Integer, FormatCount As Integer)
If Me.SalesValue = 0 And Me.MatCost = 0 And Me.LabCost = 0 And Me.ATCost = 0 And Me.wdcost = 0 Then
Me.SalesValue.Visible = False
Me.MatCost.Visible = False
Me.LabCost.Visible = False
Me.ATCost.Visible = False
Me.wdcost.Visible = False
Me.Client_Name.Visible = False
Me.enquirynumber.Visible = False
Me.ProjectTitle.Visible = False
Me.ProfitLoss.Visible = False
Else
Me.SalesValue.Visible = True
Me.MatCost.Visible = True
Me.LabCost.Visible = True
Me.ATCost.Visible = True
Me.wdcost.Visible = True
Me.Client_Name.Visible = True
Me.enquirynumber.Visible = True
Me.ProjectTitle.Visible = True
Me.ProfitLoss.Visible = True
End If
End Sub

 

[vbaInet]

Try changing the section's Can Shrink property to Yes.

 

[stopher]

For the record source for your main form, you could filter so that it only lists ProjectID's where there is either a sales record or a labour record.

You can do this by joining the three tables then doing a count by ProjectID on number of SalesID's and number of LabourID's. Then you can just filter where the sum of these two counts is >0.

hth
Chris

 

[rnutts]

Sorry for the delay, but have been consumed with month end and then salaries
Would I join the three tables in one query with the Project ID in the sales and labour tables linked to the project ID in the Project Table.
Or would I do this in two queries??
How would this then be filtered in the main report

Could you give me a bit more details

Many thanks

Richard