yes, thanks, Ed.
If the result cursor has more employees in it than there are columns across the (landscape) page to fit on the report then I do the following:
Say there are 10 columns of booked/charged data across the page. Then I go through the cursor and get each set of 10 records into a special cursor i keep just for the report.
I report form this "batch" of 10 records then get the next 10 from the cursor.
And so on till all the cursor recs have been printed.
BTW, sorry but I need to leave work in 20 mins, so maybe carry this on tomorrow after that.
>I can't say that I understand completely what this batching means. If let say you have 10 employees for some invoice then how it is reported? Please, be patient with me, after I get this idea I can start telling how report querying should be done.
>
>>Maybe I am. the rpt can handle 9 employee columns across but There may be more emp's than that. What I do in these circumstances is go through the results cursor in batches of (in this case) 9, filling a print cursor, then reporting that, then next batch, and so on.
>>
>>>I feel that you try to do it starting with wrong end :). Let's go slower. I need in some more data from you. How many columns do you expect in the report, i.e. how many employees it could be realistically; do you have a limit? If you have big number of employees per invoice, e.g. 10, then how you going to squeeze them into report form?
>>>
>>>>Don't know how many invoices, or how many employees.
>>>>Yes it does look like a x Tab. I've been trying even to get just a flat file of the join, then do my SUMming on that, and using that in the wizard but now I'm getting repeated patterns the CHARGED field.
>>>>
>>>>This is doing my head in and my rep no good in the new job :-(
>>>>
>>>>>This whole thing looks like some cross-tab exercise. How many lines you realistically expect in your report, i.e. how many invoices?
>>>>>
>>>>>>I'm trying to achieve the report below:
>>>>>>
>>>>>>
>>>>>> Derek Bob Angus ...
>>>>>>Inv. No. Booked Charged Booked Charged Booked Charged
>>>>>>_____ ______ _______ ______ _______ ______ _______
>>>>>>1 0.75 0.75
>>>>>>2 6 5.5 12 11
>>>>>>3 20.5 20.5
>>>>>>...
>>>>>>
>>>>>>
>>>>>>So I need to sum the hours worked on a job, under each invoice ID, for each employee (JobAct.Hours), versus the hours required for each (JobHeads.Quantity)
>>>>>>
>>>>>>First I'm getting "command contains unrecognised phrase/keyword" and I can't figure what's up.
>>>>>>
>>>>>>Here's my pathetic attempt at the SQL. I've come to realise that I could really do with a course on this. I can't tell how successful it is as it won't work but I'm sure it's no good.
>>>>>>
>>>>>>
>>>>>>Select JobAct.OurInvID, JobHeads.JobID, JobHeads.LabourID, ;
>>>>>> SUM( jobheads.Quantity) as Booked, SUM( jobact.hours) as Charged, ;
>>>>>> Labour.Text ;
>>>>>> from jobheads ;
>>>>>> Where jobheads.JobID = "RTvo" ;
>>>>>> and not EMPTY( JobAct.OurInvID) ;
>>>>>> INNER JOIN JobAct ;
>>>>>> ON jobheads.JobID = jobact.JobID ;
>>>>>> INNER JOIN Labour ;
>>>>>> ON Jobheads.LabourID = Labour.ID ;
>>>>>> group by JobAct.OurInvID, jobheads.JobID, JobHeads.LabourID ,Labour.Text ;
>>>>>> order by JobAct.OurInvID, jobheads.JobID, JobHeads.LabourID ;
>>>>>> into cursor JobsS
>>>>>>
>>>>>>
>>>>>>A help would be much appreciated.
>>>>>>
>>>>>>Terry
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.