Update Query

[Guest]

I have a table and these are the columns I need to work with.

All fields except id are text from a mainframe.
I have added the nextassy column so it is blank

What I am trying to do if possible is to have a query look at the pn, read
its Ind value then look up to the first lower Ind number (ind -1) and take
that pn and populate it in the nextassy field.

Current
id Ind pn nextassy
01 00 122 ______
02 00 123 ______
03 01 2124 ______
04 01 2125 ______
05 02 2166 ______

What is needed
id Ind pn nextassy
01 00 122 ______
02 00 123 ______
03 01 2124 123
04 01 2125 123
05 02 2166 2125

id line 03 has a 01 ind so it looks at the first 00 above it and take that
pn and copys it to the nextassy field of id line 03.

I hope this makes some sense, if not let me know

TYI

 

[Guest]

id line 03 has a 01 ind so it looks at the first 00 above it and take that
pn and copys it to the nextassy field of id line 03.
Excel can look back.

Maybe your example is poor but if you have
05 02 2166 2125
how do it know that it should be 2125 and not 2124 and 02 minus one equals
01 that is both 2124 & 2125?
You can have a dozen parts contained in the nextassy (Next Higher Assembly).

 

[Guest]

Rather than including the nextassy column in the base table return it as a
computed column in a query. That way it will always reflect the data in the
other columns in the table regardless of any changes and thus avoid any
inconsistencies arising. I'm not clear whether the Ind values are sequential
(no gaps) or a run (possible gaps). Assuming the latter try this:

SELECT id, Ind, pn,
(SELECT MAX(pn)
FROM YourTable AS T2
WHERE T2.Ind =
(SELECT MAX(ind)
FROM YourTable AS T3
WHERE T3.Ind < T1.Ind))
AS nextassy
FROM YourTable AS T1;

If the Ind values are a sequence then it can be simplified as the second
subquery is no longer required:

SELECT id, Ind, pn,
(SELECT MAX(pn)
FROM YourTable AS T2
WHERE T2.Ind = T1.Ind-1)
AS nextassy
FROM YourTable AS T1;

Ken Sheridan
Stafford, England

 

[Guest]

It knows that the first lower ind number above it (the first 01 up) is what
it is looking for. The nextassy will only contain one number, it will be the
first lower ind number above it.

 

[Guest]

Ken, it is close but not quite, id #84-87 should have 65b00186 for their
nextassy, configur was on id #3.

line id 90 should have a nextassy of 174U9000 see line 87.

and yes there are blank lines in the data but I may be able to take them out
for now and readd them later. I can also change ind to a number, loose the
leading 0's and put them back on latter also it that would help.

this whole table is over 200,000 records long and 20 col wide

id Ind pn nextassy
82 00 65B00186
83 65B03000
84 01 BACS40R0 C0NFIGUR
85 01 BACS40R1 C0NFIGUR
86 01 174U1101 C0NFIGUR
87 01 174U9000 C0NFIGUR
89 174U9000
90 02 BACB28AK MS27253F
91 02 BACB30LH MS27253F
92 02 BACB30LK MS27253F
93 02 BACB30LT MS27253F

Thanks

 

[Guest]

Try:

SELECT id, Ind, pn,
(SELECT pn
FROM YourTable AS T2
WHERE VAL(T2.Ind) = VAL(T1.Ind)-1
AND T2.id =
(SELECT MAX(id)
FROM YourTable AS T3
WHERE T3.Ind = T2.Ind))
AS nextassy
FROM YourTable AS T1;

You'll probably need the VAL function calls in the first subquery (but not
in the second) if the ind values are text data type. This does assume that
the order of values in the id column is meaningful. They don't necessarily
need to be sequential but should be an ordered run.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Ken, I am getting the dreded "Data type mismatch in criteria
expression" message "LOL"

I have tried to change the Ind field to a number thinking that it could not
-1 on text but that still has not worked???

any idea what I might be doing wrong? I have even checked that column
spelling and case is the same, I even renamed my table "YourTable" just to
be sure

Here is the code in the query

SELECT id, Ind, pn, (SELECT pn
FROM YourTable AS T2
WHERE VAL(T2.Ind) = VAL(T1.Ind)-1
AND T2.id =
(SELECT MAX(id)
FROM YourTable AS T3
WHERE T3.Ind = T2.Ind)) AS nextassy
FROM YourTable AS T1;
--------
tabel defination is

id AutoNumber
Ind Text
pn Text

 

[Guest]

It’s the Nulls in the ind column which are the problem. Use the NZ function
to return a zero for each Null to overcome this:

SELECT id, Ind, pn,
(SELECT pn
FROM YourTable AS T2
WHERE VAL(NZ(T2.Ind)) = VAL(NZ(T1.Ind))-1
AND T2.id =
(SELECT MAX(id)
FROM YourTable AS T3
WHERE T3.Ind= T2.Ind)) AS nextassy
FROM YourTable AS T1;

You might find a join will perform better as this requires only one
subquery. This one to include the rows with Null in the ind column:

SELECT T1.id, T1.Ind, T1.pn, T2.pn AS nextassy
FROM YourTable As T1 LEFT JOIN YourTable AS T2
ON VAL(NZ(T2.Ind)) = VAL(NZ(T1.Ind))-1
WHERE T2.id =
(SELECT MAX(id)
FROM YourTable AS T3
WHERE T3.Ind= T2.Ind)
OR T1.ind IS NULL;

This one to omit the Null ind rows:

SELECT T1.id, T1.Ind, T1.pn, T2.pn AS nextassy
FROM YourTable As T1, YourTable AS T2
WHERE VAL(NZ(T2.Ind)) = VAL(NZ(T1.Ind))-1
AND T2.id =
(SELECT MAX(id)
FROM YourTable AS T3
WHERE T3.Ind= T2.Ind);

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Ken, It is close now but I have to do a qucik project that they want
done in 10 days so I will have to put this on hold until next week

Thanks for you help