Association in Tables

[Brutal]

Hello

I have an existing database that controls some production work and each job has a unique job number, but sometimes before somebody can start work on a particular job listed in the database a previous job has to be completed first.
I would like to associate ie job no. 5 as a sub job of job no. 10.
Then have the system tell me that job 10 can be started because job 5 has been marked complete.
The database currently has the ability to mark jobs complete and I go through the system manually and work out what jobs can be done next, obviously this is very slow and I know one of you experts has an answer - so please help me.

Many Thanks

Brutal

 

[dynamictiger]

I have some thoughts on this. You could set up another table of subjobs this would store master job number 10 and sub job number 5. This record would be updateable from the job entry form.

Depending on how you are set up in the rest of your structure, if Job 5 is auto added when Job 10 is added to the roster then you could set up an associated job field in your job table.

In either case you would query the asociated job at completion to find the next job to do.

HTH

 

[Brutal]

Stage 2

Thankyou

I have created the other table and then place a sub form in the main form to add sub jobs under the master job and it is working great, But (u know there had to be one) if the master job has 4 sub jobs and I have marked complete 3 of them and I go to the last sub job and mark it complete how can I flag that the master job is ready to build.
I thought that after marking it complete I would click the save record button and it could run a query that looks at my current job number, then searches for the occurance of that job number in the sub job table, look at the master kit number associated with it, search the sub job table for all occurances of the master job number, count the number of occurances ie. 4. count how many are marked complete, if they match then display something on screen - However this is as far as I can see - I have far to little knowledge of access to complete this stage on my own If you or anyone could help then you would be an absolute star.

Thanks Again

 

[cogent1]

This is not quite as difficult as you might imagine. It can be done with a very simple totals query.

If you were to reverse the logic of your flags (so that TRUE=Not completed), it could be done with just two fields. However if you wish to preserve the intuitive method of having a tick mark signify completion, it has to involve a calculation.

The principle here relies on the fact that to Access a True =-1
and a False =0.

So, e.g, if you have 1 complete subjob out of 5, the SUM of your check boxes will be -1. However, the COUNT of your check boxes will be constant at 5.

The DIFFERENCE between the SUM and the COUNT will equal the incomplete jobs, and when that total becomes zero, you can proceed with the main job. You can make the query run on every completed sub job , or whenever you wish, by activating a button on your form .


THE QUERY (Totals Query)

Main Job ID (Group By) (Key field from Main Job table)
DONE:Completed (Sum) Check box that flags completed sub-job
HOWMANY: Completed (Count) (Same field, different alias)
TODO: HOWMANY +DONE (Totals row blank)


The Plus sign is correct, because SUM of Completed is negative.


This query returns all main jobs and the status of the sub jobs. To return only the main jobs which have all subjobs completed, base another query on this one and set the criterion of the TODO field to 0 (ZERO).

If you're a bit hazy about anything request a sample database and I'll send

HTH

 

[Brutal]

Thankyou

Thanks to both of you for all your help, I really appreciate it. everything is working great now the pointers you made were great - Thanks again

Paul