Excel or Access

[hkybuff]

I'm trying to come up with a scheme to handle our product packed into a box...All parts in the box are the same, with serial numbers...serial numbers are coded with a lot number XXXXX- then serialized XX thru n, so the serial number format is XXXXX-XX. At final inspection the inspector inspects a certain number of parts for dimensions records those dimensions on a form, but also inspects them 100% for visual defects. When the parts are accepted they are packed to a box. Boxes contain either 28 or 35 parts. I'm looking for an easy way to keep track of those parts in the box. We use a bar code scanner which will read the full serial number, but are only using the scanner to record the serial number of the dimensionally recorded parts. I'd like to use the scanner to record all parts put into a box, sort the numbers and report them with maybe just a heading of the lot number and then only show the serial numbers of that lot with in the box. The boxes will always contain multiple lots as well. So the report might look like 18406, -01, -02, -03, -05, 10, 18695, -07, -08, -11...and so on. The main thing is I want to sort and group by lot number but only display the lot number once, and then the serial numbers associated with the lot number.

 

[ProfessorPorcupine]

I would suggest Access for keeping track of products like this. I would have a table for products, with lot number and serial number as separate fields (they can be put together in a XXXXX-XX format with a query), and boxes as a table, and lots as a table. This way you don't have any duplicate information, each part is listed only once and the boxes simply refer to that part in the parts table. If you change a part (e.g. it fails inspection), then this field only has to be updated once and all the boxes that have that part are automatically updated. This is also true with lots. Also, a part cannot be in a box if it does not exist in the product table, and you can't delete a product if it is in a box (referential integrity).

Explaining how to set up a database in Access is too much for a forum like this, but I would suggest the book Access 2010 The Missing Manual by Matthew MacDonald, which I own and found quite useful in learning Access. The time spent learning it will definitely be worthwhile.