Detail of Records does not equal Total records

[henp]

I have a report I'm running & I've checked my database and it has fewer records in it that my report says it has. My total & Grand total does not match my detail records always by one. Detail records is correct totals are worng. My report is based on the following query:-

Can anyone help!

Thanks,

Kirsten.

SELECT Commentcalls.[Order #], Commentcalls.[Comment Date], Commentcalls.[Comment Time], Commentcalls.[First Name], Commentcalls.[Last Name], Commentcalls.code, Commentcalls.csr, Count(Commentcalls.code) AS CountOfcode
FROM Commentcalls
GROUP BY Commentcalls.[Order #], Commentcalls.[Comment Date], Commentcalls.[Comment Time], Commentcalls.[First Name], Commentcalls.[Last Name], Commentcalls.code, Commentcalls.csr
HAVING (((Commentcalls.code)="01" Or (Commentcalls.code)="02" Or (Commentcalls.code)="03" Or (Commentcalls.code)="04" Or (Commentcalls.code)="05" Or (Commentcalls.code)="05" Or (Commentcalls.code)="06" Or (Commentcalls.code)="07" Or (Commentcalls.code)="08" Or (Commentcalls.code)="09" Or (Commentcalls.code)="10" Or (Commentcalls.code)="11" Or (Commentcalls.code)="12" Or (Commentcalls.code)="13" Or (Commentcalls.code)="14" Or (Commentcalls.code)="15" Or (Commentcalls.code)="16" Or (Commentcalls.code)="17" Or (Commentcalls.code)="18" Or (Commentcalls.code)="19" Or (Commentcalls.code)="20" Or (Commentcalls.code)="21" Or (Commentcalls.code)="22" Or (Commentcalls.code)="23" Or (Commentcalls.code)="24" Or (Commentcalls.code)="25" Or (Commentcalls.code)="26" Or (Commentcalls.code)="27" Or (Commentcalls.code)="28" Or (Commentcalls.code)="29" Or (Commentcalls.code)="30" Or (Commentcalls.code)="31" Or (Commentcalls.code)="32" Or (Commentcalls.code)="33" Or (Commentcalls.code)="34" Or (Commentcalls.code)="35" Or (Commentcalls.code)="36" Or (Commentcalls.code)="37" Or (Commentcalls.code)="38" Or (Commentcalls.code)="39" Or (Commentcalls.code)="40" Or (Commentcalls.code)="41" Or (Commentcalls.code)="42" Or (Commentcalls.code)="43" Or (Commentcalls.code)="44" Or (Commentcalls.code)="45" Or (Commentcalls.code)="46" Or (Commentcalls.code)="47" Or (Commentcalls.code)="48" Or (Commentcalls.code)="49" Or (Commentcalls.code)="50" Or (Commentcalls.code)="51" Or (Commentcalls.code)="52" Or (Commentcalls.code)="53" Or (Commentcalls.code)="54" Or (Commentcalls.code)="55" Or (Commentcalls.code)="56" Or (Commentcalls.code)="57" Or (Commentcalls.code)="58" Or (Commentcalls.code)="59" Or (Commentcalls.code)="60" Or (Commentcalls.code)="61" Or (Commentcalls.code)="62" Or (Commentcalls.code)="63" Or (Commentcalls.code)="64" Or (Commentcalls.code)="65" Or (Commentcalls.code)="66" Or (Commentcalls.code)="67" Or (Commentcalls.code)="68" Or (Commentcalls.code)="69" Or (Commentcalls.code)="70" Or (Commentcalls.code)="71" Or (Commentcalls.code)="72" Or (Commentcalls.code)="73" Or (Commentcalls.code)="74" Or (Commentcalls.code)="75" Or (Commentcalls.code)="76" Or (Commentcalls.code)="77" Or (Commentcalls.code)="78" Or (Commentcalls.code)="79" Or (Commentcalls.code)="80" Or (Commentcalls.code)="81" Or (Commentcalls.code)="82" Or (Commentcalls.code)="83" Or (Commentcalls.code)="84" Or (Commentcalls.code)="85" Or (Commentcalls.code)="86" Or (Commentcalls.code)="87" Or (Commentcalls.code)="88" Or (Commentcalls.code)="89" Or (Commentcalls.code)="90" Or (Commentcalls.code)="91" Or (Commentcalls.code)="92" Or (Commentcalls.code)="93" Or (Commentcalls.code)="94" Or (Commentcalls.code)="95" Or (Commentcalls.code)="96" Or (Commentcalls.code)="97" Or (Commentcalls.code)="98" Or (Commentcalls.code)="N0" Or (Commentcalls.code)="N1" Or (Commentcalls.code)="N2" Or (Commentcalls.code)="N3" Or (Commentcalls.code)="N4" Or (Commentcalls.code)="N5" Or (Commentcalls.code)="N6" Or (Commentcalls.code)="N7" Or (Commentcalls.code)="N8" Or (Commentcalls.code)="N9" Or (Commentcalls.code)="NA" Or (Commentcalls.code)="NB" Or (Commentcalls.code)="NC&quot);

 

[dhookom]

First, I think all your "HAVING ..." should be a "WHERE " clause. Next, what happens when you add more calls? Do you expect to go in and edit this huge expression? Do you have a lookup table of all code values? If so, add a field that determines if it should be included in this query result or not. For instance:
tblCodes
==============
code primary key values like 01, N7, 98,...
Output yes/no

All of the above listed values should have the Output value checked. Then include tblCodes in your query and set the criteria under Output to -1. This all should be maintained in data, not complex expressions.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[henp]

Got it!!

Thanks Duane.