Progressive Query

[Neil_Pattison]

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.

 

[KeithIT]

You want to count how many jobs are outstanding? Or you want it to display the jobs which are outstanding? Sounds like you just need to run the query and use "Is Null" as your criteria for the chkCompleted field (or IntWeekCompleted depending on how your data is entered) Is this what you are looking for?

 

[Neil_Pattison]

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.

 

[Neil_Pattison]

I've done this now except for getting the numbers to roll over. Any ideas?

 

[neileg]

Reports support a running sum feature. Your outstanding jobs balance is the difference between the running sum of jobs issued and the running sum of jobs completed.

 

[Neil_Pattison]

Thanks I've sorted this now