If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Updating relationships
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”. |
#2
|
|||
|
|||
Updating relationships
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”. |
#3
|
|||
|
|||
Updating relationships
That table structure is fine and is pretty much what I had in mind, but I
guess where I get lost is how to do the query such that I get what I want. So, if my tblWidgetBatch is set up as you describe has the following: WidgetID, BatchID, Trans, Step A, 66, In, 1 B, 66, In, 1 C, 66, In, 1 A, 66, Out, 3 D, 66, In, 5 How do I find out what was in at step 4? I know I can query the batch for = 4 and see: A, 66, In, 1 B, 66, In, 1 C, 66, In, 1 A, 66, Out, 3 But what I really want to see is just B and C. Further, if I add another table. Say tblInstruction that is: InstructionBatchID Instruction Batch Tool So it looks like 1,1,66,WidgetStomper 2,2,66,WidgetPresser 3,3,66,WidgetPainter 4,4,66,WidgetTosser And I want to know what widgets ran through the WidgetTosser at step 4, how do I make that query? I want to see: batch,widget,instruction,tool 66,B,4,WidgetTosser 66,C,4,WidgetTosser Thanks for any help. "KARL DEWEY" wrote: 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”. |
#4
|
|||
|
|||
Updating relationships
Actually, to clarify, I want to be able to query and get:
widget,batch,instruction,tool A,66,1,WidgetStomper A,66,2,WidgetPresser A,99,3,WidgetHeater A,99,4,WidgetFreezer I really want to be able to see what tool each widget has gone through, but we only track what goes into a tool by batch. |
#5
|
|||
|
|||
Updating relationships
You need TransDate so as to see 'what was when'.
tblInstruction to have these -- InstructionID Instruction Tool Either add InstructionID to Step or Transaction. -- Build a little, test a little. "JonB" wrote: Actually, to clarify, I want to be able to query and get: widget,batch,instruction,tool A,66,1,WidgetStomper A,66,2,WidgetPresser A,99,3,WidgetHeater A,99,4,WidgetFreezer I really want to be able to see what tool each widget has gone through, but we only track what goes into a tool by batch. |
#6
|
|||
|
|||
Updating relationships
See the link to the picture. Maybe that will make more sense. Those two
tables have enough information to make that one query. I can't alter the two tables. That is all the info I have. That is just the way our POS database records data and nothing but an act of god, a CTO, and a few million dollars is going to change that. I want to be able to query those two tables and get the response in the bottom of the image. There is enough data in those two tables to build that, but I just don't know how to do it. http://img209.imageshack.us/img209/3760/qrytroubles.png "KARL DEWEY" wrote: You need TransDate so as to see 'what was when'. tblInstruction to have these -- InstructionID Instruction Tool Either add InstructionID to Step or Transaction. -- Build a little, test a little. "JonB" wrote: Actually, to clarify, I want to be able to query and get: widget,batch,instruction,tool A,66,1,WidgetStomper A,66,2,WidgetPresser A,99,3,WidgetHeater A,99,4,WidgetFreezer I really want to be able to see what tool each widget has gone through, but we only track what goes into a tool by batch. |
Thread Tools | |
Display Modes | |
|
|