>This whole thing looks like some cross-tab exercise. How many lines you realistically expect in your report, i.e. how many invoices?
>
Yes, looks like a cross-tab to me too.
>>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
If it's not broken, fix it until it is.
My Blog