Select Result One Record Per Job 2

[codrutza]

I have the table Jobs:
JNo JDate Sales
10 21/01/2015 10
11 21/01/2015 20
12 22/01/2015 30
13 23/01/2015 10
14 26/02/2015 10
15 26/02/2015 40
16 26/02/2015 30
17 27/03/2015 20
18 28/03/2015 40
19 29/03/2015 30
20 29/03/2015 60
40 22/01/2016 0
41 23/01/2015 50
42 24/01/2016 50
43 24/01/2016 60
44 23/01/2016 20
45 26/02/2016 10
46 26/02/2016 40
47 26/02/2016 50
48 26/02/2016 20

And the table Cons:
CNo CJNo
131 11
141 12
161 13
169 12
191 16
201 10
202 10
203 10
204 10
205 10
209 16
211 20
241 14
301 16
401 40
409 41
441 45
442 45
443 45
447 45
509 16

With
SELECT Jobs.JNo, Jobs.JDate, Jobs.Sales, Cons.CNo, Cons.CJNo
FROM Jobs LEFT JOIN Cons ON Jobs.JNo = Cons.CJNo
ORDER BY Jobs.JNo;
I get
JNo JDate Sales CNo CJNo
10 21/01/2015 10 203 10
10 21/01/2015 10 204 10
10 21/01/2015 10 205 10
10 21/01/2015 10 201 10
10 21/01/2015 10 202 10
11 21/01/2015 20 131 11
12 22/01/2015 30 141 12
12 22/01/2015 30 169 12
13 23/01/2015 10 161 13
14 26/02/2015 10 241 14
15 26/02/2015 40
16 26/02/2015 30 191 16
16 26/02/2015 30 301 16
16 26/02/2015 30 209 16
16 26/02/2015 30 509 16
17 27/03/2015 20
18 28/03/2015 40
19 29/03/2015 30
20 29/03/2015 60 211 20
40 22/01/2016 0 401 40
41 23/01/2015 50 409 41
42 24/01/2016 50
43 24/01/2016 60
44 23/01/2016 20
45 26/02/2016 10 441 45
45 26/02/2016 10 443 45
45 26/02/2016 10 442 45
45 26/02/2016 10 447 45
46 26/02/2016 40
47 26/02/2016 50
48 26/02/2016 20

But I only want to get
JNo JDate Sales

​

TotalCNoperJob
10 21/01/2015 10 5
11 21/01/2015 20 1
12 22/01/2015 30 2
13 23/01/2015 10 1
14 26/02/2015 10 1
15 26/02/2015 40
16 26/02/2015 30 4
17 27/03/2015 20
18 28/03/2015 40
19 29/03/2015 30
20 29/03/2015 60 1
40 22/01/2016 0 1
41 23/01/2015 50 1
42 24/01/2016 50
43 24/01/2016 60
44 23/01/2016 20
45 26/02/2016 10 4
46 26/02/2016 40
47 26/02/2016 50
48 26/02/2016 20

Is there a way that I can write an embedded select for Cons or something?

 

[SkipVought]

Maybe you help me with something else

Please start a new thread.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]