Can't figure out the correct relationship / design

[rede96]

Not sure how to best explain this but here goes:

Imagine a production environment where there are various machines.

A product can start its journey on a 'production run' from any one of those machines and take a number of different routes (process steps) through the production environment, back and forth, same or different machines etc.

The only natural link is that each process step is assigned a unique order, and on each process step the previous order is captured.

So assuming letters for the order reference I would get a table similar to the following to import where CurrentOrder & PreviousOrder were just the first two columns, other columns would have other production data (like qty, machine etc.) I simply use an auto key when importing so each line of the data has a unique key. However the combination of Current order and previous order may repeat, just on different dates.

CurrentORder PreviousOrder
A A
B A
B A
C B
D C
D C
E D
Z A
Y Z
X Y


(All order numbers are actually alpha numeric in real life and unique for each process step)

I always know the last process step, hence the last current order, but how then would I create the link to all previous orders in a production run?

EDIT: sometimes an order is re-opened on a process step to remove a defect. In which case it is not always possible to know the last process step, other than the current order never appears as a previous order.

So from the above example above, Say the last process step is current order E, I would want to link the combinations of orders E&D, D&C, C,&B, B&A and A&A BUT not the combination of Z&A as that was a different production flow that just had the same origin.

Any help would be much appreciated.

Thank you!

 

[RainLover]

rede

That is too much information to imagine in one's head.

Suggest you do your best then post your relationships window as a pic together with any specific questions.

 

[Mihail]

Why you need to store the previous order ?
If yo have a sequence agsheytfd, if you say "y" I can say: the previous is "e".
So you need something to unique identify a sequence and something to put this sequences in order.
If, instead ABC, you will provide real data I'm pretty sure that we'll van help you better.

 

[rede96]

Thanks for the replies. I will try and get some data shortly but a non manufacturing way to think about it is you are going to get in your car and drive to a number of different addresses.

At each address you are going to record at least two things, the address you are at (Current Order) and the pervious Address (Previous Order) Each address is unique, ie. there will never be two destinations with the same address.

At each address you may also record things like date, time, house type, size, number of people in the house etc. Each time you do this, it is equivalent to one record in the data set.

So you set off and arrive at your first destination and you record the current address, and as there was no previous address, the current address is substituted. You then arrive at the next address and record that address and the last address you went to.

You may do this any number of times. So when you've done travelling all you have to identify all the address you visited is the link between the current address and previous address. No other link exists. (I didn't design this crap system by the way lol)

So the only way I can see all the address you have been to, is to start at the last address and trace back by each record using by the previous address until I arrive at the first address.

So in Access I would then like to display all these records that were linked to you last destination, so I can group /sum various data.

To complicate things I only know which was your last address, as it wont appear in the data as a previous address. Also there are many other drivers uploading their journeys in the same table, some may have even started at your first address, but just took different routes.

Does that make sense? I will get some data to upload shortly, which I know is the best way.

 

[Mihail]

At each address you are going to record at least two things, the address you are at (Current Order) and the pervious Address (Previous Order) Each address is unique, ie. there will never be two destinations with the same address.

No need to store previous address. If I come in your country and YOU are the driver if you halt the car somewhere is enough for me to write on a pepper this:
1. Address_1
2. Address_2
3. Address_3
4. Address_2 The second halt at this address
5. Address_5
And so on.

So I have the combination number-address that unique identify the station and I have the number that put this sequences in order.

At each address you may also record things like date, time, house type, size, number of people in the house etc. Each time you do this, it is equivalent to one record in the data set.

So you can use the Date/Time instead my numbers.

You may do this any number of times. So when you've done travelling all you have to identify all the address you visited is the link between the current address and previous address. No other link exists. (I didn't design this crap system by the way lol)

It is more one link my friend: the unique travel or... the product.

Travel_1
1. Address_1
2. Address_2
3. Address_3
4. Address_2 The second halt at this address
5. Address_5
And so on.

So the only way I can see all the address you have been to, is to start at the last address and trace back by each record using by the previous address until I arrive at the first address.

Or by reading the list for a certain travel (product).

Does that make sense?

Yes, it does. The same question for you regarding my approach.

 

[rede96]

@Mihail

Thanks for that, but it doesn't work. I probably haven't done a good job of explaining it.

I’ve had a play and think I have developed the right structure but the problem is that we don't know the journey and we can't record the data in any other way than it received. That's just because of the way our current business system records it.

So all we get is current order / Previous Order and we have to investigate the links between them in order to find out the journey.

I've uploaded a small sample to demonstrate.

There are just two tables. One is the data imported in its raw format, the other is end result of making the links (which I have done manually) between the Last order to be made in a production run and all the other associated orders.

There are two queries, one is how I would like the end result to look, the other was just to demonstrate how I found the last order in each production run. (Look up any orders that don't appear as a previous order)

What I am looking for is an automated way to create the tblLinkedData from just the import data.

Thanks again for your help

 

[RainLover]

Sorry but could you explain "Journey"

Also you refer to tables as One and the other.

Which is which.

I can't follow exactly what you want. What comes from where.

current order / Previous Order Is this the same thing or different.

 

[way2bord]

Not sure how to best explain this but here goes:

Imagine a production environment where there are various machines.

A product can start its journey on a 'production run' from any one of those machines and take a number of different routes (process steps) through the production environment, back and forth, same or different machines etc.

The only natural link is that each process step is assigned a unique order, and on each process step the previous order is captured.

So assuming letters for the order reference I would get a table similar to the following to import where CurrentOrder & PreviousOrder were just the first two columns, other columns would have other production data (like qty, machine etc.) I simply use an auto key when importing so each line of the data has a unique key. However the combination of Current order and previous order may repeat, just on different dates.

CurrentORder PreviousOrder
A A
B A
B A
C B
D C
D C
E D
Z A
Y Z
X Y


(All order numbers are actually alpha numeric in real life and unique for each process step)

I always know the last process step, hence the last current order, but how then would I create the link to all previous orders in a production run?

EDIT: sometimes an order is re-opened on a process step to remove a defect. In which case it is not always possible to know the last process step, other than the current order never appears as a previous order.

So from the above example above, Say the last process step is current order E, I would want to link the combinations of orders E&D, D&C, C,&B, B&A and A&A BUT not the combination of Z&A as that was a different production flow that just had the same origin.

Any help would be much appreciated.

Thank you!

Reminds me a of a typical family tree, but instead of tracing back through parents, you're tracing back through parent (former) processes.

You simply iterate a loop from your origin process to the preceding process. Please clarify what you're trying to do...

Also:

EDIT: sometimes an order is re-opened on a process step to remove a defect. In which case it is not always possible to know the last process step, other than the current order never appears as a previous order.

This is not good. No order should be reopened. A new order should be created to maintain the appropriate process flow. This Edit will destroy your ability to accurately maintain your records, at least as far as you've described your existing system.

Please confirm that you're looking to resolve the issue described in Quote.

Edit:
It's unusual that A's previous process is A.
B's process is repeated twice, both times having A as the preceding process. This strikes me as impossible.

You need to add an additional unique key / index to identify the order sequence. A date would work. Simply sort by chronology at that point.

 

[Mihail]

way2bord wrote:

It's unusual that A's previous process is A.
B's process is repeated twice, both times having A as the preceding process. This strikes me as impossible.

It is why I ask for real data.
It is not unusual that a part to be machining on a late, then on a milling machine then, again, on the same late. The machines can be the same but the process IS NOT the same.

 

[rede96]

way2bord wrote:
It is why I ask for real data.
It is not unusual that a part to be machining on a late, then on a milling machine then, again, on the same late. The machines can be the same but the process IS NOT the same.

The real data would say the same. As motioned the first step in the process does not have a previous process step, so the current order is substituted for the previous order.

So when you see the same order as both current and previous it's how you know that was the first process step.

 

[rede96]

Reminds me a of a typical family tree, but instead of tracing back through parents, you're tracing back through parent (former) processes.

Yes, that is pretty much what I need to do. So equivalent to looking through all the records until I find someone who has no children (The last step in the process) and trace back through the records to find all the former parents (or in the real life case to the first process step) make a table that links them, and then add up the total wealth for all family members. (In the real life case this could be adding up production quantities, waste, time spent or other such data.)

The problem now is how do I automate this process in the database? I think I'll have to write some code that can do this as I don't know of a way to do it using action queries.

This tractability problem is a known issue in our business system. I don't know which bright spark signed off on it, but obviously someone with no actual production experience lol. Its crazy that the system doesn't create the link automatically.

Edit:
It's unusual that A's previous process is A.
B's process is repeated twice, both times having A as the preceding process. This strikes me as impossible.

Yes, this is just a quirk of how the system deals with the first process step, as there isn't a previous order, it assigns the current order to that field. But that is actually quite handy as it gives me an easy way to find the first process step, as the order number is the same in both fields.


B can be repeated twice or many times as I missed the date field out. But basically the process step can be spread over a couple of days and sometimes the process may even by interrupted and delayed for weeks, then completed later. It still has the same data (e.g. current order, previous order, etc) but just different dates.

This is not good. No order should be reopened. A new order should be created to maintain the appropriate process flow. This Edit will destroy your ability to accurately maintain your records, at least as far as you've described your existing system.

In the real life case, if an order is re-opened, which basically means they run it through a different process step to take out defects, the order is prefixed with a 'D', so is in effect a new order.

 

[way2bord]

Correct me if I've misunderstood...

Given your data:

CurrentORder PreviousOrder
A A
B A
B A
C B
D C
D C
E D
Z A
Y Z
X Y

You have 2 sequences you wish to "sum up".

A-A-Bx2-C-Dx2-E (Uniquely ID'ed as : group 1)
A-Z-Y-X (Uniquely ID'ed as : group 2)

You want query / code assistance to auto generate the two groups above?