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

 

[rac2]

Maybe

SELECT ID, DateCheck, TestA
FROM myTable
JOIN 
     (
      SELECT ID, MAX(DateCheck) AS "DateCheck"
      FROM myTable
      WHERE TestA <> ''
      GROUP BY ID
     ) hasAScore ON
           myTable.DateCheck = hasAScore.DateCheck
           AND myTable.ID = hasAScore.ID

 

[cuok]

Good morning rac2 and all!

thanks for replaying!

your idea gives me "syntax error in FROM CLAUSE" AND HIGHLIGHTS THE WORD JOIN .

HRER THE REAL FIELDS WITH YOUR SYNTAX:

SELECT ID, DateCheck, VitE
FROM Tbl_Biochemistry join (SELECT ID, MAX(DateCheck) AS &quot;DateCheck&quot; FROM Tbl_Biochemistry WHERE VitE <> ''&quot; GROUP BY ID ) hasAScore ON VitE.DateCheck = hasAScore.DateCheck AND VitE.ID = hasAScore.ID


I'M realy dont know what to do!
thanks
CUOK

 

[cuok]

Good morning rac2 and all!

at i solved it. here how:
first i built this query that gave me all rows that have VitE values and named it dbRAC2:


SELECT Tbl_Biochemistry.Id, Last(Tbl_Biochemistry.DateCheck) AS DateCheck, Last(Tbl_Biochemistry.VitE) AS VitE
FROM Tbl_Biochemistry
WHERE (((IsNull([Tbl_Biochemistry].[VitE]))=False))
GROUP BY Tbl_Biochemistry.Id, Tbl_Biochemistry.DateCheck;


then i built a seconed query:


SELECT DISTINCTROW dbRAC2.Id, Max(dbRAC2.VitE) AS VitE
FROM dbRAC2
GROUP BY dbRAC2.Id;


and its works...

thank you very much RAC2
CUOK

 

[cuok]

Hi!

now i got into other problem as continue of the above:

its not working when i added the next field 'VitA' to the queries!!

if the rows are:

ID DateCheck Vit E Vit A
08235257-6 13/09/00 10 522
08235257-6 08/09/01
08235257-6 12/09/03 524
9999999999-8 13/09/00
9999999999-8 07/10/00 78 56
9999999999-8 01/04/02 142 142
9999999999-8 20/04/02
9999999999-8 05/08/02 58 58
9999999999-8 07/09/02 79
9999999999-8 04/04/03 145
9999999999-8 06/06/03 56 1150
9999999999-8 21/08/03
9999999999-8 06/09/03

the answer MUST BE:

ID Vit_E Vit_A
08235257-6 10 524
999999999-8 56 1150

yhanks for any ides.
CUOK

 

[dodge20]

Did you make sure to add VitA in your group by clause in your queries. When you use aggregate functions such as max, you must put all non-aggregate fields in a group by clause.


SELECT DISTINCTROW dbRAC2.Id, dbRAC2.VitA, Max(dbRAC2.VitE) AS VitE
FROM dbRAC2
GROUP BY dbRAC2.Id, dbRAC2.VitA;



Do you get a problem with the first query?

Dodge20

 

[cuok]

Hi dodge20!
thanks a lot!

im trying it now!
in secondes i will let you know1
CUOK

 

[cuok]

Hi dodge20!

here the results: (which not comlete the task as shown 4 posts above!)

ID VitA VitE
08235257-6
08235257-6 522 10
08235257-6 524
9999999999-8 145
9999999999-8 56 78
9999999999-8 58 58
9999999999-8 79
9999999999-8 142 142
9999999999-8 1150 56

any more ideas?
thanks
CUOK

 

[dodge20]

Oh you want the max of VitA as well? See what this returns

SELECT DISTINCTROW dbRAC2.Id, Max(dbRAC2.VitA) as VitA, Max(dbRAC2.VitE) AS VitE
FROM dbRAC2
GROUP BY dbRAC2.Id

Dodge20

 

[cuok]

Hi! dodge!

almost!!

what realy i need is the criteria to retrieve the latest value of each columns in a table (VitA, VitB... not the max!)

your query gives me:

ID VitA VitE
08235257-6 565 10
9999999999-8 1150 145

not what i need that mean:
ID Vit_E Vit_A
======== ======== =========
08235257-6 10 524
999999999-8 56 1150

thanks a lot for your efforts to help me!
CUOK

 

[cuok]

Hi dodge20!

changing max to last not makes the work.
now im trying to add the query the dareCheck

CUOK

 

[dodge20]

Oh now I see, I think you want to use the last function like you did in your first query. I believe last is considered an aggregate function so you should be able to run this query.

SELECT DISTINCTROW dbRAC2.Id, Last(dbRAC2.VitA) as VitA, Last(dbRAC2.VitE) AS VitE
FROM dbRAC2
GROUP BY dbRAC2.Id

Dodge20

 

[cuok]

Hi dodge20!

in this time i replayed before i saw your idea!

i mean:
changing max to last not makes the work.
now im trying to add the query the dareCheck

because its possible to have in one row a value for VitA but there is no value for VitE for the same DateCheck as you can see from this:

ID DateCheck Vit E Vit A
08235257-6 13/09/00 10 522
08235257-6 08/09/01
08235257-6 12/09/03 524
9999999999-8 13/09/00
9999999999-8 07/10/00 78 56
9999999999-8 01/04/02 142 142
9999999999-8 20/04/02
9999999999-8 05/08/02 58 58
9999999999-8 07/09/02 79
9999999999-8 04/04/03 145
9999999999-8 06/06/03 56 1150
9999999999-8 21/08/03
9999999999-8 06/09/03

thanks
CUOK

 

[dodge20]

Man I am out of it this morning. I thought you meant the last entry. I am not sure if VitA and VitE are in you tbl_biochemistry. So something like this might be what you want. it is real close to what RAC2 had.

SELECT ID, DateCheck, VitA, VitE
FROM Tbl_Biochemistry A
(
SELECT ID, MAX(DateCheck) AS &quot;DateCheck&quot;
FROM Tbl_Biochemistry B
GROUP BY ID
)
Where A.ID = B.ID



Dodge20

 

[cuok]

sorry dodge20 but its gives me the error: &quot;syntax error in from clause&quot;

can you pls go on ?
thanks
CUOK

 

[cuok]

dear dodge20!

I must loguot now for some hours ,i im realy appriciating and thanks your efoorts and your time,

hope to come and see your success!!!!

THANKS A LOT
CUOK

 

[Golom]

Sorry to butt in guys. dodge20 has the solution but, because of an Access limitation, you need to do it as a two step process.

Step 1: Save the derived table as a query (let's call it MXDATE)

SELECT ID, MAX(DateCheck) AS [DateCheck]
      FROM Tbl_Biochemistry B
         GROUP BY ID

Step 2: Use that query in the second query

SELECT A.ID, A.DateCheck, VitA, VitE
FROM Tbl_Biochemistry A INNER JOIN MXDate B
     ON A.ID = B.ID AND A.DateCheck = B.DateCheck

Access cannot use derived tables coded in-line (which is what this is.) More powerful systems like SQL-Server or Oracle can use this syntax.

 

[dodge20]

Thanks Golom, I am still somewhat new to Acces and this has been driving me nuts. You can have a star for verifying that I am not crazy.

Dodge20

 

[Golom]

dodge20
You're welcome ... but since you did all the real work ... here's your star.

Crazy? Of course you're crazy ... we all are. Its a side-effect of trying to understand a language that we make up as we go by following rules that we don't understand and that change daily.

 

[cuok]

Hi GOLOM, dodge20 RAC2 and all!
In my last post I wrote &quot;I must logout now for some hours&quot; - because of personal reason &quot;some hours” became 24 hours - I apologize about it.


Maybe I didn’t brought my question clear enough.

GOLOM answer, by 'MXDATE' query - finds the Max(DateCheck) in Tbl_Biochemistry
Then uses the seconed query to retrieve VitA,VitE for the DateCheck found by MXDATE query.

Its gives the latest DateCheck then retrieves VitA,VitE for latest DateCheck

But what if there is no value of VitA,VitE for this latest DateCheck ?

My task is to find:
The value for VitA by Last date that have value for VitA and so for VitE.

Posts above you have this table:

ID DateCheck Vit E Vit A
08235257-6 13/09/00 10 522
08235257-6 08/09/01
08235257-6 12/09/03 524
9999999999-8 13/09/00
9999999999-8 07/10/00 78 56
9999999999-8 01/04/02 142 142
9999999999-8 20/04/02
9999999999-8 05/08/02 58 58
9999999999-8 07/09/02 79
9999999999-8 04/04/03 145
9999999999-8 06/06/03 56 1150
9999999999-8 21/08/03
9999999999-8 06/09/03

The answer MUST BE:

ID Vit_E Vit_A
08235257-6 10 524
999999999-8 56 1150


I hope im more clearly now and not missed the train !!

Thanks
CUOK