On Tue, 27 Sep 2005 20:04:34 +0100, <jim> wrote:
>hi,
>
>i have a table( tblUpdate) with the following fields
>
>UpdateID, UpdateDate, ActionID
>
>UpdateID is the primary key
>ActionID is a foreign key
>
>i want to find the latest (by date) update of a particluar action.
>
>and i can do this using the following:
>
>SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate, tblUpdate.ActionID
>FROM tblUpdate
>GROUP BY tblUpdate.ActionID;
>
>
>this gives me :
>
>"MaxOfUpdateDate","ActionID"
>24/9/2005 ,24
>26/8/2005 ,25
>11/9/2005 ,26
>26/9/2005 ,28
>
>which is good but i also need the primary key, UpdateID
>
>If i add updateID into the query i get
>
>SELECT Max(tblUpdate.UpdateDate) AS MaxOfUpdateDate, tblUpdate.UpdateID,
>tblUpdate.ActionID
>FROM tblUpdate
>GROUP BY tblUpdate.UpdateID, tblUpdate.ActionID;
>
>i now get this
>
>"MaxOfUpdateDate","ActionID","UpdateID"
>24/9/2005 ,24,23
>22/9/2005 ,24,24
>26/8/2005 ,25,25
>26/9/2004 ,25,26
>11/9/2005 ,26,27
>26/9/2005 ,28,28
>
>which is no good because im getting results for all the updateids
>
>i really just want :
>
>"MaxOfUpdateDate","ActionID"
>24/9/2005 ,24
>26/8/2005 ,25
>11/9/2005 ,26
>26/9/2005 ,28
>
>but with the primary key for each one.
>
>any help would be much appreciated
How about this - just knocked together quickly in the QBE and not
thoroughly tested:
SELECT
tblUpdate.UpdateId
,tblUpdate.ActionId
,tblUpdate.UpdateDate
FROM
tblUpdate
INNER JOIN
tblUpdate AS tblUpdate_1
ON
tblUpdate.ActionId = tblUpdate_1.ActionId
GROUP BY
tblUpdate.UpdateId
,tblUpdate.ActionId
,tblUpdate.UpdateDate
,tblUpdate_1.ActionId
HAVING
(((tblUpdate.UpdateDate)=Max([tblUpdate_1].[UpdateDate])));
With this data:
UpdateId ActionId UpdateDate
1 24 24/12/2004
2 16 13/06/2005
3 44 21/06/2004
4 18 22/04/2004
5 11 14/09/2005
6 11 14/05/2005
7 24 12/05/2004
This query gives:
UpdateId ActionId UpdateDate
1 24 24/12/2004
2 16 13/06/2005
3 44 21/06/2004
4 18 22/04/2004
5 11 14/09/2005
Is that what you need?
Lemming
--
Curiosity *may* have killed Schrodinger's cat.
|
