Problem with Append/Update Query

[Alec Green]

Hi, hope you can help - this is a bit long winded, so hope you understand:

I have a job monitoring database, and have 3 tables tblJOBS with StockNo,
Description, Qty, Job No (this is actually linked from another database),
tblJOBSTATUS with Job No, Current Status and JobColour and finally tblCOLOUR
with StockNo and JobColour. By linking tblJOBS and tblCOLOUR in a query I
have all the info StockNo ,JobNo and Colour - I now need an append or update
query to update the JobColour in tblJOBSTATUS. I need this as different jobs
for the same StockNo can be different colours.

Hope you can help as it is doing my head in

Thanks

Alec

 

[John Vinson]

Hi, hope you can help - this is a bit long winded, so hope you understand:

I have a job monitoring database, and have 3 tables tblJOBS with StockNo,
Description, Qty, Job No (this is actually linked from another database),
tblJOBSTATUS with Job No, Current Status and JobColour and finally tblCOLOUR
with StockNo and JobColour. By linking tblJOBS and tblCOLOUR in a query I
have all the info StockNo ,JobNo and Colour - I now need an append or update
query to update the JobColour in tblJOBSTATUS. I need this as different jobs
for the same StockNo can be different colours.

Hope you can help as it is doing my head in

I think the problem is that - if the JobColour is a property of the
JOB, and can be derived from the stockno - then it simply *should not
exist* in tblJobStatus. Just create a Query joining tblJobStatus,
tblJobs, and tblColour to pick up the colour for that job. Storing the
information redundantly risks having a job be Red in tblColour, and
Blue in tblJobStatus - and that would make your boss see red, and YOU
turn blue!

John W. Vinson[MVP]