pdvsa
asked on
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.amou nt)
AS SumOfamount, Count(tblLCTypeDropbox.lct ype) AS CountOflctype, tblLCTypeDropbox.LCType, Sum(IIf([CurrencyID]=1,[Am ount],Roun d([Amount] *[Exchange Rate]))) AS Sum1
FROM tblCurrencyExchange RIGHT JOIN (tblLetterOfCredit INNER JOIN tblLCTypeDropbox ON tblLetterOfCredit.LCType = tblLCTypeDropbox.ID)
ON tblCurrencyExchange.Curren cyID = tblLetterOfCredit.Currency
WHERE (((tblLetterOfCredit.DateO fIssueSB) Is Not Null) AND ((tblLetterOfCredit.Expire dYN) Is Null))
GROUP BY tblLCTypeDropbox.LCType;
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.amou
AS SumOfamount, Count(tblLCTypeDropbox.lct
FROM tblCurrencyExchange RIGHT JOIN (tblLetterOfCredit INNER JOIN tblLCTypeDropbox ON tblLetterOfCredit.LCType = tblLCTypeDropbox.ID)
ON tblCurrencyExchange.Curren
WHERE (((tblLetterOfCredit.DateO
GROUP BY tblLCTypeDropbox.LCType;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Sum([CountOflctype])
and it summed it. Damn that was easy overlook on my part. thanks
= Count(lctype)