Problems with Multiple Rows in Query for Report

[richard]

Hi

I have a database that captures two distinct pieces of information
One table (tblPayWeek) captures the timesheets for employees and costs, this
is captured by employeeID and also by ProjectID, the project being the large
job being worked on.

We also have sub jobs to the large job(Project), and the sales information
is captured here(tblSalesDetails) with an index field called SalesID, along
with (if they record it) the hours the men have worked on a sub job. The
costs for labour, materials are stored in seperate tables using the Field
SalesID as the foreign key link back to tblSalesDetails.

Example of project and sub jobs
Project is working on a tower block of flats
SubJob is the work in a flat within the tower

The tables tblSalesDetails and tblPayWeek do have a foreign key field called
ProjectID in each table.

I have a report (rptSalesJobSummary) which pulls together the sales by
Client, using Subreports for the costs (labour, materials etc). However due
to the lack of info provided by the workers I wish to use the timesheets
information for the labour costs in this report.
The problem occurs when I use the field ProjectID to link the
report(rptSalesJobSummary) and subreport for timsheet labour costs. I am
getting multiple rows for the labour being reported, giving highly inflated
labour costs because of this.
I am unable to use the SalesID field in the table tblPayWeek as the
timesheets do not provide that level of detail.
Can anyone think of a way around my problem with the report(if you can
understand it)

 

[Jeff Boyce]

Richard

While you can (and usually do/should) use a query to return the data you
want, you rarely need or want to show all that in your report.

A couple ideas suggest themselves to me right off...

First, you could use Group/Sort in your report to create a group on the
Client, and use the detail section to show, well, the details!

Another approach might be to create a main report that holds Client info,
and a sub-report that holds Sales info.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.