Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Users that do not log in
- Thread starter v45
- Start date
RobertKaiserIII
Programmer
How about trying this:
> Make the table with the User ID (let's call it Table A) your main table and link to the one with the log in info (Table B).
> Add the staff name from Table A and your log in status field from Table B to your detail section.
> Add a suppression formula for your detail section that says IsNull(Table B.Log_In_Field) = True (or whatever value you use to flag whether or not that user has logged on).
This will suppress all rows where that user has logged on, leaving you only with users who haven't logged on.
> Make the table with the User ID (let's call it Table A) your main table and link to the one with the log in info (Table B).
> Add the staff name from Table A and your log in status field from Table B to your detail section.
> Add a suppression formula for your detail section that says IsNull(Table B.Log_In_Field) = True (or whatever value you use to flag whether or not that user has logged on).
This will suppress all rows where that user has logged on, leaving you only with users who haven't logged on.
- Thread starter
- #3
I've always found what I needed by searching, so this is my first post.... I'm not sure I described the problem well.
If the user has not logged in, there is no information for them in the table that tracks access. I tried using an outer join so the staff table would list all records while the columns for the access table would be blank. This is described in the help files. Couldn't get it to work.
I thought there might be a way to use a formula to find records in one table where the linked field didn't exist in another table.
If the user has not logged in, there is no information for them in the table that tracks access. I tried using an outer join so the staff table would list all records while the columns for the access table would be blank. This is described in the help files. Couldn't get it to work.
I thought there might be a way to use a formula to find records in one table where the linked field didn't exist in another table.
RobertKaiserIII
Programmer
I made a quick test report here using a Left Outer Join and it worked. The suppression formula in your detail section should be IsNull(Log In Table.Primary Key) = FALSE (sorry, I typed "True" in my first response).
If that still doesn't work, there's another technique, but it is considerably more involved. Crystal Decisions has a sample report that you shows how to report on nonexistent data, but it involves a subreport, and might be overkill for your purposes. You can download it from here:
Or if the link doesn't work, just search for "cr_subreports_samples.zip".
The report I am referring to is the one named Exceptions.rpt
If that still doesn't work, there's another technique, but it is considerably more involved. Crystal Decisions has a sample report that you shows how to report on nonexistent data, but it involves a subreport, and might be overkill for your purposes. You can download it from here:
Or if the link doesn't work, just search for "cr_subreports_samples.zip".
The report I am referring to is the one named Exceptions.rpt
synapsevampire
Programmer
I alleviate this sort of nuisance by ALWAYS creating a Periods table in the database. This table contains an entry for every date.
Now you can select the appropriate range from the Periods table, and join to it, that way you ALWAYS get the period (dates) required.
Standard fare in a Data Warehouse.
-k kai@informeddatadecisions.com
Now you can select the appropriate range from the Periods table, and join to it, that way you ALWAYS get the period (dates) required.
Standard fare in a Data Warehouse.
-k kai@informeddatadecisions.com
v45,
Robert Kaiser's left outer join example is exactly what you need to sort users that did log on from users that didn't in a report with no subreports.
However, if all the report is supposed to do is display folk that didn't log on, you could report on customised sql to get you this information using the minus command:
SELECT userid FROM Staff_Table
MINUS
SELECT userid FROM Login_Table
WHERE Login_Time > <LastMonthsDate>...
Naith
Robert Kaiser's left outer join example is exactly what you need to sort users that did log on from users that didn't in a report with no subreports.
However, if all the report is supposed to do is display folk that didn't log on, you could report on customised sql to get you this information using the minus command:
SELECT userid FROM Staff_Table
MINUS
SELECT userid FROM Login_Table
WHERE Login_Time > <LastMonthsDate>...
Naith
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question