Database Design

[rnutts]

Hi

I have three tables

1 tblAssistFMWork in which is stored the fields JobNumber, SiteRefNumber, SurveyorNo
2 tbltable1 which has the field SiteRefNumber, Address1, Address2 etc
3 tblAssistFMSubJobNumbers which has the fields JobNumber, SubJobNumber.

JobNumber is the relationship between tables 2 and 3 and SiteRefNumber is the relationship between tables 1 and 2. There are other fields in each table but these are the relevant ones for this question.

Each JobNumber will have a minimum of one SubJobNumber but may have many. Each SubjobNumber starts at 1 for each new job so this may look like this in the table

JobNumber SubjobNumber
0001 1
0002 1
0002 2
etc
Each SiteRefNumber may have one or many JobNumbers

I wish to enter the date a SubJobNumber is completed and have been struggling with pulling the record into a form using a combo box because of the need to enter the JobNumber and also the SubJobNumber. So I decided to create a new table tblAssistFMJobDateCompleted which has the fields

JobNumber, SubJobNumber, DateJobCompleted

I need to report on a jobs status ie done or not done, but when I created the query for this it would only pull the records which have a completed date on them.
Could someone please give me some advice as to a way forward.

Thanks

Richard

 

[neileg]

You seem to have one too many tables. I'd expect you to have one for the jobs - tblAssistFMWork, one for the site - tbltable1 and one for the subjobs. Why do you need a second table to hold the date completed, why can't this be held in tblAssistFMSubJobNumbers?

Clearly, if the subjob has a date in the completed field, it is complete, if it is null it's incomplete.

if your fourth table only has records when the subjob is complete and you have used an inner join in the query, this will exclude subjobs with no entry in the fourth table - good enough reason to ditch this table.

 

[rnutts]

Thanks for that, I need some help with the form for selecting the record to add the completed date.

I was using a combo box for the Job number and another combo box for the SubjobNumber (using criteria to limit the second boxes selection)to select the right record but I end up overwriting data already in the or creating a new record.
I am using two boxes because we may enter the numbers using barcodes and these will need entering seperately.

Any thoughts on this

 

[neileg]

I'm not clear on why you are having the problems you describe. You will have to elaborate.

If you are using a form as you describe, then the user will be selecting the job number and then choosing the appropriate subjob number. That's fine. If you are using a scanner, then you won't need to enter the job number as this will be linked to the subjob number. Or am I misunderstanding?