The JOINs are a part of FROM clause. The WHERE clause goes after that.
In general, you should always try to keep a query clauses in the order provided in the help file. MS SQL Server, for example is very picky about that. Also it makes your query easier to read.
Select JobAct.OurInvID, JobHeads.JobID, JobHeads.LabourID, ;
SUM( jobheads.Quantity) as Booked, SUM( jobact.hours) as Charged, ;
Labour.Text ;
from jobheads ;
INNER JOIN JobAct ;
ON jobheads.JobID = jobact.JobID ;
INNER JOIN Labour ;
ON Jobheads.LabourID = Labour.ID ;
Where jobheads.JobID = "RTvo" ;
and not EMPTY( JobAct.OurInvID) ;
group by JobAct.OurInvID, jobheads.JobID, JobHeads.LabourID ,Labour.Text ;
order by JobAct.OurInvID, jobheads.JobID, JobHeads.LabourID ;
into cursor JobsS>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
--sb--
