need help with variable data in lookup list

[ahken]

Hi all

I am a first time user of the microsoft access 2003 im currently stuck with my work and help of any kind would be greatly appreciated.

I am trying to create a database to track my spare parts' batch number. I would need to keep a record of which batch number have been used in which particular repair job. I created two tables in access one called 'model number' and the other one called 'spares'. I made a lookup list which looks up the spares list which i have made.

I would only have to key in the model number and the list of spares required appears at the bottom as a subform.
eg:
-----------------------------------------------------------------------
Job Number:
Date:
Model Number:

Part Number Description Quantity Batch Number
1)
2)
3)
-----------------------------------------------------------------------
This is where it gets tricky. When i enter the batch number for a a certain record, it saves correctly and i do not have any problems. However as i add a new record with a new batch number, access updates all the previous records making them all have the same batch number as the new one. Would it be possible to make access save the lastest batch number without affecting the previous records? and would it be possible to search which jobs have used a certain batch of spares?



Thank you everyone for your great help.

[NTC]

in DB parlance - you don't have your fundamental data normalization. Meaning you don't have your table definitions set up correctly.

Not knowing your line of business - and so just guessing based on usual terminology you need:

JobsTable
PartsTable
JobDetailTable

Not sure where the Batch Number fits in..... Parts Table is a static list of all your parts with any info that is non-changing and unique to each part.

JobsTable is the common, non duplicate core info about a Job.

JobDetail Table has the JobsTable cross reference and is a record per Part Used on a Job and becomes the source for subforms and such... So here you have a field that is the Job# (cross reference) and you have a field that is the Part# (cross reference).

[ahken]

Thank you for your prompt reply..i am a total newb thus i do not know how to describe to you what i have done. However, i have attached the db for your reference.. take a look at it thank u! oh initially i could edit the spaces in the form, but now i do not know why i cant anymore...it kinda changed over the weekend..sigh...

link for the database:
http://www.wikiupload.com/download_page.php?id=211670