Adding a Count to a Query

[Big_Si]

Hi

I collect three column of data in a table as below

ID NAME JOB
1 DAVE 1
2 DAVE 2
3 JIM 3
4 JIM 4
5 SIMON 5
6 DAVE 6

I am trying to construct a query which will include a column showing a running total against each name. So that in the example above the result would look like this:

ID NAME JOB COUNT
1 DAVE 1 1
2 DAVE 2 2
3 JIM 3 1
4 JIM 4 2
5 SIMON 5 1
6 DAVE 6 3

the nearest I can get is either a summary showing a total count against each user. Can somebody advise me further

thanks

 

[Alc]

Any good?

SELECT ID, Name, Job, Count(*) AS JobCount
FROM [I]tablename[/I]
GROUP BY ID, Name, Job
ORDER BY ID, Name, Job;

The word Count may throw things out (I'm not sure if it's reserved?).

 

[Big_Si]

Cheers for the advice Alc

It doesn't quite work the way I need it to. This code creates the field in the query but allocates each record in the query with a JobCount value of 1

ID Name Job JobCount
1 Simon 1 1
2 Simon 2 1
3 Simon 3 1
4 Simon 4 1
5 Simon 5 1
6 Mark 6 1
7 Mark 7 1
8 Mark 8 1
9 John 9 1
10 John 10 1
11 John 11 1
12 John 12 1
13 John 13 1
14 Dave 14 1
15 Dave 15 1
16 Dave 16 1
17 Dave 17 1
18 Dave 18 1
19 Dave 19 1

Perhaps I didn't explain it correctly. I need it to show the count of the current job within the list. ie If Dave has 4 jobs they will show as 1, 2, 3, 4 respectively

thanks

 

[Alc]

Sorry, that's the unique ID causing the problem.
I wasn't paying attention and ignored the fact that each unique ID number will result in one record.
How about

SELECT Name, Job, Count(*) AS JobCount
FROM [I]tablename[/I]
GROUP BY Name, Job
ORDER BY Name, Job;

That should give the count per job, per person.

 

[Big_Si]

Not to worry.

Unfortunately this doesn't achieve the right result either. I suspect the fact that the job number is unique in each case results in 1 record also. If I take the "job" number out of the code then i get the total count of jobs allocated to each user but without the actual detail of which job is 1st 2nd 3rd to each user.

The end result is that I would like to use a cross tab query based on this to display "NAME" as the row header and "JobCount" as the column header. The daft thing is that I know it can be done as I have done it some years ago but can't remember how I did it, nor do I have that DB as a point of reference

 

[Big_Si]

Hi All

It seems that this question seems to come up a lot and for the most part remains unanswered or unclear for us less experienced developers. I have managed to coble together some code which seems to do the trick perfectly so thought I would post it should anybody else be in this situation in the future.

Create a new query in SQL view with the code below.

SELECT T.Id, T.Name, T.Job, (Select Count(*) from [Jobs Table] where NAME=T.NAME and ID<=T.ID) AS JobCount
FROM [Jobs Table] AS T
ORDER BY T.Name, T.Job;

Obviously this works for the table I am working with but would need to be modified accordingly for different table and field names.

Hope it helps