It has been a while since I last set up a new database and I am having trouble with a main aspect of the table design. My database is to keep a log of all projects within the company. The area I am having trouble with is how best to set out key data within the table(s).
The following fields will refer to all aspects of the project (from its start to finish):- Job number (unique to the job), date order received, product code, end user, site name, project manager, etc.)
However, there is a second level which relates to all of the above information, but where there are variables, i.e. multiple visits to site etc. The fields for this section include: Date on site, Installation time, Men required, remarks, etc.
What I need is that the main job number remains unique, but that the individual site visits (second level) are linked into the job number and the top level information.
I have set up the top and secondary levels in separate tables and using relationships have linked the relevant fields. The main problem I have come up against is that even though I can set up a new project if the job number field from the top level table is used, the database will not accept the same data via the secondary level field (which is linked by one-to-many relationship to the top level). I.e. the top level data has to be entered before any multiple visit secondary level data can be added. I cannot get the database to do it all in one go.
The second main problem I have is related to the first. There is a 3rd table which deals with materials ordering. It has a main link to the above mentioned top level table - the job number. Despite the relationship being set up seemingly correctly, data is never sent over the the materials' table
I am afraid I have baffled myself and am lost. I hope the above makes some sort of sense.
Just to confirm, in the relationships for all fields, referential integrity and cascading ceckboxes have all been ticked.
I hope someone is able to help!
The following fields will refer to all aspects of the project (from its start to finish):- Job number (unique to the job), date order received, product code, end user, site name, project manager, etc.)
However, there is a second level which relates to all of the above information, but where there are variables, i.e. multiple visits to site etc. The fields for this section include: Date on site, Installation time, Men required, remarks, etc.
What I need is that the main job number remains unique, but that the individual site visits (second level) are linked into the job number and the top level information.
I have set up the top and secondary levels in separate tables and using relationships have linked the relevant fields. The main problem I have come up against is that even though I can set up a new project if the job number field from the top level table is used, the database will not accept the same data via the secondary level field (which is linked by one-to-many relationship to the top level). I.e. the top level data has to be entered before any multiple visit secondary level data can be added. I cannot get the database to do it all in one go.
The second main problem I have is related to the first. There is a 3rd table which deals with materials ordering. It has a main link to the above mentioned top level table - the job number. Despite the relationship being set up seemingly correctly, data is never sent over the the materials' table
I am afraid I have baffled myself and am lost. I hope the above makes some sort of sense.
Just to confirm, in the relationships for all fields, referential integrity and cascading ceckboxes have all been ticked.
I hope someone is able to help!
