I have a report that is slowly getting more and more complex. Problem one is the report uses several different related tables. Problem #2 is that in addition to printing out detail, their is a few "summary" fields that print in each detail "section/group" that comes from the SAME tables but with different date parameters.

With the help of others on here, I was able to get the correct data via a double SELECT statement with 2 cursors. Now the new problem is, I need data from the FIRST select statement, but the criteria of the second is removing ALL the pertinent records.

Here is the massive select statements....

Select client.clientid, ;
sum(iif(code.codetype="Time" and log.logdate iif((code.codetype="Expense" or code.codetype="WIP") and ;
log.logdate iif(code.codetype=="Billings" and ;
log.logdate sum(iif(code.codetype=="Billings" and ;
log.logdate>=gStartDate,log.rate,0.00)) as Billed, ;
sum(iif(code.codetype=="WIP Adjustment" and ;
log.logdate>=gStartDate,log.rate,0.00)) as WIPadj ;
from Timepro!log left join timepro!code on log.codeid=code.codeid ;
left join timepro!client on log.clientid=client.clientid ;
where log.firmid=gFirmid and log.logdate<=gEnddate and log.clientid<>0 ;
into cursor curstemp ;
order by client.clientid ;
group by client.clientid

Select curstemp.begbal, curstemp.billed, curstemp.wipadj, ;
client.clientnum as clinum, ;
client.clientname as cliname, ;
alltrim(str(client.clientnum))+" "+client.clientname as clistr, ;
code.codetype as codetype, ;
code.codenum as codenum, ;
AllTrim(str(code.codenum))+" "+code.codedesc as codestr, ;
Trim(employee.lastname)+", "+employee.firstname as empstr, ;
log.logdate as logdate, ;
log.time as logtime, ;
log.rate as lograte, ;
iif(code.codetype="Time" or code.codetype="Administrative",log.time*log.rate,log.rate) as logamt ;
from curstemp left join timepro!log on curstemp.clientid=log.clientid ;
left join timepro!employee on log.employeeid=employee.employeeid ;
left join timepro!client on log.clientid=client.clientid ;
left join timepro!code on log.codeid=code.codeid ;
where (log.firmid=gFirmid and log.employeeid<>0 and log.clientid<>0 and ;
log.logdate>=gStartDate and log.logdate<=gEnddate) ;
into cursor curswip ;
order by client.clientname, code.codenum


The first select basically calculates the beginning (prior to the report starting date "gStartDate"), billed and adjustment balances (in the current reporting period) and stores them in a cursor.

The second one creates the detail for the report and adds the 3 above fields to each row in the detail based on the clientid and groupings.

The report itself consists of a group header-which prints the beginning balance for that client. The detail lines for the client (not including the fields from the first cursor) and finally a group footer which prints the billed and adjustment balance fields from the first cursor.

This report works fine UNTIL their is no "detail" for the client for the current reporting period. If they have a beginning, billed or adjustment balance, the second SELECT removes these values (basically the entire record) from the final cursor because their is no matching detail records between those dates.

I have spent the better part of the day trying to manipulate the selects, joins, etc to get the data in the cursor. Somehow I need to have a record with those pertinent values from the first select NO MATTER what happens in the second select. I have tried full joins, right joins, left joins, various where clauses, etc to no avail. I either get WAY to many records or they are stripped completely. I can't find away to do this. Does anyone have any suggestions?

Peter Brama
West Pointe Enterprises

VFP is getting easier but STILL alot to learn!!