Create a new field based on values above select rows

[MrHeynow]

Is this even possible? I want to make a new table with three columns from the data sampled in Field1 and Field2. Two columns are to be the same, the third column is to be the record number found below the data and to the right each appearance of the word “Record” in Field1 in found in Field2? Field3 is what is desired but I only have the first two fields.

Field1 Field2 Field3
A03 Sugar 12333
N03 12.3 12333
B03 Jack 12333
B04 Armstrong 12333
Record 12333 12333
A03 Butter 12356
N03 1.75 12356
B03 Sally 12356
B04 Jones 12356
C06 Akron 12356
Record 12356 12356
A03 Green 12346
N03 15 12346
B03 Holly 12346
B04 Bear 12346
F06 OH 12346
Record 12346 12346

 

[Remou]

Do you have a field that contains a unique id? Something that determines the sort order?

http://lessthandot.com

 

[MrHeynow]

No, it's an imported two field comma delimited text file.

 

[Remou]

Can you re-import allowing Access to add a primary key?

http://lessthandot.com

 

[MrHeynow]

Yes, however it would suplly an Auto Numbered field where as the initial table would now appear like.

ID Field1 Field2
1 A03 Sugar
2 N03 12.3
3 B03 Jack
4 B04 Armstrong
5 Record 12333
6 A03 Butter
7 N03 1.75
8 B03 Sally
9 B04 Jones
10 C06 Akron
11 Record 12356
12 A03 Green
13 N03 15
14 B03 Holly
15 B04 Bear
16 F06 OH
17 Record 12346

 

[Remou]

You can try:

SELECT t.ID, t.Field1, t.Field2, 
(SELECT TOP 1 Field2 FROM Imp q WHERE  Field1="Record" AND q.ID>t.ID) AS Expr1
FROM Imp AS t
WHERE (((t.Field1)<>"Record"));

http://lessthandot.com

 

[MrHeynow]

I gathered t. = table but is there a query needed ? I dont think I'm understanding....

(SELECT TOP 1 Field2 FROM Imp q WHERE Field1="Record" AND q.ID>t.ID) AS Expr1
FROM Imp AS t

 

[MrHeynow]

I see what I did and this does precisely what I needed Thanks so Much!

 

[Remou]

Good. It might be safest to add ORDER BY to the inner query:

SELECT t.ID, t.Field1, t.Field2, 
(SELECT TOP 1 Field2 FROM Imp q WHERE  Field1="Record" AND q.ID>t.ID ORDER BY q.ID) AS Expr1
FROM Imp AS t
WHERE (((t.Field1)<>"Record"));

http://lessthandot.com