Last Login

S

Simon

hi folks,

Im writing an ASP application where authors submit stories for use in a
book, everything is going pretty good so far however I have ran into a
stumbling block.

the publishers of the book can log into an admin panel and view/edit/delete
stories, I'm trying to add functionality that shows them how mang stories
have been submitted since they last logged into the site. I have the
following two tables:

tblSubmission = holds the story submissions
tblLastLogin = holds username and last login date

Both tables have a date field so how would I count the stories in
tblSubmission that are new since the last login date in tblLastLogin?

Thanks in anticipation
Simon Ruzgar
 
D

Dale Fye

Simon,

1. My first question would be when does the LastLogin table have its value
entered? If it gets updated as soon as the publisher logs in, then there
won't be any submissions that are newer than their login. What I think you
mean is you want to know the number of submissions between their previous
login(or logout) and the current date/time. You might also want to
reconsider just having the tblLastLogin in favor of maintaining a tblLogins
table with records all logins for a particular user. Much more convenient,
allows you to track usage by an individual rather than just their last
usage, and is easy to query to get their previous login. However, assuming
what you want is what you originally asked for, the easiest method would be
something like:

SELECT Count(S.SubmissionID) as NewSubmissions
FROM tblSubmissions S
WHERE S.SubmissionDateTime > (SELECT L.LastLoginDateTime FROM tblLastLogin L
WHERE L.LoginID = 999)

HTH
Dale

Where you would substitute the correct loginId in the subquery. If your
LoginID is text, you would have to wrap it in quotes.
 
S

Simon

Dale,

First up, thanks for your reply! The table "tblLastLogin" gets updated when
teh user logs out of the application on a page called "logout.asp" Im
defineltye trying to show the number of story submissions since the last tim
ethey logged in/out of the application, if you think there is a better way
to do this I'm all ears?

I addeed the following SQL statement:

SELECT Count(Date) AS NewSubmissions
FROM tblSubmission
WHERE Date > (SELECT LastLogin FROM tblLastLogin WHERE UserName = '" &
strUser &"')"

then I populate the following variables:

intLastLogin = rs.Fields("LastLogin")
intCount = rs.Fields("NewSubmissions")

intCount gets successfully populated with the number of stories since last
login but intLastLogin does not get updated, any ideas why? Do I have to
update my SQL statement?

Thanks again (you rock)

Simon
 
D

Douglas J. Steele

If that's the SQL that's opening the recordset, the recordset doesn't
include LastLogin because you haven't included it in the main SELECT.

Unfortunately, I can't think of a simple way to be able to include it in an
Access query.
 
D

Douglas J. Steele

Gave up too soon. Assuming you're using Access 2000 or newer, try:

SELECT Constraint.LastLogin, Count([Date]) AS NewSubmissions
FROM tblSubmission
INNER JOIN
(SELECT LastLogin FROM tblLastLogin WHERE UserName = '" & strUser &"')" AS
Constraint
ON tblSubmission.[Date] > Constraint.LastLogin
GROUP BY Constraint.LastLogin

BTW, Date is a reserved word, and should not be used as a field name.
 
Top