Solved

Counting on Report

Posted on 2011-02-18
3
281 Views
Last Modified: 2012-05-11
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
Comment
Question by:pdvsa
  • 2
3 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Just put a textbox into the footer with the controlsource..
= Count(lctype)
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
Comment Utility
Sorry that's wrong - I didn't realise you were already grouping in the query
=SUM ( CountOflctype)
0
 

Author Comment

by:pdvsa
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now