need help with query - please.

[Guest]

i have a form that has listbox. I am setting the listbox's rowsource to a
query... through code. This form has multiple combo boxes for filtering
(what records in the listbox). everything is working perfect except one
filter 'GLAccountNo'. The amount field in te listbox is the 'total' amount
for all accounts associated with the ID (Req#). When the user filters on
account # the correct records are pulled but the amount for the record (shown
in the listbox) is not correct. Instead of showing the TOTAL amount (of all
accounts) for that record (req#) it shows only the amount for that account
(that the user filtered on). Here is an example: the user selects 'account1'
in the filter account drop-down.. the record (req#) has 2 accounts associated
with it (account1 and account2).. account1's amount = 10.00 and account2's
amount = 5.00. The record's amount (in the listbox) = 10.00 but instead
should = the TOTAL amount (15.00). Note: in my sql below.. 'mySQL' is the
'having' clause (this is where all the filtering criteria is).. thanks for
taking the time!

myRowsource = "SELECT tblCheckReq.ID AS [Req #], tblCheckReq.Date, " & _
" tblCheckReq.Company, tblCheckReq.State, tblCheckReq.TaxType, " & _
" tblCheckReq.PaymentType, tblCheckReq.TaxYear,
Sum(tblCheckReqAmtsToAccts.Amount) AS [Amount],
tblCheckReqAmtsToAccts.GLAccountNo, tblCheckReq.Issued " & _
" FROM tblCheckReq INNER JOIN tblCheckReqAmtsToAccts ON tblCheckReq.ID =
tblCheckReqAmtsToAccts.ID " & _
" WHERE tblCheckReq.DeletedCheckRequest = false GROUP BY tblCheckReq.ID,
tblCheckReq.Date, tblCheckReq.Company, tblCheckReq.State,
tblCheckReq.TaxType, tblCheckReq.PaymentType, tblCheckReq.TaxYear,
tblCheckReqAmtsToAccts.GLAccountNo, tblCheckReq.Issued " & _
" " & mySQL & " " & _
"ORDER BY tblCheckReq.ID DESC;"

 

[[MVP] S.Clark]

Best guess is the fact that you've included so many fields in the Group By,
makes me think that the sum is only being performed on one record at a time,
and not 2 or more like you desire. Try testing the query, apart from the
application and see what results you get.

 

[lp]

yea .. i think i need a nested query for my Total field
maybe.. because i think my
Sum(tblCheckReqAmtsToAccts.Amount) AS [Amount] in a group
by, will only sum the values that exist in the query,
nothing else.. say if i have an ID that has 2 accounts and
it only filters by 1 account only... so i am only getting
the sum of one account only.. And the thing is i've never
worked with a nested query.. so i'm not quite sure how to
go about this.. is this the right direction maybe?.. any
advice/help?.. thanks so much!

-----Original Message-----
Best guess is the fact that you've included so many fields in the Group By,
makes me think that the sum is only being performed on one record at a time,
and not 2 or more like you desire. Try testing the query, apart from the
application and see what results you get.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting.

i have a form that has listbox. I am setting the listbox's rowsource to a
query... through code. This form has multiple combo boxes for filtering
(what records in the listbox). everything is working perfect except one
filter 'GLAccountNo'. The amount field in te listbox is the 'total' amount
for all accounts associated with the ID (Req#). When the user filters on
account # the correct records are pulled but the amount for the record
(shown
in the listbox) is not correct. Instead of showing the TOTAL amount (of
all
accounts) for that record (req#) it shows only the amount for that account
(that the user filtered on). Here is an example: the user selects
'account1'
in the filter account drop-down.. the record (req#) has 2 accounts
associated
with it (account1 and account2).. account1's amount = 10.00 and account2's
amount = 5.00. The record's amount (in the listbox) = 10.00 but instead
should = the TOTAL amount (15.00). Note: in my sql below.. 'mySQL' is the
'having' clause (this is where all the filtering criteria is).. thanks for
taking the time!

myRowsource = "SELECT tblCheckReq.ID AS [Req #], tblCheckReq.Date, " & _
" tblCheckReq.Company, tblCheckReq.State, tblCheckReq.TaxType, " & _
" tblCheckReq.PaymentType, tblCheckReq.TaxYear,
Sum(tblCheckReqAmtsToAccts.Amount) AS [Amount],
tblCheckReqAmtsToAccts.GLAccountNo, tblCheckReq.Issued " & _
" FROM tblCheckReq INNER JOIN tblCheckReqAmtsToAccts ON tblCheckReq.ID
=
tblCheckReqAmtsToAccts.ID " & _
" WHERE tblCheckReq.DeletedCheckRequest = false GROUP BY
tblCheckReq.ID,
tblCheckReq.Date, tblCheckReq.Company, tblCheckReq.State,
tblCheckReq.TaxType, tblCheckReq.PaymentType, tblCheckReq.TaxYear,
tblCheckReqAmtsToAccts.GLAccountNo, tblCheckReq.Issued " & _
" " & mySQL & " " & _
"ORDER BY tblCheckReq.ID DESC;"

.