Help needed with this simple query

[pretoria]

I'm missing a bit of experience dealing with this query. It's very simple. I'm selecting log activities from COMLOG. However I only want the most recent DateStamp entry of each Cust_FK entry listed.

Because there are more logactivities, there are more/identical Cust_FK values. Would I first have to group these?? I just don't know how I would go about it.

SELECT COMLOG.LogID, COMLOG.DateStamp, COMLOG.Cust_FK, COMLOG.LogActivity
FROM COMLOG;

thanks

 

[pretoria]

A quick example of my table would be this. But I only want it to return the most recent entry for each Cust_FK

LogID DateStamp Cust_FK LogActivity
1 30/06/2005 1 text. bla bla bla
2 28/06/2005 1 text. bla bla bla
1 30/06/2005 1 text. bla bla bla
2 28/06/2005 1 text. bla bla bla
3 28/06/2005 1 text. bla bla bla
4 28/06/2005 2 text. bla bla bla
5 28/06/2005 2
6 21/06/2005 2
7 19/04/2005 2
8 21/06/2005 2
9 17/06/2005 2
10 16/06/2005 2
11 07/06/2005 2
12 16/06/2005 4

 

[lcross]

heve you tried to use the max function? in design view choose max, and it returns the last entry.

 

[pretoria]

yes, but that returns only one record. I need the most recent entry of each group of Cust_FK's.

any ideas?

 

[Jon K]

LogID DateStamp Cust_FK LogActivity
1 30/06/2005 1 text. bla bla bla
2 28/06/2005 1 text. bla bla bla
1 30/06/2005 1 text. bla bla bla
2 28/06/2005 1 text. bla bla bla

Your entries are not unique. For some Cust_FK, it's not possible to determine which entry is the most recent one.

If you can use a DateTimeStamp or add an autonumber field to the table to uniquely identify the entries within each Cust_FK, you can use a series of two queries to achieve what you want. With your given sample data, you just can't.
.

 

[pretoria]

You're right. I forgot to mention I have an additional TimeStamp field next to the DateStamp field which would make it unique. How would I sql these two queries you mention?

 

[pretoria]

Jon K,

Ok. I've also added your LogID field. the table now looks like this.

LogID CustLogID Cust_FK DateStamp TimeStamp Log
1 1 1 10-01-05 10:23 bla bla
2 2 1 10-01-05 12:23 bla bla
3 1 2 10-04-02 10-30 bla bla
4 2 2 10-05-05 10-03 bla bla

Now I want a query that returns the bold highted fields. Which is the first CustLogID of each Cust_FK.

How would this sql statement look like? in other words how would ik get this result

1 1 1 10-01-05 10:23 bla bla
3 1 2 10-04-02 10-30 bla bla

 

[Jon K]

See the two queries in the database.

You can run the second query.
.

 

[pretoria]

Jon K is the man.
Thanks for extended effort!