Last Login Date

[jenksonu]

I have a database that is a log file. Everytime a user logs on the log(databasee) is updated with username, time, date, etc. I have a scripte that querys database and displays DISTINCT username and count from table2.fldUserName. What I'm trying to do is get the last login Date of each user also. fldusername is a text field but fldDate is a time/date field. This is the code I use to get Distinct username and count.

SELECT DISTINCT Table2.fldUserName, Count(Table2.fldUserName) AS CountOffldUserName " & _
"FROM Table2 GROUP BY Table2.fldUserName ORDER BY COUNT(Table2.fldUserName) DESC

I was thinking something like this would work to get the last login date (LDate) but with no luck

"SELECT DISTINCT Table2.fldUserName, Date(Table2.fldDate) AS LDate " & _
"FROM Table2 GROUP BY Table2.fldUserName ORDER BY Date(Table2.fldDate) DESC"

:cry2:

[atlas]

Originally posted by jenksonu
I have a database that is a log file. Everytime a user logs on the log(databasee) is updated with username, time, date, etc. I have a scripte that querys database and displays DISTINCT username and count from table2.fldUserName. What I'm trying to do is get the last login Date of each user also. fldusername is a text field but fldDate is a time/date field. This is the code I use to get Distinct username and count.

SELECT DISTINCT Table2.fldUserName, Count(Table2.fldUserName) AS CountOffldUserName " & _
"FROM Table2 GROUP BY Table2.fldUserName ORDER BY COUNT(Table2.fldUserName) DESC

I was thinking something like this would work to get the last login date (LDate) but with no luck

"SELECT DISTINCT Table2.fldUserName, Date(Table2.fldDate) AS LDate " & _
"FROM Table2 GROUP BY Table2.fldUserName ORDER BY Date(Table2.fldDate) DESC"

:cry2:



"SELECT Table2.fldUserName, Date(Table2.fldDate) AS LDate " & _
"FROM Table2 GROUP BY Table2.fldUserName ORDER BY Date(Table2.fldDate) DESC limit 1"


try that...

[jenksonu]

Thanks someone helped me with this and it works great...

"SELECT table2.fldusername, Count(table2.fldusername) AS CountOffldUserName, Max(table2.fldDate) AS lDate FROM table2 GROUP BY table2.fldusername ORDER BY COUNT(Table2.fldUserName) DESC"