Query to show last time someone logged in.

[Jonathan Brown]

When my users log into my database a query automatically runs and creates a
new record in a table called tblUsageStats. Each record contains the user's
Username and the Current date & time (Now()).

I want to create a query now that will pull from that table only the last
time each user logged in. So it would probably looks something like:

FROM "tblUsageStats"
Select "Username" & "Date"
Where "Date" = "The last time they logged in" or "The closest date to NOW()"

This is not to say, "Who was the last person to login?" but "when was the
last time each user logged in?"

Thanks a ton.

 

[John Vinson]

When my users log into my database a query automatically runs and creates a
new record in a table called tblUsageStats. Each record contains the user's
Username and the Current date & time (Now()).

I want to create a query now that will pull from that table only the last
time each user logged in. So it would probably looks something like:

FROM "tblUsageStats"
Select "Username" & "Date"
Where "Date" = "The last time they logged in" or "The closest date to NOW()"

This is not to say, "Who was the last person to login?" but "when was the
last time each user logged in?"

Thanks a ton.

A Subquery will do this:

SELECT [Username], [Date]
FROM [tblUsageStats]
WHERE [Date] = (SELECT Max([Date]) FROM tblUsageStats AS X
WHERE X.[Username] = [tblUsageStats].[Username]);

Note that fieldnames must be delimited with [brackets] not "quotes",
or you'll retrieve the literal string values "Username" and "Date".
Also, the field named Date should be renamed - that's a reserved word
and very well may cause problems.

John W. Vinson[MVP]

 

[Jonathan Brown]

John,

You're a genius.

When my users log into my database a query automatically runs and creates a
new record in a table called tblUsageStats. Each record contains the user's
Username and the Current date & time (Now()).

I want to create a query now that will pull from that table only the last
time each user logged in. So it would probably looks something like:

FROM "tblUsageStats"
Select "Username" & "Date"
Where "Date" = "The last time they logged in" or "The closest date to NOW()"

This is not to say, "Who was the last person to login?" but "when was the
last time each user logged in?"

Thanks a ton.

A Subquery will do this:

SELECT [Username], [Date]
FROM [tblUsageStats]
WHERE [Date] = (SELECT Max([Date]) FROM tblUsageStats AS X
WHERE X.[Username] = [tblUsageStats].[Username]);

Note that fieldnames must be delimited with [brackets] not "quotes",
or you'll retrieve the literal string values "Username" and "Date".
Also, the field named Date should be renamed - that's a reserved word
and very well may cause problems.

John W. Vinson[MVP]

 

[LisaVH]

Jonathan,
I would be really interested in how you set up your table tblUserStats. I
would very much like to track the times that my users log in, but can not
seem to find out how.

Thanks for any help you can provide.
Lisa.

John,

You're a genius.

When my users log into my database a query automatically runs and creates a
new record in a table called tblUsageStats. Each record contains the user's
Username and the Current date & time (Now()).

I want to create a query now that will pull from that table only the last
time each user logged in. So it would probably looks something like:

FROM "tblUsageStats"
Select "Username" & "Date"
Where "Date" = "The last time they logged in" or "The closest date to NOW()"

This is not to say, "Who was the last person to login?" but "when was the
last time each user logged in?"

Thanks a ton.

A Subquery will do this:

SELECT [Username], [Date]
FROM [tblUsageStats]
WHERE [Date] = (SELECT Max([Date]) FROM tblUsageStats AS X
WHERE X.[Username] = [tblUsageStats].[Username]);

Note that fieldnames must be delimited with [brackets] not "quotes",
or you'll retrieve the literal string values "Username" and "Date".
Also, the field named Date should be renamed - that's a reserved word
and very well may cause problems.

John W. Vinson[MVP]

 

[Rich via AccessMonster.com]

Hey Lisa

Have you already got a login screen (made yourself, with a login table?) if
not then best set one up like this

1. Make new table with name like "UserLog" etc. Have fields called what ever
you need ie FirstName, Surname, ID (Dont use "NAME"). Plus enter another 2
fields i. DateLogged ii. TimeLogged (Note don't label them "Date" or "Time"
as these are "special words" used by access. Put their default values as Date
() and Time() and set the formats to however you wish to view the info ie
"Short Date"

2. Create yourself a form which has recordsource of your table and put a text
box for each of your fields ie one for [name] one for [date] one for [time]
whatever you named them. Save this as "loginformdata" or similar!

3. Create yourself a small login form with one text box so the user can enter
their [Name / ID / etc...] Have a button which is only enabled if the text
box is not null use this code in the texts' box "OnChange" event

if isnull(me.textbox101) then
me.MycommandButton.enabled = false
else
me.MycommandButton.enabled = true
end if

4. On the OnClick event of your button put the following code...

Dim Myname as string
Myname = me.textbox101
docmd.openform "Loginformdata",,,,,achidden
docmd.gotorecord acform, "Loginformdata",acnewrec
forms!loginformdata![Their Name etc] = Myname
docmd.close acform, "Loginformdata"
' and a little note to say hi!
msgbox "Welcome " & myname


this will then save the login name in the table and the date and time should
default to the time the button was pressed. If you wanted a better way with
passwords I'm affraid that it will be a long conversation - I don't mind but
didn't want to go into too much detail if this is enough for you!

Hope this does help let me know either way!

Rich x

Jonathan,
I would be really interested in how you set up your table tblUserStats. I
would very much like to track the times that my users log in, but can not
seem to find out how.

Thanks for any help you can provide.
Lisa.

John,

[quoted text clipped - 29 lines]

 

[LisaVH]

Thank you for your help. Is there a way to use the existing log in screen?
I have each user set up with a username and password that is entered on
opening database.

Lisa.

Hey Lisa

Have you already got a login screen (made yourself, with a login table?) if
not then best set one up like this

1. Make new table with name like "UserLog" etc. Have fields called what ever
you need ie FirstName, Surname, ID (Dont use "NAME"). Plus enter another 2
fields i. DateLogged ii. TimeLogged (Note don't label them "Date" or "Time"
as these are "special words" used by access. Put their default values as Date
() and Time() and set the formats to however you wish to view the info ie
"Short Date"

2. Create yourself a form which has recordsource of your table and put a text
box for each of your fields ie one for [name] one for [date] one for [time]
whatever you named them. Save this as "loginformdata" or similar!

3. Create yourself a small login form with one text box so the user can enter
their [Name / ID / etc...] Have a button which is only enabled if the text
box is not null use this code in the texts' box "OnChange" event

if isnull(me.textbox101) then
me.MycommandButton.enabled = false
else
me.MycommandButton.enabled = true
end if

4. On the OnClick event of your button put the following code...

Dim Myname as string
Myname = me.textbox101
docmd.openform "Loginformdata",,,,,achidden
docmd.gotorecord acform, "Loginformdata",acnewrec
forms!loginformdata![Their Name etc] = Myname
docmd.close acform, "Loginformdata"
' and a little note to say hi!
msgbox "Welcome " & myname


this will then save the login name in the table and the date and time should
default to the time the button was pressed. If you wanted a better way with
passwords I'm affraid that it will be a long conversation - I don't mind but
didn't want to go into too much detail if this is enough for you!

Hope this does help let me know either way!

Rich x

Jonathan,
I would be really interested in how you set up your table tblUserStats. I
would very much like to track the times that my users log in, but can not
seem to find out how.

Thanks for any help you can provide.
Lisa.

John,

[quoted text clipped - 29 lines]

John W. Vinson[MVP]

 

[Rich via AccessMonster.com]

Yes this can be done. To be really honest I haven't dealt with it in much
detail so might be best for an "MVP" to answer your question, I presume you
mean you are using MSAccess security system (Users and usergroups)?

I will Post you some more informtion tonight but I am currently at work at
the moment so can't really type a lot without being noticed!

Speak soon

Rich x

Thank you for your help. Is there a way to use the existing log in screen?
I have each user set up with a username and password that is entered on
opening database.

Lisa.

Hey Lisa

[quoted text clipped - 55 lines]