get values from row below in the same query? 1

[evr72]

Hello,
I have a table that has the following fields
MACHINE # PartNum Stat Operator

the machines numbers are assiged for each job, right now they add the text "Next" to the machine number to know what is the next job. all this works well. I have been asked if I could add a column on the side of operator that displays the next part number

this is what I can see on my table

MACHINE #	PartNum	         Stat	          Oper
3004	           3045-G	Running	           Eric
3004 Next	   3036-H	Ins due 2/13	
2301	           2837-B	Running	
2301 Next	   2837	       Change Over	
2205	           SW3012BK	Running	           Sao
2205 Next	   SW3012BL

What I was asked to do is

MACHINE #	PartNum	   Stat	        Oper	Next Job
3004	        3045-G	  Running	Eric	3036-H
2301	        2837-B	  Running		2837
2205	        SW3012B   Running	Sao	SW3012BL

this is what I have tried with out any luck

SELECT Board.[MACHINE #], IIf(Board.[PART NUMBER]="0","",IIf(Board.[PART NUMBER]="#N/A","",Board.[PART NUMBER])) AS PartNum, IIf(Board.STATUS="0","",IIf(Board.STATUS="#N/A","",Board.STATUS)) AS Stat, IIf(Board.OPERATOR="0","",IIf(Board.OPERATOR="#N/A","",Board.OPERATOR)) AS Oper, IIf(Board.[MACHINE #]="5001 Next" Or "4001 Next" Or "3004 Next" Or "2301 Next" Or "2205 Next" Or "1761 Next" Or "1504 Next" Or "1203 Next" Or "1202 Next" Or "1201 Next" Or "1107 Next" Or "1001 Next" Or "883 Next" Or "882 Next" Or "881 Next" Or "1204 Next" Or "2005 Next",Board.[PART NUMBER],"") AS [Next] 
FROM Board
WHERE (((IIf([Board].[PART NUMBER]="0","",IIf([Board].[PART NUMBER]="#N/A","",[Board].[PART NUMBER])))<>'' And (IIf([Board].[PART NUMBER]="0","",IIf([Board].[PART NUMBER]="#N/A","",[Board].[PART NUMBER])))<>''));

Any help is much appreciated!!!!!

Thanks in advance!!!

 

[Andrzejek]

"displays the next number" - where do you get this next number from? What do you mean by 'next'?
[tt]
MACHINE # PartNum Stat Oper [blue]Next Job[/blue]
3004 3045-G Running Eric [blue]3036-H[/blue]
2301 2837-B Running [blue]2837[/blue]
2205 SW3012B Running Sao [blue]SW3012BL[/blue]
[/tt]

Have fun.

---- Andy

 

[dhookom]

In addition to Andy's questions, do you understand there is no previous or next or first or last unless you have specific values saved in field(s) that identify the order?


Duane
Hook'D on Access
MS Access MVP

 

[evr72]

I got it I did a query with all the machine numbers
5001
5001 Next
4001
4001 Next
etc.

and trimmed the field so I would get
5001
4001
etc.
and saved it as query one
then did another query with just the regular parts without the "Next" text

then from the query where I trimmed the "Next" text created another query with that only shows the "Next" text in the part

then joined both queries by Mahcine number worked ok

Thanks!!

 

[PHV]

Why all this queries ?
What about this single one ?

SELECT A.[MACHINE #], A.[PART NUMBER], A.STATUS, A.OPERATOR, B.[PART NUMBER] AS [Next Job]
FROM Board AS A LEFT JOIN Board AS B ON A.[MACHINE #] & ' Next' = B.[MACHINE #]
WHERE A.[MACHINE #] Not Like '*Next'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886