Peter,

I'm glad you found the comma. I didn't see it. I've had SQL with a non-printing character imbedded in it somewhere and when the "same" thing was re-typed it worked. Add that to your debugging techniques.

I never use table names in report control expressions or in report variables. Just let Fox look for the fieldname in whatever table/cursor is open and selected. (Not sure what you mean by "temporary name" below.)

>Cindy,
>
>I tried what you said and copied the Select to the command window... the TABLE picker shows up immediatly. I think all the tables are open, I tried to USE each on listed and got a FILE IN USE message. I then closed all tables and USE them again (no FILE IN USE errors) and then tried the Select again. I still get the picker
>
>As for NOT using the cursor names in the report - then I assume that the variables we declared will be readily available... what about the field names CLIENT.FIRMID, CLIENT.CLIENTNAME, LOG.LOGDATE, etc. Should I store those in a temporary name as well?
>
>Thanks
>
>
>
>>Peter,
>>
>>I never put table/cursor names in my reports. That way you can run off any cursor. Some day someone will ask you for a special run of some particular time period or some particular client... so just use Billings, Collections, and Writeoff in your report control expressions.
>>
>>Do you get the table picker when starting the SELECT or when starting the report? Try from the command window with all of the databases and tables open. The select looks ok to me.
>>
>>>Cindy,
>>>
>>>Thanks for the info, that is what I was thinking... I worked late on this one and unfortunately it still doesn't want to work right. Here is the SELECT I wrote and I get a TABLE picker window and I don't know why.. Any suggestions?
>>>
>>>Select client.firmid,client.clientnum,client.clientname,log.logdate,;
>>> sum(iif(code.codetype="Billings",log.rate,0)) as Billings, ;
>>> sum(iif(code.codetype="Cash Receipts",log.rate,0)) as Collections, ;
>>> sum(iif(code.codetype="WIP Adjustments",log.rate,0)) as Writeoff ;
>>> from Timepro!client inner join timepro!log on client.clientid = log.clientid, ;
>>> inner join timepro!code on log.codeid = code.codeid ;
>>> into cursor cursARsum ;
>>> Order by client.clientname
>>>
>>>
>>>In the report, I put the EXPRESSION for the field as cursARsum.Billings etc is that correct?
>>>
>>>Thanks again
>>>
>>>
>>>>Peter,
>>>>
>>>>The absolutely most reliable way to do reports in Fox is to select the data into a cursor. Printing from related tables is a debugging nightmare.
>>>>
>>>>Do like this:
>>>>

>>>>SELECT Log.Client, ;
>>>>    SUM(IIF(Code.BillingRecord, Log.LogAmount, 0) AS TotalBilling, ;
>>>>    SUM(IIF(Code.CollectionRecord, Log.LogAmount, 0) AS TotalCollection, ;
>>>>    SUM(IIF(Code.WriteOffRecord, Log.LogAmount, 0) AS TotalWriteOff, ;
>>>>    FROM Log, Code ;
>>>>    INTO MyReportCursor ;
>>>>    GROUP BY Log.Client
>>>>

>>>>Now, if you are showing the detail lines, just SELECT the Client, Code, and Amount into the cursor. Then create report variables where the value to store is the IIF statement as above and reset when the Client changes.
>>>>
>>>>I do a lot of these reports and these methods are reliable. It's easy to debug when you have the exact data in a cursor and you can SUM in the command window to check the math.
>>>>
>>>>>I am writing a report that has 3 columns.... BILLING, COLLECTIONS, WRITEOFF... this report is to list the totals per client for these three columns based off records entered in a file I call LOG which is a transaction type file. The log file, in addition to storing dates and user, etc, stores a code ID and and amount. The code ID links to a master CODE file that stores the type of code it is (Billing, Collection, Writeoff, etc).
>>>>>
>>>>>Here is the scenario I am attempting to accomplish. Per client, I need to SUM all the LOG records whose matching code.type=Billing and place that in the BILLING column. Then I need to SUM all the LOG records whose matching code.type="Collection" and place that in the COLLECTIONS column, and finally the same for WRITEOFF.
>>>>>
>>>>>Currently, I tried just adding the files to the DE on the report, linking via the appropriate linking fields (ID's), and have a GROUP defined for the CLIENTID... I placed the actual fields in the GROUP FOOTER so that I wouldn't have a record listed for each LOG entry.
>>>>>
>>>>>Now the problem comes with the summing steps. I am not sure if I am overlooking something in the report writer for doing this (a specific EXPRESSION for a field and/or the CALCULATIONS section of a field or what. Nor am I sure if instead I should create a SELECT cursor before running the report and build the report of the cursor. Is this another one of those VFP items that have multiple ways of accomplishing it or is one the proper, correct, or only way of handling this.
>>>>>
>>>>>Thanks