Access report question

[MrHockey]

Hi -

I have a report that has a subreport embeded in it. There is a work order field in the main report and in the sub report, so they are my master and child links. The main report list costs associated to work orders related to vender invoices, where as the subreport list costs associated to materials.

When you run a report you are asked to enter a work order # this parameter is passed to the main report and through the link is passed onto the child, however I have a problem.....some reports I run have do not have vendor invoice costs (no data in the main report) but they do have material costs (data in the subreport), so when I run my report it works perfect when there are both types of costs or if there is vendor invoice costs, but it does not work when there are only material costs (only data that would appear in the supreport.

How can I work around this?

Thanks!

 

[Phildaburn]

In the data condition you're describing, you have work order 123 in the data source for the subreport. In the data source for the main report, is there a work order 123?
If not, this is why they're not showing up.
Phil...

 

[MrHockey]

That is exactly my problem....is there not away to run the subreport based on a parameter (i tried adding a second parameter to the subreport) so that the subreport returns values even if there isn't anything in the main report?

I assume there isn't.....would you have a suggestion on how I should apporoach this report?

Thanks.

 

[Phildaburn]

Is the main report based on a table or a query? If it's a table, I would suggest a query where the data source for the subreport is the first table with a left join link to the main table on the work order number. It will provide all the records in the subreport's data source and only the ones in the main report's data source where the 2 are equal.
Hope that helps.
Phil...

 

[MrHockey]

The problem with that is if there is only vendor invoice costs and no material costs then I will have the same problem...won't I?

Essentially, these are the possible outcomes of the querry:

1 - Material only
2 - Vendor only
3 - Material and Vendor

 

[Phildaburn]

Yes you will. To work properly, the work order number field value must exist in your main table even if no other fields of infomation in your main table are populated.
Phil...

 

[MrHockey]

is it possible to use a parameter as the master / child link?

 

[Phildaburn]

I don't think so. I've only ever done a parameter-based report where the parameter existed in the query used as the data source for the report.
Phil...

 

[MrHockey]

Got...I made both the vendor and material costs subreports on a main report that just has the work order information in it.

Thanks.