A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Updating relationships



 
 
Thread Tools Display Modes
  #1  
Old March 31st, 2010, 06:51 PM posted to microsoft.public.access.tablesdbdesign
JonB
external usenet poster
 
Posts: 11
Default 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  
Old March 31st, 2010, 08:23 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old April 3rd, 2010, 06:52 AM posted to microsoft.public.access.tablesdbdesign
JonB
external usenet poster
 
Posts: 11
Default 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  
Old April 3rd, 2010, 07:51 AM posted to microsoft.public.access.tablesdbdesign
JonB
external usenet poster
 
Posts: 11
Default 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  
Old April 5th, 2010, 04:23 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old April 10th, 2010, 02:27 AM posted to microsoft.public.access.tablesdbdesign
JonB
external usenet poster
 
Posts: 11
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.