/    Sign up×
Community /Pin to ProfileBookmark

Need Select Statement

I’m trying to pull data out of an Access DB and I’m stumped on how to write the Select Statement. I’ve got two tables, tblLogins and tblPages. tblLogins has these fields; SessionID, User, LoginDate, LoginTime. tblPages has these fields; SessionID, PageID, PageDate, PageTime. The two tables are connected by the SessionID fields using a one-to-many relationship (one record in tblLogins and multiple in tblPages). I want to pull the last 5 distinct pages that a User has visited recently. The only other criteria I have is that I’m excluding certain pages from the list. I’ve got a working Select Statement, but it pulls the Page names in alpha order instead of Date order and the Order By clause doesn’t work with Distinct. Please let me know if you have a solution to this problem, thanks.

Current Select Statement:

strSelect = “SELECT DISTINCT TOP 5 P.PageID FROM tblLogins L, tblPages P
WHERE L.User = ‘UserName’ AND L.SessionID = P.SessionID AND
P.PageID NOT IN(‘Estimate Request DU’, ‘Log Out’, ‘Login DU’, ‘Main Menu’)”

to post a comment

2 Comments(s)

Copy linkTweet thisAlerts:
@russellJan 26.2007 — try this (i havent tested it but if memory of msaccess crazy engine serves)
FROM tblLogins L
On l.sessionId = p.SessionId
WHERE L.User = 'UserName'
AND P.PageID NOT IN('Estimate Request DU', 'Log Out', 'Login DU', 'Main Menu')
ORDER BY P.PageDate Desc, P.PageId DESC
Copy linkTweet thisAlerts:
@nbcrockettauthorJan 26.2007 — Close, but not quite. It pulls from the database, but not like I'm wanting. By having PageDate in the list of fields it pulls you actually end up with the same PageID multiple times instead of just once. I'd also like to sort by PageTime, so the Order By would be P.PageDate DESC, P.PageTime DESC. It won't allow you to do this unless you add PageTime to the list of fields it pulls. Doing that messes up the results even more for the same reason I mentioned first. Can you think of any adjustments that can be made to accomplish this? Thanks!


Help @nbcrockett spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 11.30,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ

legal: ({
terms: of use,
privacy: policy
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
recent_tips: (
tipper: @bahaedd,
tipped: article
amount: 1000 SATS,

tipper: @Balmasexy,
tipped: article
amount: 1000 SATS,

tipper: @mbsaad,
tipped: article
amount: 1000 SATS,