report recordsource

[Guest]

this is my code (in open) to set my report's recordsource (mysql is the
having clause.. this is there because i have a form that has a listbox that
the user has filter options for... the listbox are there results and then
they can print their results on this report)... everything is good (reporting
on what it should) only one problem.. the report is to be grouped by company.
their are multiple accounts within the ID field.. these accounts have
amounts.. I need the detail to show one record per ID with the account
amounts summed into a Total Amount... I am currently getting a detail record
for every account. (i.e. ID #1 has 2 accounts associated with it with amounts
= 5.00 and 10.00.. i want only 1 record for 15.00.. but instead i am
receiving 2 records..one for each) here is my sql and i currently have my
report grouping on company and ID. thanks in advance!

rst.Open "SELECT tblCheckReq.Company, tblCheckReq.Date, tblCheckReq.ID,
tblCheckReq.State, tblCheckReq.TaxType, tblCheckReq.TaxYear,
Sum(tblCheckReqAmtsToAccts.Amount) AS Total,
tblCheckReqAmtsToAccts.GLAccountNo, tblCheckReq.Issued " & _
"FROM tblCheckReq INNER JOIN tblCheckReqAmtsToAccts ON
tblCheckReq.ID=tblCheckReqAmtsToAccts.ID " & _
"WHERE tblCheckReq.DeletedCheckRequest = False " & _
"GROUP BY tblCheckReq.Company, tblCheckReq.ID, tblCheckReq.Date,
tblCheckReq.State, tblCheckReq.TaxType, tblCheckReq.TaxYear,
tblCheckReq.DeletedCheckRequest, tblCheckReqAmtsToAccts.GLAccountNo,
tblCheckReq.Issued " & mySQL & " " & _
"ORDER BY tblCheckReq.Company DESC", CurrentProject.Connection

 

[Guest]

RESOLVED MYSELF.. THANKS ANYWAY!