Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I just wanted to say how much I have enjoyed these forums. I am a new user and have a lot of elementary questions. I get quick answers with a friendly attitude..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Queries and JET SQL Forum

Create a new field based on values above select rows

thread701-1682925
Read More Threads Like This One
MrHeynow (TechnicalUser)
11 May 12 12:05
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 (TechnicalUser)
11 May 12 12:30
Do you have a field that contains a unique id? Something that determines the sort order?

http://lessthandot.com
MrHeynow (TechnicalUser)
11 May 12 12:50
No, it's an imported two field comma delimited text file.
Remou (TechnicalUser)
11 May 12 12:58
Can you re-import allowing Access to add a primary key?

http://lessthandot.com
MrHeynow (TechnicalUser)
11 May 12 14:19
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 (TechnicalUser)
11 May 12 14:36
You can try:

CODE

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 (TechnicalUser)
11 May 12 15:23
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 (TechnicalUser)
11 May 12 15:28
I see what I did and this does precisely what I needed Thanks so Much! [thumbsup2]
Remou (TechnicalUser)
11 May 12 15:34
Good. It might be safest to add ORDER BY to the inner query:

CODE

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

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Queries and JET SQL

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close