SQL that excludes unwanted values???

[Chew407]

I have a report that draws data from two separate tables which are linked on the claim number. What's happening is that in the tblRecovered there is often more than one of the same claim number. So it appears in the report as many times as it appears in the table. However, I only want the amount invoiced (INV_AMOUNT) to appear once for every claim number, no matter how many times it appears in the report. This is for calculation reasons. the amount invoiced is being added 2, 3, sometimes even four times throwing the numbers way off.I have the following code in the on-open event property of my report:

Private Sub Report_Open(Cancel As Integer)
strRptAmt = "SELECT tblRecovered.CL_CLAIMNO, tblIncident.INV_AMOUNT, " _
& "tblRecovered.AMOUNT, " _
& "tblRecovered.DATE, tblRecovered.CHEQUE_NO, tblRecovered.RECEIVED_SOURCE, " _
& "tblRecovered.JOURNAL_NO, tblRecovered.JOURNAL_DATE, tblRecovered.COMMENTS " _
& "FROM tblRecovered LEFT JOIN tblIncident ON " _
& "tblRecovered.CL_CLAIMNO = tblIncident.INCIDENT_ID WHERE (((tblRecovered.DATE) " _
& "Between #" & dtFROM & "# And #" & dtTO & "#)) ORDER BY tblRecovered.CL_CLAIMNO;"

Me.RecordSource = strRptAmt
End Sub

Anyone have any ideas?

 

[gwinn7]

Yeah, setup your selects as expressions and use the GROUP BY clause in your SQL.

Like...

SELECT myval1, First(InvoiceAmt) as Amt
FROM myinvoices
WHERE myinvoicedate BETWEEN [beg] AND [end]
GROUP BY myval1

Gary
gwinn7

 

[Chew407]

Do you have a working example of this? I'm not sure exactly how group by works.