[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Counting on Report

Posted on 2011-02-18
3
Medium Priority
?
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 77

Expert Comment

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

Accepted Solution

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

Author Comment

by:pdvsa
ID: 34926154
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

656 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