I think got it. The numbers look correct on a couple of test items. I'm shocked that it works, doesn't really make sense to me how the inner select lines up with the main select.
queryString +=
" SELECT " & _
" DISTINCT FITEMNO " & _
", SUM(FSHIPQTY) " & _
", AVG(FCOST) " & _
", AVG(FPRICE) " & _
", SUM(FSHIPQTY * FPRICE) " & _
", (SELECT FDESCRIPT FROM ICITM01.dbf i WHERE i.FITEMNO = h.FITEMNO) AS FREALDESC " & _
", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST)" & _
" FROM " & _
"(" & _
" SELECT " & _
" h.FITEMNO " & _
" , h.FSHIPQTY " & _
" , h.FCOST " & _
" , h.FPRICE " & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" UNION ALL " & _
" SELECT " & _
" v.FITEMNO " & _
" , v.FSHIPQTY " & _
" , v.FCOST " & _
" , v.FPRICE " & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
")" & _
" GROUP BY FITEMNO "
