Adding invoice totals with multiple records showing totals

[Mike Koop]

First is an example of the output that I am working with.

Invoice Number Date Serial Number Make Invoice Total
012E5756 12/21/2007 123456 Chevy 73.87

REMOVED AND REPLACED BURNED OUT LIGHT BULBS ON UNIT DURING SERVICE
THESE REPAIRS ARE NOT COVERED UNDER THE MAINTENANCE CONTRACT
THANK YOU FOR YOUR BUSINESS
Total 221.61
I am trying to create a report from a Query that pulls the information from
5 different tables. Everything works fine until except that the table that
has the comments about repairs has a separate record for each line of
description. Each line also has the Invoice number which is where the join
is. In the report I have the properties to not show duplicates (otherwise
each line of comment would have the invoice number and invoice total next to
it).

The problem is that when I try and total the invoice it is picking up each
invoice total from every line of comments. In this case there are three
lines of comments so the total should be $73.87 but is three times that
amount at $221.61.

For the purpose of this question there is only one invoice displayed but
there will be multiple invoices that need to be totaled. I need just the
invoice total to calculate 1 time for each invoice.

Thank you ahead for any help.

 

[John Spencer]

Sounds as if you could use Duane Hookom's Concatenate function to solve your
problem

Field: CommentsLine: Concatenate("SELECT CommentLine FROM CommentsTable
WHERE InvoiceNum = ''' & MainTable.InvoiceNum & """",Chr(10) & Chr(13))

The function can be found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Download the Generic Function To Concatenate Child Records (46 KB) Access
2000

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..