>>The query looks OK now. Are you sure you have JobHeads table?
>>
>>Also from what you have posted it looked like you wanted to do a cross-tab.
>>
>>>Thanks, Sergey. Yes, I'd just figured that and tried it after posting, as Agnes stated too. In that order I get "variable Jobheads not found".
>>>
>>>Oops, belay that. I'd forgotten to take the AND clause with the WITH. I'm just checking my results now.
>>>
>>>
>
>Thanks Naomi
>
>The Jobheads problem was my placement of the where clause, as replied to Sergey and Agnes.
>
>Maybe it does need a cross-tab but I've never done them in SQL and I thought I'd see how I get on here first. I'm getting resu8lts now but each sum is multiplied by 11 for some reasons (there are 12 records in my test set for teh given JobID).
>
>Ah, there are 11records with an inv. no. in JobAct for that jobID, ie the ones I'm interested in.
I'm only getting the sums of all records for the given InvID, and the values for any LabourID in JobHeads. multiplied by the number of recs for the JobID in JoobAct, whereas I need sums for each individual employee (labour).
eg JobHeads.LabourID "5MIF" has a JobHeads.Quantity of 10.00. Now there are 11 recs in JobAct for this same JobID, so the result I'm getting is Jobss.Booked = 110. (ie Quantity * no. recs in JobAct.
Similarly :
JobHeads.LabourID "1lRY" has a JobHeads.Quantity of 20.00. Now there are 11 recs in JobAct for this same JobID, so the result I'm getting is Jobss.Booked = 220.
What's the trick to get this right?
'ppreciate it.
- 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.