Hi
I am trying to create a database (Sql server back / Access Front) that will track components/parts fitted to machines, throughout the component/part life.
basically i have 3 tables
one table lists the components/parts with a unique part_id for each component/part serial number
the component/part will through its life have modifications and work done on it, and as such, this will change its part number
hence, i have a second table that records the part_id, part number and date of part number change (due to its modification)
also, the component/part will be fitted on different machines during its life
hence i have a third table that records the part_id, the machine (the part is fitted to), the date fitted, the date removed
what i want to be able to do is create a query that will tell me the part number of the component/part at the fitting and removal date.
i hear you ask, why not just add fields to the third table. well i am loading a lot of retrospective data, and i only want to have to change the part number history (second table) and let the db do the rest.
can any helpful person point me in the right direction. i would be most appreciative.
I am trying to create a database (Sql server back / Access Front) that will track components/parts fitted to machines, throughout the component/part life.
basically i have 3 tables
one table lists the components/parts with a unique part_id for each component/part serial number
the component/part will through its life have modifications and work done on it, and as such, this will change its part number
hence, i have a second table that records the part_id, part number and date of part number change (due to its modification)
also, the component/part will be fitted on different machines during its life
hence i have a third table that records the part_id, the machine (the part is fitted to), the date fitted, the date removed
what i want to be able to do is create a query that will tell me the part number of the component/part at the fitting and removal date.
i hear you ask, why not just add fields to the third table. well i am loading a lot of retrospective data, and i only want to have to change the part number history (second table) and let the db do the rest.
can any helpful person point me in the right direction. i would be most appreciative.
