I really hope someone can help me as I seem to be going around in circles at the minute.
I am a sort of newbie to access, but have set up databases in the past (although it’s a while and my skills are very rusty to say the least).

I need to set up a database that records issues for the production WorkCentre’s.
This data will then produce a report that will be used as the communication log between the shifts.

The next part is that for only 2 WorkCentre’s I need to record issues and measurements.

The flow is as follows:
Everything links back to the Job number & Product number.
From that you will have a mill roll number and then you may have multiple batches for that mill roll.
For each batch you may have up to 8 lanes (but this could be from 1 lane to 8lanes – varies from Product to Product)

So this is what I have been thinking?
Have 1 table that will record all the issues?
Then from that using Job number as the link, have a separate table for each of the WorkCentre’s that have measurements.(The same measurements and information is not carried out in these work centres)

I do have a separate table for Batch data, but not sure if this the way to go??

I have attached how I think the data should be layed out, but please give me your taught and ideas.

A few thoughts

if multiple batches are run off same mill roll, then your structure does not reflect that.

tblBatchData should have own ID as primary key, and not JobNumber. If you use JobNumber as primary key, then you only allow one batch per job. You could use BatchNumber as primary key,
and have JobNumber and MillROllID as foreign keys.

If you have multiple lanes for a batch, then you have a many-to-many relation between batches and lanes - that is not reflected in your tables.

I would have thought that measurements go together with a batch and not a job. So the relation would be Job 1-to-many Batch many-to-many Measurements

I am not sure how "lane" fits into the measurements.

So, if a mill roll is processed in multiple batches, and each batch along one or more lanes, and the measurements are done on each lane, then Job 1-to-many Batch many-to-many Lanes many-to-many Measurements

What is the relation between job and mill roll? one-to-one? Or multiple jobs off one mill roll?

Thanks a million for your help so far! That makes sense about the batch data, I couldnt figure out how best to lay that out!

So in answer to your questions:
The same millroll is used for the entire Job, so it is a one to one relationship.
Several batches are run from that millroll across several lanes.
The measurements are then taken for each lane.

Do I need to set up a Lane table? Or can I have a set up where Lane id is entered along with the results?

I have reattached the setup as per your suggestions!
Am I understanding your taughts?

If Job one-to-one millroll, then it is not necessary to put milrollID in tblBatchData, since you can just as well link the roll to the job in QATestData , ie. put the millrollID in that table (unless I have missed some information).

It is difficult to see what you have in your measurement tables. I would do it like this

WCxmeasurements
--------------------
MeasurementID (PK)
BatchNumberID (FK)
Lane
values....

Thanks a million for your help.

I put millroll into the batch table as it is only relevant for the 2 workcentres that it is necessary to carry out measurments. (The main QA test data table will be used to record issues that happen), which then links to the measurements table for only 2 workcentre!

I dont really understand the layout of the measurements part.
I have set up 2 seperate measurements tables (1 for each different workcentre) as different measurments are carried out in them. (2 different product types).

Hopefully this make sense!!!

How you recommend setting up the dataentry forms to record the data?