Problem with Report Totals

[lsembera]

I have a Vendor Shortage Report that is intended to show how many
parts we are missing from that vendor to complete our jobs. It is
first grouped on Vendor ID, then Part #. Underneath each part number
in the detail section, is a list of every job that we have that is
being held up by us not having that part. In the detail section is
also the estimated sale amount for that particular job.

Since the data is organized by vendor part number, a job could show up
in more than one section, and the estimated sales amount associated
with it will show up multiple times as well.

I need to add a total to the report that will count every job (work
order) and sum the estimated sales for each. In essence it will say
that THIS many jobs are being affected by us not having these parts
and it is causing us to lose THIS MUCH in customer sales.

I have no idea how to do this. If I try to do a simple sum, it just
adds up all the lines of detail and the jobs are counted more than
once (and the sales are counted over and over again too). I tried
looking into DCount and Running Sum and nothing works. I even tried
creating a query that housed JUST THESE 2 fields and had planned to
just show them in the report with an unbound textbox, but that's not
working either. The only thing that has been successful is to create
a listbox at the top of the report and then reference that query that
sums these up, but it looks weird and I know there's a simpler way.

Please help. I'm not really an access programmer, so dumb down the
explanation.

 

[KARL DEWEY]

Post the table strucures - field names with datatype.
Post sample data.

 

[John Spencer]

Create another report that shows the summary information you need.
Save the report.

In the existing report, add the summary report as an unlinked SUB-Report to
the existing report's Report footer section.

You may have to "play" with the saved sub-report to get it to show the
information you want.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[lsembera]

Create another report that shows the summary information you need.
Save the report.

In the existing report, add the summary report as an unlinked SUB-Report to
the existing report's Report footer section.

You may have to "play" with the saved sub-report to get it to show the
information you want.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County









- Show quoted text -

Thanks John, I'm going to try this and see how it works.