Hello, I'm not quite sure if what I want to do is possible with one query, but I'll jump right into what I'm trying to do anyway..
This is our Setup
End Users Table
ID, Name, Email, Date_Created, Last_Date_Accessed
Uses Table
ID, End_User_ID, Date_Accessed
Every time a user logs into our system, a record is added to the Uses table.
What I want to do, is generate a report, that contains the number of uses each user had in a given month, that would display like this
Name, Email, User Since, Last Login, Number of Logins in X Month (x being a variable of any month I wish to generate)
an example record for July would be
Kyle, myEmail@gmail.com, 1/17/2008, 7/5/2008, 13
(meaning Kyle logged in 13 times this month..or that Kyle had 13 records in the Uses table with the Date_Accessed being between 7/1/2008, and 7/31/2008)
This is what I know how to do
SELECT End_Users.Name, End_Users.Email, End_Users.Date_Created, End_Users.Last_Date_Accessed, Uses.Date_Accessed FROM End_Users INNER JOIN Uses ON End_Users.ID = Uses.End_User_ID
The problem with this, is if for example User '1' Logged on 10 times that month, it would return 10 rows for that user. Is there a way to count up the number of rows, and put that in a different field or something?
Any hints on this would be appreciated, if this is simply not possible with SQL alone, please let me know, and i'll use some other alternative.
Thank you,
ArchG
edit* Unsure of why the formatting seems off in firefox at least...I'm not sure how to fix that.