• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

Counting on Report

Experts,

I seem to not be able to count the TOTAL of  [LCType]
I have it counting PER the LCType but need in addition the total Issued.
but need it to summ a GrandTotal Count of LCType.

eg:  I have 16 LCTypes and might have 50 of each type issued but can not sum this "50" for each type to arrive at a Grand total of say 1000 [LCTypes]  issued.  

I have tried "over Group", Over All in the Running Summ property of the field but does not summ it.  I know it requires modification to the SQL but this is tricky for me as it is outside of the normal canned stuff in MSAccess.


Thank you...
here is the SQL:
SELECT Sum(tblLetterOfCredit.amount)
         AS SumOfamount, Count(tblLCTypeDropbox.lctype) AS CountOflctype, tblLCTypeDropbox.LCType, Sum(IIf([CurrencyID]=1,[Amount],Round([Amount]*[ExchangeRate]))) AS Sum1

FROM tblCurrencyExchange RIGHT JOIN (tblLetterOfCredit INNER JOIN tblLCTypeDropbox ON tblLetterOfCredit.LCType = tblLCTypeDropbox.ID)
      ON tblCurrencyExchange.CurrencyID = tblLetterOfCredit.Currency

WHERE (((tblLetterOfCredit.DateOfIssueSB) Is Not Null) AND ((tblLetterOfCredit.ExpiredYN) Is Null))
GROUP BY tblLCTypeDropbox.LCType;
 summ
0
pdvsa
Asked:
pdvsa
  • 2
1 Solution
 
peter57rCommented:
Just put a textbox into the footer with the controlsource..
= Count(lctype)
0
 
peter57rCommented:
Sorry that's wrong - I didn't realise you were already grouping in the query
=SUM ( CountOflctype)
0
 
pdvsaProject financeAuthor Commented:
no actually that did it.  I had changed it to
Sum([CountOflctype])

and it summed it.  Damn that was easy overlook on my part.  thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now