<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v = "urn:schemas-microsoft-com:vml" xmlns:o = "urn:schemas-microsoft-com:office:office" xmlns:w = "urn:schemas-microsoft-com:office:word" xmlns:m = "http://schemas.microsoft.com/office/2004/12/omml"><HEAD>
<META content="text/html; charset=utf-8" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7601.17699">
<STYLE><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Comic Sans MS";
panose-1:3 15 7 2 3 3 2 2 2 4;}
@font-face
{font-family:"Lucida Calligraphy";
panose-1:3 1 1 1 1 1 1 1 1 1;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></STYLE>
</HEAD>
<BODY style="MARGIN: 4px 4px 1px; FONT: 12pt Arial" lang=EN-US link=blue vLink=purple>
<DIV>I believe this is one area where UniQuery is missing some functionality that exists in most SQL implementations. I don't know of any way to select separate records based on a calculation across records.</DIV>
<DIV> </DIV>
<DIV>As a work around, we have used two different approaches:</DIV>
<DIV>* create an intermediate (temporary) file that is keyed by vendor and contains either a list of the checks issued within your date range or a count of those checks (look at programs like CREATE.SUMMARY on our FTP site for a utility that can create these records)</DIV>
<DIV>* create an i-descriptor on your vendors file that fetches all checks written to that vendor...then use TRANS to get the check dates. Once you have the dates, you can use a subroutine like IF.COMPARE (also on our FTP site) to find all of the checks within a particular date range.</DIV>
<DIV> </DIV>
<DIV>I'm open to suggestions if someone has found an easier way to do this kind of select.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>
<DIV><FONT face=Arial>Dave Rotman</FONT></DIV>
<DIV><FONT face=Arial>Associate Vice President for Technology<BR>Chief Information Officer</FONT></DIV>
<DIV><FONT face=Arial>Cedarville University<BR>251 N. Main Street<BR>Cedarville, OH 45314<BR>rotmand@cedarville.edu<BR>voice 937-766-7905<BR>fax 937-766-8819</FONT></DIV>
<DIV> </DIV><BR><BR>>>> On 12/1/2011 at 2:52 PM, in message <8B65C5E2BF77A646B389712DB2611C195105552739@mail.campus.collegeofidaho.edu>, "Johnson, Keith M" <KMJohnson@collegeofidaho.edu> wrote:<BR></DIV>
<TABLE style="MARGIN: 0px 0px 0px 15px; FONT-SIZE: 1em" border=0 bgColor=#f3f3f3>
<TBODY>
<TR>
<TD>
<DIV style="BORDER-LEFT: #050505 1px solid; PADDING-LEFT: 7px">
<DIV class=WordSection1>
<P class=MsoNormal><I>Hi all,<o:p></o:p></I></P>
<P class=MsoNormal><I>I have been toying with making a report that only shows vendors that have three checks since our current FY.<o:p></o:p></I></P>
<P class=MsoNormal><I><o:p> </o:p></I></P>
<P class=MsoNormal><I>The CC XCOUNTER just returns a numeral ‘1’ per check record. Other than going in to a subr can this be done at the DB level? I think I have done it years ago but cannot think of the way it’s done…<o:p></o:p></I></P>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal>SELECT CHECKS WITH CHK.DATE GT '07/01/11' BY CHK.VENDOR AND WITH EVAL SUM 'XCOUNTER' GE 3 <I># blows up</I><o:p></o:p></P>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal>LIST CHECKS BREAK.ON CHK.VENDOR EVAL "IF (TOTAL XCOUNTER) GT 3 THEN TOTAL XCOUNTER" DET.SUP NOPAGE <I># blows up</I><o:p></o:p></P>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal><I>These two base lines get me my report but include the vendors with only one or more checks since my date.<o:p></o:p></I></P>
<P class=MsoNormal>=========<o:p></o:p></P>
<P class=MsoNormal>SELECT CHECKS WITH CHK.DATE GT '07/01/11' BY CHK.VENDOR<o:p></o:p></P>
<P class=MsoNormal>LIST CHECKS BREAK.ON CHK.VENDOR TOTAL XCOUNTER DET.SUP NOPAGE<o:p></o:p></P>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal><I>Ultimately I was thinking of doing something like the download example…<o:p></o:p></I></P>
<P class=MsoNormal>DOWNLOAD.TEST.PARAGRAPH<o:p></o:p></P>
<P class=MsoNormal>[…]<o:p></o:p></P>
<P class=MsoNormal>161: DISPLAY ===============================================================<o:p></o:p></P>
<P class=MsoNormal>162: DISPLAY 10 Testing EVAL and COL.HDG using comma-quote output<o:p></o:p></P>
<P class=MsoNormal>163: SELECT DOWNLOAD.TEST.FILE \<o:p></o:p></P>
<P class=MsoNormal>[…]<o:p></o:p></P>
<P class=MsoNormal>168<B>: EVAL "NUMERIC.FIELD+58"</B> \<o:p></o:p></P>
<P class=MsoNormal>169: COL.HDG "New Amount" \<o:p></o:p></P>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal><I>TIA -KJ<o:p></o:p></I></P>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Lucida Calligraphy'; FONT-SIZE: 13.5pt">Keith Johnson<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 10pt">Information Technology <o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 10pt">The College of Idaho <A href="mailto:kmjohnson@collegeofidaho.edu"><SPAN style="FONT-FAMILY: 'Times New Roman','serif'; COLOR: blue">kmjohnson@collegeofidaho.edu</SPAN></A><o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 10pt">2112 Cleveland Blvd Caldwell, ID 83605 <A href="http://www.collegeofidaho.edu/"><SPAN style="FONT-FAMILY: 'Times New Roman','serif'; COLOR: blue">http://www.collegeofidaho.edu</SPAN></A><o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 10pt">Business: 208.459.5209<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 10pt">Fax: 208.459.5543<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 10pt"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><B><SPAN style="COLOR: #1f497d; FONT-SIZE: 8pt">CONFIDENTIALITY NOTICE:</SPAN></B><SPAN style="FONT-FAMILY: 'Arial','sans-serif'; COLOR: #1f497d; FONT-SIZE: 8pt"> <I>This e-mail message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you believe you have received this e-mail in error please notify the sender. </I></SPAN><SPAN style="COLOR: #1f497d; FONT-SIZE: 8pt"><o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN style="FONT-FAMILY: 'Comic Sans MS'; FONT-SIZE: 10pt"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><o:p> </o:p></P></DIV></DIV></TD></TR></TBODY></TABLE></BODY></HTML>