Bit of vague subject title there...lol

Just looking for some ideas of how to finally finish the last or what I hope to be the last bit of work on my database.
I have records (Jobs) that will display a Job status of either Active, Assigned, cancelled or complete

Active = Nothing done, waiting to be given out
Cancelled = Job not required anymore
Assigned = Given to a tradesman to carry out the work required
Complete = Tradesman completed the work & costs have been entered into the Job


At the moment Complete is the last stage that the record will get to & if done correctly should have a money value linked to it either from Labour & material costs.

My problem is that each month I need to run a report of all the completed jobs that WILL NOT have any more costs added to it & then invoiced & closed for good

I'm looking for the best way to achieve this as I know that unfortunately when a job is marked off as complete it may take some time for the cost details to come in & it may be more than one entry as more than one tradesman can work on the same job for any length of time.

I'm trying to avoid a job appearing in one Months report then again the next if more costs are entered.

so this is my plan

I create another Status called Invoiced & when I run the report for all cost movement for that month it will then run an update query & change the status to Invoiced

I just need to find a way to prevent people from still adding costs to a job after it has been marked as invoiced.


Does this sound like a good idea or is there a better way?

thanks in advance for any replies to this thread :-)

This sounds perfectly logical to me.

How are costs being entered? If they select the "job" from a combo box, you could easily have the combo not include invoiced jobs, or if you want them to be able to be selected, if the Job is invoiced, lock the record and display a message that they were too late.

HTH.

My biggest problem is educating people to update the job status accurately so to enable it to be invoiced at the end of each month
I don't want the situation of invoiced jobs being reopened because costs were left out & don't want to have duplicate orders created to cover the missing costs as I know this will happen alot.

The costs are entered into a table linked to the job id no
I have three of these tables one for labour one for materials & one for Subcontractor costs.

I then have a few union queries to join all the costs together then a final query to list all the costs on a one line entry per job

All I need to do Is put date parameters into the query then an update query to mark the tasks as Invoiced.


I think I'm a little stuck on the update query side as I need to work out a way to keep track of what I have just invoiced if you know what I mean.

Wonder if I should also include an invoiced date field to log the invoice date so I can keep track of what has been invoiced

Well, the main problem I see with what you have now is you have three tables for costs (labour, materials, Subcontractor). There should only be one costs table with a field to identify the type of cost. Then there would be no need for the union queries (which can slow down a database).

As far as tracking the invoices, you have a status of the job, do you have a status date? Do you have a history table to track the status over time?

I have three tables because I have three different applications in the database that are linked to these jobs

I have a form to log all the labour hours carried out by our engineers then a form to log all the work given out to subcontractors then finally a form for all the materials that have been booked out to these jobs, each form has it's own table to store the data to and all link to the Job no using a Job No id.


I don't have a field for the status date but but all movements are logged in the audit trail that I use anyway but that won't be any use for the purpose of controlling the movements, that's why I 'm thinking of having an invoiced date as that is the most important level to be at & I need to be able to control it.

I do not have a history table, all the jobs stay in one table but I have three different forms to view these jobs limiting them depending on it's status.

Active form - Only lists active & assigned jobs
History form - Only lists completed Jobs
All Form - Lists all jobs with all type of status types