Where to start - manufacturing log

[frosty]

Hi, Ive pretty much zero access experience but I'm sure its the way I should go. I log a manufacturing process, where process operators record times, temperatures and pHs etc. on a paper batch log, this data is then manually entered into excel (by me) where all sorts of graphs etc are created.

I know my way around excel pretty well, but I want to miss out the data entry and get the data entered once, at source, by the process operator and move away from this multiple data handling, and try and mistake-proof the process a little more.

Are there any good places to start? (tutorials etc). Is access a decent option to record this data? I'd still like to create graphs etc, but know I can export the relevant data using access.

I'd like to make individual batch records via forms in access, and ultimately export this data to excel to graph. Records I shall require are along the lines of:

Batch number
lot number
time start
chemical strength
time at step 1
pH at step 1
temperature at step 1

time at step 2
pH at step 2
temperature at step 2


test result from step 2

time at step 3
pH at step 3
weight before addition
weight after addition
(therefore weight of addition)

total amount
finish time
operator name

etc etc etc

at present this is recorded on a 'batch sheet', which I'd like to as a more up to date process

if anyone could help me in any way at all that would be fantastic!!

Cheers

[rpeare]

There are a lot of things to consider here. The biggest thing is that access does not handle more than about 5 concurrent users very well, particularly when adding new records to any given table. Was your intended deployment that each manufacturing position have a PC that has an open copy of your database available for them to do their data entry in the computer rather than on a paper sheet?

There are also some procedural things like... does every single item you manufacture have 3 steps? If they do can they be scrapped out at any given stage? Your question is really open ended but yes, a database would be a good solution for you. One other thing to note is this, anything you can do in excel, you can do in access, that includes producing graphs, reports, queries, etc.

[frosty]

There are a lot of things to consider here. The biggest thing is that access does not handle more than about 5 concurrent users very well, particularly when adding new records to any given table. Was your intended deployment that each manufacturing position have a PC that has an open copy of your database available for them to do their data entry in the computer rather than on a paper sheet?

There are also some procedural things like... does every single item you manufacture have 3 steps? If they do can they be scrapped out at any given stage? Your question is really open ended but yes, a database would be a good solution for you. One other thing to note is this, anything you can do in excel, you can do in access, that includes producing graphs, reports, queries, etc.

Thanks for the input. There are only 3 reactors, reactor 1 makes products A or B, reactor 2 makes product A or B, and reactor 3 makes products C or D. These are individual processes, independant of each other. If it were easier I could have a PC at each reactor, with unique databases for each product at said reactor. I realise I could just get this data put into excel, however a form would be a lot neater and easier to limit typo's etc.

I wouldn't really require separate users, maybe just a name of operator at certain stages (eg starting batch, finishing batch which I could be recorded on a form when required).

Even If I started with product A on reactor 1 as an independant trial it would be a massive progression, or even entering of the weights of materials used (as the process is just like a recipe being followed, there are slightly differing weights of raw materials used - and this all needs to be recorded)

Again, thanks thus far.

[rpeare]

Alright I'm going to give you some general advice as you build your application.

First, do not put spaces in field names or object names (names of tables, reports, queries etc) it's just going to make your programming life easier to avoid it.
Second, do not use any reserved words (date, time, left), instead use compound filed names/object names (Start_Date, Start_Time, Left_Margin). There are a lot of reserved words you can find on line, just stay away from using them as object names.
Third, do not store any calculated values in your tables, that's what queries or formulas on reports/forms are for
Lastly, think about how to use the minimum space possible to store all the data you want to track.

In your case can you have multiple records per batch?
Can you have multiple lot numbers per batch?
Can your processes have more or less than 3 steps?
Can a lot be scrapped out before it completes or are things scrapped out of your system after manufacturing is complete?
How are you going to handle scrap?

I am making some guess here but I'd think that you may have a batch with multiple lots per batch and (based on your original post) that there is only one record per lot. Further, you have no scrap (it's not indicated in your original post so I don't know if or how you handle it)

If that's the case you would have a structure something like this:

tblBatches (batchID as an autonumber and primary key field)

BatchID  Batch_Number  Other Batch Level related fields ----->
1        AA-0001
2        AA-0002
3        BB-0001

tblLots (LotID as an autonumber and primary key field)

LotID  BatchID  Lot_Number Other Lot related fields ------->
1      1        L001-001
2      1        L001-002
3      2        L002-001
4      2        L002-002

The table tblLots would contain your start time, end time of each of the steps along the way but no 'Total Amount' field, a total (whether total manufacturing time, total resources used, etc) can be calculated in a query for a report or form.