I would love some help on the design and implementation of a database as sort of a project management tool.
Here's the specifics of what I was thinking...
Table 1 Job Information- has all the information of a job (address, price, ect), primary key is job number. I want the numbers to auto populate with each new row, but I need it to start at a specified number.
Table 2 Job Orders- information about the actual order (order date, work crew, ect)
Table 3 Complete Jobs- completion information (date, if there were any issues during install, warranty number)
Table 4 Punch List- post inspection items that need to be fixed by the work crew assigned.
I want to be able to track and enter information based on job number (no jobs have the same number).
Form 1 Job Information- assigns job numbers and allows to enter specific information about the job into table 1.
Forms 2-4 (for tables 2-4) I want the forms to use job numbers to enter information, and for it to display identifying information about the job besides the job number (address, contract date, ect), but not to edit this information. Only be able to edit the corresponding information, i.e. order information, completion, ect.
I want to be able to pull lists or reports of what jobs are ordered but not complete listed by work crew, jobs that are complete but do not have a warranty number, and probably some other things as well.
Does this seem like a good design? How would I go about setting up the relationships so I can use the forms like I want?
I'm hoping this all makes sense.... Thank you so much in advance!!!!