Query to get value and previous value in 1 row (1 Viewer)

[boerbende]

Dear readers
I have a simple table with columns ID, Val and prod
Table1
ID val Prod
1 1 A
2 2 B
3 3 A
4 2 B
5 3 A
6 2 B
7 9 A
8 2 B
9 3 B
10 4 A
11 5 A
12 6 B
13 4 B

I would like to query the data in Access to get as result the following table
- filtered by prod = “A”
- Column Val
- 1 extra column with the previous VAL

ID Prod Val Prev
1 A 1
3 A 3 1
5 A 3 3
7 A 9 3
10 A 4 9
11 A 5 4

I am trying to do this with a subquery, but it looks more simple than it is (at least not for me)
Can somebody give me an example?

Many thanks

Ben

 

[jdraw]

See this from Allen Browne re subqueries.

 

[boerbende]

Hello Jdraw,

Sorry, This was what I tried. Not the UNION
Corrected my post

Ben

 

[jdraw]

How do you determine previous value (plain English)?
I could understand if there was a date involved.

 

[boerbende]

You got me. For the real diehards I did maybe simplify it too much
The table has off course also date and time.
measurements for a product can be on different times / different days
for example
20-11-2016 10:00
25-11-2016 19:20
28-11-2016 16:10
28-11-2016 16:30
30-11-2016 23:30

I was thinking to use dates but first to start with ID. Previous value has ID which is lower, to be selected with for example
The query below is how I was "thinking" (but does not work yet)

SELECT
C.ID, C.Val, C.Prod, N.Val, N.ID
FROM Table1 AS C
LEFT JOIN
(SELECT Val, ID FROM Table1 WHERE (Prod="A" AND ID = (SELECT min(ID) from table1 where ID > C.ID))) AS N
ON C.ID = N.ID
WHERE (C.Prod ="A")

 

[boerbende]

Thanks for the link. It contained the solution for me!! (so simple)

SELECT N.ID, N.Val, (SELECT TOP 1 VAL
FROM Table1 AS P
WHERE P.ID < N.ID AND P.prod= N.prod
ORDER BY P.ID DESC) AS P, N.Prod
FROM table1 AS N
WHERE (((N.Prod)="A"));

 

[jdraw]

Try the following:

SELECT C.ID
, C.Val
, C.prod
, C.mDate,
  (SELECT TOP 1 Dupe.val
   from boer as dupe
   where c.prod=dupe.prod and
   dupe.mdate<c.mdate 
   order by dupe.mdate desc
  ) AS PrevValue
FROM boer AS C
order by mdate asc 
;

My test table:

ID		0	Long		
val		1	Long		
Prod		2	Text	
mDate	3	Date

table values:

ID	val	Prod	  mDate
1	1	A	28-Oct-2016 4:30:00 PM
2	2	B	28-Oct-2016 11:30:00 AM
3	3	A	14-Nov-2016 4:30:00 PM
4	2	B	18-Nov-2016 4:30:00 PM
5	3	A	19-Nov-2016 5:30:00 PM
6	2	B	21-Nov-2016 1:20:00 PM
7	9	A	20-Nov-2016 4:32:00 PM
8	2	B	22-Nov-2016 12:30:00 PM
9	3	B	25-Nov-2016 2:30:00 PM
10	4	A	28-Nov-2016 4:30:00 PM
11	5	A	28-Nov-2016 6:30:00 PM
12	6	B	26-Nov-2016 4:30:00 PM
13	4	B	27-Nov-2016 9:30:00 AM

Query result:

ID	Val	prod	       mDate	            PrevValue
2	2	B	28-Oct-2016 11:30:00 AM	
1	1	A	28-Oct-2016 4:30:00 PM	
3	3	A	14-Nov-2016 4:30:00 PM	1
4	2	B	18-Nov-2016 4:30:00 PM	2
5	3	A	19-Nov-2016 5:30:00 PM	3
7	9	A	20-Nov-2016 4:32:00 PM	3
6	2	B	21-Nov-2016 1:20:00 PM	2
8	2	B	22-Nov-2016 12:30:00 PM	2
9	3	B	25-Nov-2016 2:30:00 PM	2
12	6	B	26-Nov-2016 4:30:00 PM	3
13	4	B	27-Nov-2016 9:30:00 AM	6
10	4	A	28-Nov-2016 4:30:00 PM	9
11	5	A	28-Nov-2016 6:30:00 PM	4

 

[boerbende]

Hello Jdraw, many thanks. For both solutions Because the link contained indeed the answer as well