Tables, Relationships and Query design

[Ravek]

Guys,
Can any one please help me in figuring out how to achieve the following in Access 2002?
It’s about capturing Changes done to ‘Routes’ (Machining Operations) details for a manufactured part. A table in our ERP system holds the following fields:
‘Part_No’, ‘Op_No’, ‘Start_Date’, ‘End_Date’, ‘SetUp_Time’, ‘Run_Time’
And we are making changes to these fields to reflect reality, based on the shopfloor feedback. Changes could be modifying the ‘SetUp_Time’ or ‘RunTime’ or, starting or closing off an operation.
It’s OK for a Part to have multiple operations as long as the following rules are met:
1. ‘Op_No’ can not have duplicates where ‘Start_Date’ or ‘End_Date’ are blank
2. can have ‘Op_No’ duplicates using ‘Start_Date’ or an ‘End_Date’ as long as these dates do not overlap. It’s not mandatory to have both dates in a single record (‘Op_No’).

Before start of the project I took a snapshot of these Routes and made a table in Access (treating it as a Master table). I want to do the same periodically say every 15 days, and make a report to management showing the changes done.
I have tried ‘Many-Many’ relationships etc making a fresh table out of recent dumps but haven’t succeeded a lot.

Thanks in advance
(*I thank and appreciate everyone for their participation and contribution to these forums*)

Ravek

 

[SkipVought]

Ravek,

I'm not sure what your objective is.

Seems to me that you have a routing table, that is a template for the standard hours for manufacturing parts.

Then you have DATES for starting and ending operations. Recording date values, now you're into actual hours and location of the manuacturing process.

Then your have these snapshots of WHAT: the standards or actuals or both?

So what is the objective?


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Ravek]

Hello SkipVought,
If I haven't made my point clear, my objective is to capture the changes done to Routing Table in ERP itself, periodically. Snapshot of this table was exported and a table in Access was created before start of the project. Because once any of the fields are over written with new values the old values are gone. For example details of a part before start of project:
Part A, Op10, ..., ..., 0.5,0.1,

could now be looking like below:

Scenario-1: Part A, Op10, ..., ..., 0.6, 0.1,
or
Scenario-2: Part A, Op10, ..., 21/03/04, 0.5, 0.1,
Part A, Op10, 22/03/04, ..., 0.2, 0.3,

Here in Scenario-1, the 'SetUp_time' and 'RunTime's have been overwritten. I can not see what the old values were once done.
In Scenario-2,
The operation Op10 has been ended with an 'End_Date'
and an operation with same 'Op_No' Op10 has been started with a 'Start_Date' (This happens when we shift the machining operation from one machine to another using an 'Op_Code' field. There are many other reasons too)

I want to show the changes done to routings for a part, before and After like below.
Before After
Part_No, Op_No, ....... ..........

The Routing Table in ERP has thousands of records (Parts)and any of them could be changed. There are no templates but the first table/export is going to be treated as a Master table.
Capturing the changes done to a table in remote location using MS Access is the objective here.

Thanks again Skip...
Ravek

 

[SkipVought]

Then, why not INSERT every record into the table along with the SNAPSHOT_DATE. You could delete any record that had ALL the same values as the previous SNAPSHOT_DATE. This would give you a complete history based on the snapshots.

However, realize that TWO OR MORE changes might occur in a record between shapshots.

The systems I have been familiar with, capture each change (via a controlled change process). So each Change is documented. I guess it depends on the requirements. I have been in the aerospace industry.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Ravek]

SkipVought,
The company I am working for doesn't have the luxury of having enough resources for controlling/documenting changes like an Aerospace Industry has. But certainly our ERP system is capable. ECM module you are talking about is yet to be implemented in this medium size, market driven machine tool building company.

Honestly I have tried a few things before, including your suggstion, but the problem is joining the tables. Which field(s) should be Primary Keys?. 'Part_No' 'Op-No'both can have duplicates (as explained in my previuos reply)and 'Start_Date' or 'End_Date' fields could be blank. If I don't use any primary I end with too many records for a part.
Please give me some idea about the Table structure and Joining them.

Thanks

 

[SkipVought]

What other tables would you be relating to? I thought...

"I want to show the changes done to routings for a part, before and After "

So you have, for instance...
[tt]
Part_No Op_No SetUp_Time Run_Time Snap_Date
A 0010 0.40 0.0020 01/15/2004
A 0020 0.50 0.0015 01/15/2004
A 0010 0.45 0.0020 02/15/2004
A 0010 0.50 0.0012 03/15/2004
[/tt]
the history on Part_No A shows that Op_No 0010 has standards that changed on 2/15 & 3/15

But WHY would you include start and end times? That is a scheduling issue and not a standards issue -- or am I missing comething?

Maybe you want to store the Schedule History...
[tt]
Part_No Op_No Start_Date End_Date Ord_No Ord_Qty
A 0010 3/1/2004 3/1/2004 100 150
A 0020 3/1/2004 3/2/2004 100 150
A 0010 4/1/2004 4/2/2004 151 430
A 0020 4/2/2004 4/4/2004 151 430
[/tt]
In reality, I'd put this in at least 2 tables - one for order-level data and the other for order detail/op-actuals.

Where am I going wrong?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Ravek]

[tt]Hello Skip,
Sorry for the delay.
The first instance you have shown is exactly what I want to achieve but in a columnar format like below.[/tt]


[tt]Part_No Op_No SetUp_Time Run_Time Snap_Date SetUp_Time Run_Time Snap_Date
A 0010 0.40 0.0020 01/15/2004 0.45 0.0020 02/15/2004
A 0020 0.50 0.0015 01/15/2004 [/tt]

[tt]This will be the result of two tables by means of a query, the Master table with Snap_Date: 01/15/2004 and the table from snap shot on 02/15/2004. The same will be done for the changes done on 03/15/2004 by means of another query.[/tt]

[tt]Now, the Start_Date and End_Date are not for scheduling but to control the operations themselves. They allow us to duplicate Op_No(s) for a part. Here is an example:[/tt]

[tt]Part A when first designed had two operations, OP10 and OP20. OP10 was for a lathe-1 and OP20 for a mill-1. To start with, OP10 and OP20 did not have any start or end dates. OP10 had 0.25 SetUp_Time and 0.1 Run_Time. OP20 had 0.15 Setup_Time and 0.6 RunTime.[/tt]

[tt]The ERP table would look like below:[/tt]

[tt]Part No Op_No Start_Date End_Date Setup_Time Run_Time
A 0010 0.25 0.1
A 0020 0.15 0.6[/tt]

[tt]Snap shot of this was taken and a table created in MS access (Mater Table).[/tt]


[tt]After some time shop floor asked OP10 Run_Time to be increased to 0.2, now the ERP table would now look like below:[/tt]

[tt]Part No Op_No Start_Date End_Date Setup_Time Run_Time
A 0010 0.25 0.2
A 0020 0.15 0.6[/tt]


[tt]Designer then added a feature that made us shift the machining operation, OP10 to lathe-2. I would do this by using an End_Date and then creating another record with same Op_No for the part using a Start_Date (dates not to overlap). Either carry the old Setup_Time and Run_Times or assign new.[/tt]
[tt]The ERP table would now look like below:[/tt]


[tt]Part No Op_No Start_Date End_Date Setup_Time Run_Time Machine
A 0010 01/15/2004 0.25 0.2 Lathe-1
A 0010 02/15/2004 0.25 0.2 Lathe-2
A 0020 0.15 0.6 Mill-1[/tt]

[tt]Designer then deleted a feature that resulted in closing/ending OP20. OP20 record deleted.[/tt]
[tt]ERP table would now look like this:[/tt]

[tt]Part No Op_No Start_Date End_Date Setup_Time Run_Time Machine
A 0010 01/15/2004 0.25 0.2 Lathe-1
A 0010 02/15/2004 0.25 0.2 Lathe-2[/tt]

[tt]Such changes can be happening on number of parts. [/tt]


[tt]Hope I haven’t ended up confusing you more.[/tt]

[tt]Ravek
30/03/04 [/tt]

 

[SkipVought]

Ahhhh,

Start & End Dates are for Change Control. Any operation with and End Date is no longer valid.

I still maintain, that you should have a single table with headings
[tt]
Part_No
Op_No
Setup_Time
Run_Time
Snap_Date
Machine
Start_Date
End_Date - dont think you would need this
[/tt]
So a sequence of recording might be
[tt]
PN Op_No SetUp Run_Time Snap_Date Ma Start
A 0010 0.40 0.0020 01/15/2004 L1
A 0020 0.50 0.0015 01/15/2004 P1
A 0010 0.45 0.0020 02/15/2004 L1
A 0010 0.50 0.0012 03/15/2004 L2 3/10/2004
[/tt]
Initially Part A has 2 ops. Then on 2/15 op 10 setup changes. on 3/15, setup, run, machine & start changes. By implication, any time a change occurs on an op, the previous op record is no longer valid.

Here's a PivotTable Report of the data example above...
[tt]
Snap_Date Data
1/15/2004 2/15/2004 3/15/2004
PN Op_No Ma Start _SetUp _Run_Time _SetUp _Run_Time _SetUp _Run_Time
A 0010 L1 (blank) 0.40 0.0020 0.45 0.0020
L2 3/10/2004 0.50 0.0012
0020 P1 (blank) 0.50 0.0015
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Ravek]

Hello Skip,
Good suggestion of using a pivot table but the problem is maintaining all the changes in one single table. Because
changes are happening in ERP table not in my Access table and i can only take periodical dumps.

you wrote...
"Initially Part A has 2 ops. Then on 2/15 op 10 setup changes"...
In case of overwriting as above, the old Setup_Time are gone, and this is the whole reason why i took a snap shot before changes started happening (start of the project)which i am referring to as a Master table. The master table or the first dump has a Snap_Date inserted in Access. Please tell me how will i capture changes done to some of the parts/ops.
Is there a way i can merge the second or the consequent dumps into the first one without disturbing the original details?

Thanks again

Ravek

 

[SkipVought]

Is there a way i can merge the second or the consequent dumps into the first one without disturbing the original details?"

This is EXACTLY what I am suggesting. NOTHING gets overwritten. Each record is made unique by the Snapshot Date.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Ravek]

That worked brilliant and thanks.

Ravek

 

[Ravek]

Skip,
There is only a small problem though...
I can't (don't know may be)show only the ones that have changed. Any suggestions?

Thanks
Ravek

 

[SkipVought]

You're probably going to have to run a procedure that compares a Part_No's Op_No This SnapDate with the Previous SnapDate. If all values are identical, then delete the record.
[tt]
PN Op_No SetUp Run_Time Snap_Date Ma Start
A 10 0.4 0.002 01/15/2004 L1 01/00/1900
A 10 0.45 0.002 02/15/2004 L1
A 10 0.5 0.0012 03/15/2004 L2 38056
A 20 0.5 0.0015 01/15/2004 P1
A 20 0.5 0.0015 02/15/2004 P1
A 20 0.5 0.0015 03/15/2004 P1

[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884