How to retrieve the latest value from a column of table?. 5

[cuok]

Hi friends,
I'll appreciate any help and thanks in advance!!

My problem is to retrieve the latest value from a column.

I have a table with thos fields:

ID (text)
DateCheck (dd/mm/yy format)
TestA (integer)
TestB (integer)
TestC (integer)
……


The user must enter the DateCheck but must not enter all other fields!
So after having, let say, 8 records the table can be like this:


ID DateCheck TestA TestB TestC
111 01/01/00 56
222 03/02/00 58
222 01/01/02 90
333 01/01/00 66
111 17/02/00 89
333 01/10/03 86
111 01/10/03 45
222 01/01/01 77


The query will run only for ‘TestA’

The query must answer the quistion: Give me the latest ‘TestA’ that has a ‘DateCheck’!
So, in my example the answer is:

111 17/02/00 89
222 01/01/02 90
333 01/01/00 66


Many Thank
CUOK

 

[cbhnp]

I think you'll need to use a subquery. Try:

--this will retrieve the latest VitA value
SELECT ID, DateCheck, VitA
FROM Tbl_Biochemistry A
WHERE DateCheck = (select max(datecheck)
from Tbl_Biochemistry B
where A.id = B.id
and B.VitA is not null)
--this will retrieve the latest VitE value
SELECT ID, DateCheck, VitE
FROM Tbl_Biochemistry A
WHERE DateCheck = (select max(datecheck)
from Tbl_Biochemistry B
where A.id = B.id
and B.VitE is not null)
--then join join these two queries on ID to get both VitA and VitE

 

[cuok]

Good morning (HERE 08:00) cbhnp and all!

thank cbhnp!

but i dont know how to "join these two queries on ID to get both VitA and VitE"
can you tell me how please?
thanks
CUOK

 

[Golom]

Save the two queries. Let's call the first one "BiochemA" and the second "BiochemB". Then write a third query

   Select A.ID, A.VitA, B.VitE
   From BiochemA As A INNER JOIN BiochemB As B
        ON A.ID = B.ID

 

[cuok]

Thank You Golom, cnhnp and dodge20!

here your stars!
Thanks
CUOK