Progressive Query

[NeilPattison]

In one of my tables I have a Yes/No field that users click if a job has been Issued [chkIssued] and a number field to show the week number (can be 1 - 52) that it was sent out [intIssuedWeek]. There is also a Yes/No field for when then job has been completed [chkCompleted] and a number field for the week number when completed [intWeekCompleted].

The week number that a job was completed in should be the one directly after the week it was issued in, for example a job issued in week 36 should be returned in week 37.

I have set up a query to show how many have been issued in a week and also how many were returned the following week. I want to be able to extend this query show that it shows the progress of work over the past 4 weeks, but I don't know how to do this. The main aim is to see how many jobs are outstanding.

Sample data: Week 36 Issued 20 Week 37 Completed 16

so say week 37 another 20 are issued I want it to show 24, as 20 have been issued and 4 are uncompleted from the previous week.

I hope I have made myself clear and not confused you too much. Any help on how I could do this would be greatly appreciated.

 

[lupins46]

I think you need a query like this....
WkNo is the issue week. Chnage it to use your own names.


SELECT WkNo, Count(Wkno) AS Issued, (Select Count(Wkno) from Mytable where Wkno <=B.wkno and isnull(WKCompleted)) AS Outstanding
FROM MyTable AS b
GROUP BY Wkno;

 

[NeilPattison]

I'm trying to produce a report that will show how many jobs are still outstanding with uncompleted jobs rolling onto the next week. An Example of the possible format would be

...............No of Issued Jobs .........No of completed jobs .......Jobs Outstanding

Week 39 ...........20 ...............................16 ................................4

Week 40 ...........20 ...............................19 ................................5

Week 41 ...........20 ...............................21 ................................4

Week 42 ...........20 ...............................10 ...............................14

Hope this makes sense.

 

[PHV]

Have a look at the IIf function:
Sum(IIf(some condition,1,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886