Need a quick answer to this question, pls:

strQuery2 = "SELECT AVG(b_total * exchrate) FROM tblProspects INNER JOIN tblEFHist "
strQuery2 = strQuery2 & "ON tblProspects.jobNo = tblEFHist.jobNo WHERE Month = '03/01/2007' "
strQuery2 = strQuery2 & "AND tblProspects.jobNo = '" & ws.Cells(counter, 1) & "';"

Examples:
==========================================
Date                 |    B_Total             |   ExchRate
--------------------------------------------------------------
01/03/2007      |    2,000                |   0.5
--------------------------------------------------------------
01/06/2007      |    7,000                |   0.3
--------------------------------------------------------------
01/03/2007      |     4,000               |   0.5
==========================================

I need it to RETURN the figure of AVG (2,000 * 0.5 + 4,000 * 0.5)... as in the avg of 01/03/2007

I'm afraid i MESSED up somewhere, as it doesn't GIVE the answer it should. I basically need the  calculated AVG of MULTIPLE rows ( as seen above, from tblProspects) but conforming to a particular date (from ANOTHER table, tblEFHist)... thanks in advance!