Best way to normalise.

[tommywat]

I'm in the process of building a database that gona be used to record work done to a fleet of trucks,

now i have a sheet that lists each job and the relivant information and a sheet for the vehicles, now with each job there will be multiple parts and i'm not sure what would be the best way to lay it out.



the parts will be added with each job but due to price changes i don't just create a table of all the parts as when the price changes on a part then i will need to create a new record with the new price to avoid it affecting old jobs ( correct me if i'm wrong on this)

[ranman256]

You need a tables of:
vehicles...tVehicles
tJobs
tJobParts
tVehMaint, to track vehicle maintenance

TJobParts ,will most likely have keys from tVehicles....
[jobPartID]
[VehID]

so many vehicles can work 1 job.

[tommywat]

i think there may of been a misunderstander,

each jobID should be for work carried out on a single vehicle, so summin like

JobID(PK) - VehID - PartsIDs - Cost

VehID(PK) - VehDesc

PartID - PartCost

as i add a new job to the main JobID table i'll be adding a feew new parts to the partID table if the part dosen't already exist with the crrent price.

now what i need to knnow is if say i chnaged a price linked to a part would that change the JobID records that are linked to the part?

becouse i need a way to record to costs of each month and not have the old records affected buy new price chnages.

[ranman256]

Then you also need tParts table w price.
And if part is missing, then add button to add to tParts table and THEN add to tVehParts too.

[orange]

Tommy ,

When you are referring to "sheet" you are talking spreadsheet/Excel which is quite different than database/Access.
To get a good understanding of the concepts of table and database design, I recommend you work through this tutorial from RogersAccessLibrary. You have to work through it to get the experience. It does have a solution and it follows a procedure that you can use with any database.

For more info on normalization.

For even more info.

Good luck.