How do I show only new users since last month?

[JessicaD]

I have an Access 2003 db that contains user hit info. I want to create a report that shows only the new users since last month. I created a report that lists the current month's user info. This is the SQL query in Crystal 10:


CODE
SELECT `allkbusers_OLD`.`USERID`, `allkbusers_OLD`.`HIT_TIME`,
`RPN_data`.`CustomerName`, `RPN_data`.`LastName`, `RPN_data`.`FirstName`,
`RPN_data`.`Email`
FROM `RPN_data` `RPN_data` INNER JOIN `allkbusers_OLD` `allkbusers_OLD`
ON `RPN_data`.`ContactID`=`allkbusers_OLD`.`USERID`
WHERE (`allkbusers_OLD`.`HIT_TIME`>=#2008-10-01 00:00:00# AND
`allkbusers_OLD`.`HIT_TIME`<#2008-11-01 00:00:00#)
ORDER BY `allkbusers_OLD`.`USERID`, `RPN_data`.`LastName`

This is the selection criteria (users are prompted with the DateRange parameter):

{allkbusers_OLD.HIT_TIME} = {?DateRange}

I tried to add a second date range after the first one in the SQL query with "AND NOT (`allkbusers_OLD`.`HIT_TIME`>=#2008-9-01 00:00:00# AND `allkbusers_OLD`.`HIT_TIME`<#2008-10-01 00:00:00#)" in it, but the results were the same. Then I took that out and tried adding "and if {allkbusers_OLD.HIT_TIME} <> LastFullMonth then true" to the selection criteria, but that also returned the same results.

Does anyone have any suggestions about how to compare this month's data with last month's data and then only show the difference between the two (which would be the new users)?

 

[CaptainD]

Using SQL you could use a "NOT IN" sub query (You can do this with "MS SQL" anyway)

If your server allows it, it goes something like this.

SELECT r.CustomerName FROM RPN_data r 
WHERE r.Hit_TIME >='2008-11-01'
AND 
USERID NOT IN(SELECT USERID FROM RPN_data 
WHERE Hit_TIME<'2008-11-01')

 

[Madawc]

If CaptainD's solution doesn't work, you could include the table twice, the second time as an alias linked with a 'left outer'. Also have no selection criteria for the alias (left outer will fail if you do). The first occurance should be just for users this month.

This method will give you lots of detail lines. Group by USERID and suppress the group header and detail lines. In the group footer, check the minimum date from the alias. The users you want have a minimum in the current month. Suppress the group footer if it isn't. (Right-click on the section and Section Expert.)

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Or you could allow in both months data in the record selection formula. Assuming the user picks a date in the desired month by using a parameter, you could use a selection formula like this:

{table.date} in dateserial(year({?parm}),month({?parm})-1, 1) to
dateserial(year({?parm}),month({?parm})+1, 1)-1

...and then insert a group on {table.user} and use a GROUP selection formula like this:

minimum({table.date},{table.user}) >= dateserial(year({?parm}),month({?parm}), 1)

-LB

 

[JessicaD]

Thanks everyone for the suggestions. I ended up pulling just the user IDs into an Excel spreadsheet and using a formula to compare the IDs month over month and then cumulatively.

Just so you know, CaptainD, the subquery returned no results, and Madawc, I don't think I'm advanced enough yet to do what you suggested, but I intend to keep trying. Thanks lbass for the suggestion - it was the closest to getting the data my boss asked for until she said she wanted it to be cumulative.

Thanks again for your help!