The way i see it is that you need at least three tables.
tblBatch --
BatchID - Autonumber - primary key
DateStart -
DateEnd - other fields as needed

tblWidget --
WidgetID - Autonumber - primary key - each widget id'd either by tag, stamp,
etch, etc.
other fields - special characteristics, tolerances, etc.

tblWidgetBatch --
WidgetBatchID - Autonumber - primary key
WidgetID - number - long integer - foreign key
BatchID - number - long integer - foreign key
TransDate -
Trans - In/Out (Yes/No)
Step - number - long integer
OutTo - Pick list - refinish, calibrate, scrap, ship, stock, etc (These
could have a BatchID)

tblWidget and tblBatch to have one-to-many relationship with tblWidgetBatch.

Use a query to determine want was in a batch at any given time or where any
widget was at a particular instance.

--
Build a little, test a little.


"JonB" wrote:

Here is my challenge. My work has a wretched almost unsearchable "database".
I am trying to cobble together my own relational database from that
monstrosity so that I can pull the information I need quickly and link it to
other databases we have. My biggest road block is in how we store data what
widgets are inside a batch. Here is the problem:

We make widgets. The widgets are made in batches. The widgets that are in
a batch some times are merged into new batches, split, or destroyed. I
really want to be tracking what happens to a widget, not a batch.

The database stores ONLY batch IDs. It records when a widget leaves or
enters a batch, but there is no way to query “hey, what was in batch 66 at
processing step #10”. The best you can do is find that at process step #1
widgets A, B, and C were put into the batch, at step #6 C was removed, and at
step #9 D was added, so at step #10 widgets A, B, and D must be in the batch.

So the evil work database would looks like this for the above:
Batch, Step, Action, Action Value
66, 1, add widget, A
66, 1, add widget, B
66, 1, add widget, C
66, 6, remove widget, C
66, 9, add widget, D


Here is the question, what sort of methodology should I be using to set up a
nice clean relations table. I want it so that there is a table that more or
less says at step #10, batch #66, and components A, B, and D. In my ideal
world I think I would want it to look like:

Step, batch, component
10, 66, A
10, 66, B
10, 66, D
11, 66, A

Is there a way to do this? I really want to be able to answer the question
of “what widgets were in batch XX at step ZZ”.