make a table query

[Stevo911]

Hi

The problem:
There are two columns of product codes. The 1st is for the original part and the 2nd its replacement code.

eg. Orig Part Repl Part
A0000000299 83091446038
A0000000320 A0000002404
. .
. .

There are over 100,000 cases.

The problem is that there are codes in the 2nd column found in the first column which means that they in turn have also been replaced by another code.

eg. Orig Part Repl Part
a b
b c

What is needed is to convert the cases from the above form to a form something as follows:

eg. Orig Part Repl Part1 Repl Part2
a b c

(and so on until there are no more replacements.)

Any advice on a query that could be run or any directional advice would be greatly appreciated.
Thanks

 

[JoanneM]

Hi

Before we start does the record with part codes on it have record id.

or is the part code the unique Identifier for the record

regards

Jo

 

[Stevo911]

Hi Jo

Thanks for replying ...
The part code is the unique identifier for the record.

Steve

 

[earthandfire]

Could the problem extend beyond ReplPart2?

For example:

eg. Orig Part Repl Part
a b
b c
c d
...
...

to:

OrigPart ReplPart1 ReplPart2 ReplPart3 ... ... ReplPartx
a b c d

 

[Stevo911]

hey eathandfire,

yes it can.
By doing some basic queries it has already been seen that some parts have been replaced 8 times.

thanks

 

[JoanneM]

hi again

to make y theory accurat do you at any point eever have a repl partno in on record that was the original partno of another record.

This is important as data integrity could be compromised if I got this wrong, or am i missing the point,is this the object of the exercise- to see if integrity has already been compromised

jo

 

[Stevo911]

Hey Jo

To answer your question ...
There are many codes in the repl partno column which are found in the orig partno column.

exaggerated simple eg.

orig repl
a b
b c
c d
x z
d e

So, there is an issue here to locate the 'true' orig partno, in this case 'a' and then form a tree-type structure removing duplicates ....

eg

orig repl1 repl2 .......
a b c ......
x z

As far as i know, the system in place failed to keep a history of part codes being replaced and so the relationship between a b c d e (in the example) has been lost. This data has been dumped into access in the hope of finding a 'make table query' or something else that can recover the links.
Pls let me know if anything else is unclear.

Thank you

 

[earthandfire]

The problem appears to be therefore, that you do not know, ultimately, how many columns you would need. Additionally which column would then contain the "current" part number.

A possible solution would be to have a two column table which contains the "old part numbers" in one column and the "current part numbers" in the other column (which would be linked to the main table). Whilst this would circumvent the multiple fields problem, it wouldn't solve the problem identified by Jo.

If you have no history at all, then I'm not sure how you would solve that problem.

 

[JoanneM]

Hi again

just by chance was there a date field anywhere that recorded any of this other wise it's crunch time

if the guys monitoring the board don't mind me giving you my email send me a 1000 rec+ snapshot tbl zipped in an Mdb and I will send you the result this afternoon when I've checked my old code against it.

I hit loads of oops in the process but I ended up with a table listing
a) the original number
b) the number of times in a record it had been replaced
with another number
c) the number of times it had been replaced by itself
d) the number of times it had been used in other records

in a separate table I listed the other records the number had been used to replace the original

if this is of use and is your goal then I can be of some help otherwise as earth and fire says you are up a gum tree

jo