IACA Resource Center

Welcome to the IACA resource center. All resources such as jobs, articles, books, tips & tricks and software are here. Choose an item from below or a category from the right. IACA members can not only view but comment on resources (members must be logged in to comment).


First | Previous | [ 1 ] | 2 | 3 | Next | Last ( 59 Resources)

 

Comparing Lists of Values in Microsoft Excel (Tip)

This blog post lists all Excel formulas used to compare lists and provides examples of each one. This is useful for many reasons, such as comparing lists of case numbers, phone numbers, suspects, etc.

Redact your Microsoft Word Bulletins with this Free Add-In (Tip)

The Word 2007 Redaction Tool can be used to hide text within Microsoft Office Word documents. You can mark text to redact and then create a new, redacted version of the document in which the marked text is replaced with a black bar that cannot be converted back to the original text.


Visit: http://redaction.codeplex.com for the Word 2007 or 2010 version or http://tinyurl.com/42qpool for the word 2003 version. Note: you may have to run the installation process twice to sucessfully install it.

Sorting Access Crosstab Columns (Tip)

Have you ever created a Access crosstab (crime by month, for example), and the columns ended up sorted alphabetically as April, August, December...instead of January, February, March?

This quick tip shows you an easy way to fit it:

http://datapigtechnologies.com/blog/index.php/changing-the-sort-order-of-crosstab-column-headings/

This works in Access 2003 and up.

Fast and Easy Search for your Outlook Email (Tip)

In the search bar in Microsoft Outlook 2007, you can enter keywords and Outlook will search for those words in the current folder. But you can also add other filters without having to do an advanced search (which takes longer).

For example, you can go to your sent items folder and enter this into the search box:

to:joe smith contents: robbery bulletin

To retrieve emails that you sent to Joe Smith pertaining to a robbery bulletin.

Or you can search your inbox, read mail, or other folders for:

from: susan davis subject: survey

To get emails from susan about that survey you were supposed to complete for her.

This Microsoft website lists all of the available options: http://tinyurl.com/22mpdlx for doing quick searches in Outlook 2007.


Split time from dates and times in multiple fields (Tip)

Use this function to split time from date/time fields

Function ACalcSTime(d1 As Range, t1 As Range, d2 As Range, t2 As Range) As String
Dim dt_start As Date
Dim dt_end As Date

'Make sure the start time has a colon separating hours and minutes
foo = t1.Value
If Len(foo) = 4 Then foo = Left(foo, 2) & ":" & Right(foo, 2)
dt_start = d1.Value & " " & foo

'Make sure the end time has a colon separating hours and minutes
foo = t2.Value
If Len(foo) = 4 Then foo = Left(foo, 2) & ":" & Right(foo, 2)
dt_end = d2.Value & " " & foo

dt_split = ((dt_end - dt_start) / 2) + dt_start

ACalcSTime = dt_split
End Function

Sort by font color in Excel 2007 (Tip)

This quick tip: http://tinyurl.com/4rlrlq2
shows you how to sort Excel data by font color.

View Analysts Notebook Charts for Free (Tip)

Download the free ChartReader application from here:

http://www.i2group.com/us/products--services/analysis-product-line/chartreader

to view charts creating using i2's Analysts's Notebook.

List of State and Local Government Agencies (Tip)

A website list of agencies by state that you may find helpful: http://www.statelocalgov.net

Google Alerts - Receive Email Updates of Relevant Google Results (Tip)

Google Alerts are email updates of the latest relevant Google results (web, news, etc.) based on your choice of query or topic.

Google Alerts uses the Google search function to locate recent content based on your chosen search terms and then sends that content to your e-mail.
The search terms can include advanced search functions, such as “filetype:” and “site:”.

For example to search on just government domains you would enter your search term followed by “site:.gov”, and if you only wanted to search for MS Excel spreadsheets you would enter your search term followed by “filetype:xls”.

Each alert you create allows you to choose whether you want to search everything (i.e. all of Google), or limit your search to news, blogs, video, or discussions.

You can receive an e-mail with your results as the happen, or once daily, or once weekly.

Google Alerts is an excellent tool for keeping track of a specific topic as it is identified by Google.

Persistent Number Sequence in Excel (Tip)

If you want a column of numbers that keep a certain sequence when you delete rows, use this tip as a guide. If you have a list like this:

A B
1 foo
2 bar
3 baz

with the numbers in column A, you would want a value of 1 in cell A1. Then, all other cells in column A would have this formula:

=INDIRECT("A" & ROW()-1)+1

Transitioning to Office 2007 - Where'd That Button Go? (Tip)

So IT just loaded Office 2007 on your computer, and you have no idea what this ribbon thingy is? Having trouble finding a particular button in an Office 2007 program? No problem!

This site contains an Excel workbook for each Office application that lists the old location and the new location for every feature:

http://office.microsoft.com/en-us/templates/results.aspx?qu=ribbon
+mapping&av=TPL000

Each tab represents a menu in Office 2003. Go to a tab, look for the feature on the left, and it will tell you where to find that feature in Office 2007. Very handy reference for learning Office 2007!

Rounded Time and Range (Tip)

If you have a time in Excel and want a time range that respects 24-hour format time, here is a custom function. Tested in Excel 2000 and 2003.

if you need help adding the code to Excel, please see the related article here: http://www.iaca.net/Articles.asp?AID=95


Rem TimeRange - return a time +/- the number of hours from a specified time
Rem Parameters:
Rem intime - the base time
Rem hrange - the value to add and subtract to get the time range
Function TimeRange(intime As Date, hrange As Integer) As String
Dim ret As String
Dim h As Integer 'Hour of intime, beginning of the time range
Dim range_end As Integer 'High end of the range
' Calculate range start
h = Hour(intime) 'Get the hour from the base time to use as the range's starting time (we don't really need the minutes)
str_h = Trim(Str(h)) 'Convert hour to a string
If Len(str_h) = 1 Then str_h = "0" & str_h 'Make sure starting hour is 2 digits (so mornings < 10 AM have a leading 0)
'Calculate range end
range_end = h + hrange
If range_end > 24 Then range_end = range_end - 24 'Wrap the hour around to the next day if it exceeds midnight
st_range_end = Trim(Str(range_end))
If Len(st_range_end) = 1 Then st_range_end = "0" & st_range_end 'Make sure ending hour is 2 digits (so mornings < 10 AM have a leading zero)
ret = str_h & ":00 to " & st_range_end & ":00" 'Construct the string with the time range
TimeRange = ret
End Function

Schedule Access Macros to Run While You Sleep (Tip)

The tip below will allow you to automatically run a macro in Microsoft Access. While you can automatically run a macro when Access opens just by naming it autoexec (see http://www.iaca.net/AnalystToolbox.asp?TID=106) , the approach described here allows you to run a macro by any name. Best of all, you can schedule it to run whenever you want!


1) Create a macro in Access and add whatever actions you want to run.
2) Make sure that the very first action is SetWarnings = Off. In Access 2007, you must make sure the database is in a trusted location, then click the "Show All Actions" button from the design tab before the Set Warnings action will be available to use in a macro.
3) Make sure the last two actions are SetWarnings = On, followed by Exit.
4) Also remove any MsgBox actions if you have them.
5) Open Notepad and enter the following code:

c:
cd\Program Files\Microsoft Office\Office11
Msaccess.exe C:\MyFolder\MyDatabase.mdb /x MyMacro

Where C:\MyFolder is the location of the database, MyDatabase is the name of the database, and MyMacro is the name of the macro within the database.

If the database is on a server share, you would use something like this:

Z:\MyServerFolder\ MyDatabase.mdb /x MyMacro (if the Z drive is mapped)

or

\\CityServer1\Police\ MyServerFolder\ MyDatabase.mdb /x MyMacro (if there is not a mapped drive)

6) In Notepad, go to File | Save As. Change the “Save as type” to ‘All Files’ and name the file with a .’bat’ extension. For example, “MyImporter.bat” or “Scheduler.bat”. You do not have to save the file in the same folder, but it need to be on the same drive.
7) Almost done! At this point, you can double click the batch file (*.bat) to open Access, run the macro, and exit. There is one final step to automate it – you need to schedule a task which calls the batch file, which in turn calls the Access Macro.
8) Open Windows Task Scheduler (Start | Programs | Accessories | System Tools | Scheduled Tasks).
9) Create a new task. When prompted to choose the program to run, click the browse button and browse to the bat file you just created. Specify a run time & frequency.
10) That’s it! On the day(s) and time(s) specified in the task, the batch file will open Access and fire off the macro, and then everything will close when it is complete.


Link Your Addresses (Tip)

Whenever you are publishing a bulletin, sending an email, making a brochure, why not include a map? Consider these examples:

An invitation to a meeting: the address of the meeting location is formatted as a hyperlink. Clicking the address opens a map showing that location.

An intelligence bulletin: the offender's address is a hyperlink. Clicking the address opens a map showing an aerial photo of the location.

This is easy to do using Google Maps. Enter the address, get the map set up the way you like it, then look for the website address at the top of the page. Copy that and use it as the hyperlink in your document.

Automatically Delay Sending Bulletins with Outlook Rules (Tip)

Ever send out an email (or specifically, a bulletin) to the entire department, only to discover that you forgot a key piece of information? using the Rules feature of Outlook, you can mitigate this issue.

Go to Tools - Rules. Set up a new, blank rule with the following choices:

Apply this rule after I send the message

with _______ in the subject

and which has an attachment

defer delivery by ___ minutes




So your completed rule might look something like this:



Apply this rule after I send the message

with CAU BULLETIN in the subject

and which has an attachment

defer delivery by 5 minutes


You can choose other options (if you do not always some consistent text in the subject, or if you do not send bulletins via attachment), but the important setting is the last one. If you defer delivery by 5 minutes, the email will sit in your outbox until the time has passed, then it will be delivered. this gives you some time to catch potential mistakes and correct them before the email goes out.

This approach is not readily apparent because the Outlook rule specifies that it will be applied, "after I send the message". To most users, this means once it has been delivered, but they are actually two different actions. Using this approach, you can add a delay between "send" and "delivery". Bulletins are the most obvious use, but you can apply this (and other) rules to an infinite set of circumstances!



Add a Picture to MS Excel Comments (Pop-Up) (Tip)

A MS Excel comment is a pop-up box that appears when you hover over the cell with your mouse.

Cells containing comments are indicated by a red triangle in the upper right corner.

Right-click the cell where you want to add the comment/photo and select "Insert Comment".

Make sure the comment block is blank (i.e. contains no text).

With the blank comment block added to the cell, right-click the cell again and select "Show Comment". This will keep the comment block open.

Click on the comment block and then right-click on the border of the comment block, and select "Format Comment".

On the "Format Comment" pop-up select the "Colors and Lines" tab.

On that tab, select "Fill" and "Color", and then in that pallet select "Fill Effects" (at the bottom).

On the "Fill Effects" pop-up select the "Picture" tab. Now use the "Select Picture" button to choose the picture you want to add to the comment block in your cell. Click "OK" to add picture.

Finally go back to the cell (on the MS Excel spreadsheet) that you selected for the photo and right-click and select "Hide Comment".

Now when you hover over this cell with your mouse the picture you selected will pop-up.

Adding picture pop-ups to your MS Excel worksheet allows you to include (for example) individual photos with identification information, or property photos with an inventory list.

Pipl.com - FREE Search Tool (Tip)

pipl.com is another great & FREE people search engine.

ArcView Google Hybrid (Tip)

CrimeMapping.com is a public crime mapping tool that focuses on data integrity and allows agencies to automatically post their data to a Google mapping portal. Because it is a hybrid of both ESRI and Google technology crime data is more accurately located on the map.

Use Excel's VLOOKUP Function to Compare Lists (Tip)

Using MS Excel "vlookup" to compare lists.
As crime and intelligence analysts we often have to compare two lists to find data that appears on both lists. Finding case numbers, names, or locations that appear on different lists can be tedious and inaccurate if done manually, especially if the lists contain numerous entries.
One way of comparing lists is to use "vlookup" in MS Excel. The vlookup function takes a lookup value and compares that value to a table array. If a match is found the function returns the cell contents from a specified column index. Vlookup can return exact matches or close matches.
Let's say that we have two lists of names on an MS Excel spreadsheet and we want to find out what names are contained on both lists. List-1 is in column A and List-2 is in column D.

Column-A
List-1
SMITH
JONES
JOHNSON
WILSON
GREEN
NELSON
HANNIGAN
CHESBRO
HATCHER


Column-D
List-2
MASON
BRADLEY
WHITE
CHESBRO
BILLINGS
ROBERTS
WILSON


Our vlookup formula is =VLOOKUP(A1:A10,$D$1:$D$7,1,FALSE)

This formula tells MS Excel to compare each name in cells A1 - A10 with each name in cells D1 - D7 (note that we use the "$" Absolute Reference for the cells containing the table array). When the lookup value matches a cell in the table array our formula returns the contents of that cell (i.e. the matching name). The "False" at the end of the formula tells MS Excel to only return exact matches.
Note that if the table array contains multiple columns we can tell vlookup to return the contents of any cell in the associated row by changing the column index (in this case the number "1").
We enter our vlookup formula in cell B1 and fill down column B matching the number of cells we have in our lookup values in Column A.

Column-A
List-1
SMITH
JONES
JOHNSON
WILSON
GREEN
NELSON
HANNIGAN
CHESBRO
HATCHER
GRANT

Column-B
Vlookup
#N/A
#N/A
#N/A
WILSON
#N/A
#N/A
#N/A
CHESBRO
#N/A
#N/A

Column-D
List-2
MASON
BRADLEY
WHITE
CHESBRO
BILLINGS
ROBERTS
WILSON


The vlookup function returns the data in column-B. In this case we see that the names "Wilson" and "Chesbro" are found in both lists. Where the lookup value from column-A wasn't found in column-B, MS Excel returns #N/A.
The vlookup function is a useful tool which makes data matching quick and easy.

Creating a "Top N" Query in Access Without Ties (Tip)



Sometimes you want to create a “Top N” query in Microsoft Access: top 5 repeat locations, top 10 calls for service, etc. It’s not very intuitive in Access, but here’s how you do it!

1) Create a select query in Access which summarizes the data you want. For example, you might choose [ReportNumber] (Count) and [Offense] (Group By), [District] = 1 (Where), to get a count of offenses for District 1.

2) Once you have the query tweaked, right click in the gray area of the query designer (by the table(s) used in the query). Choose "Properties".

3) For "Top Values", enter the number of records you want (choose an existing value or enter your own, such as “10”).

4) This will return the top N records, but it will also include ties, so you might get 14 records instead of the top 10 you expected. To fix this, you need to add sorting on a field, even if it’s meaningless. So in the example above, you might sort by District ascending. Since you are only pulling data for one district, the sort will not affect the results, but adding the sort will force the query to only return 10 records.

5) You may need to go into SQL view (View menu -> SQL View) and manually rearrange the ORDER BY statement so that it sorts first by the count descending, and then by whatever additional field you sort by (such as district).

Another hint: If you need a crosstab-style top N query, then you will need two queries.. First, create a crosstab query which contains the rows and columns you want. Then build a select query (as documented above) using the crosstab as the source of the select query. The select query will show only the Top 10 records from the crosstab.

Truecrypt encryption (Tip)

It is easy to have your templates, most used documents and links or even complete databases with reference data with you on a USB flash drive. The average storage capacity of a flash drive nowadays is 4 to 8 Gb which means you could take a lot of data with you.

The downside of flash drives is the data security: incidents where drives with confidential and sensitive data have been lost occur frequently. The open source software Truecrypt (truecrypt.org) provides strong encryption that can be used on flash drives to keep your data secure.

Open source means that it is free and that the source code is publicly available for review which significantly decreases (some say: excludes) the possibility of build-in backdoors.

Truecrypt is easy to use, it is fast (no noticeable delays because of the encryption) and multi platform (Windows, Mac and Linux). Under Windows flash drives can be configured in a so-called 'traveler mode' so that they are fully portable and that there is no need for the Truecrypt software to be installed on the PC.

When not open, the encrypted Truecrypt containers behave like an ordinary file so making a backup is easy and your backups are secure as well.

Wikimapia Mapping (Tip)

wikimapia.org

WikiMapia is a Web 2.0 project to describe the whole planet Earth. It was created by Alexandre Koriakine and Evgeniy Saveliev, inspired by Google maps and Wikipedia.

This program has some cool features including geolocation on IP addresses. You can also create an account and save locations, create overlays/polygons, and measure distances and area. The imagery is pretty impressive for some locations.

Assessor and GIS Websites (Tip)

Many cities and counties have websites that allow you to search properties. Below are some examples of the types of information you can get:

-GIS, with satellite view with property lines
-Sketch of structure layout
-Owners name
-Dates of sale and amount of transaction
-Photographs of the property

My hometown of Cedar Rapids, IA has a very good site. You can check it out at cedar-rapids.org/assessor/pmc/

To see if a town or county has a website, do a simple google search.

US Search website (Tip)

USSearch.com

This website allows you to search for individuals using their first and last name. What sets this apart from some other websites is that it will give you some information without having to pay for a membership.

I will use myself as an example. I entered my first and last name, age, and state (Iowa is where I am from). I could leave state and age blank, but it is better to use these pieces of information to narrow down the search, especially for common names.

What I get are two results, one of which is me. It tells me that I also go by Jeremy R Riley (alias), that I am 27 years old (no DOB), and that I have records connecting me to several cities, all of which are correct. For most results, you also get a list of relatives. Below were my results:

Jeremy Riley
Jeremy R Riley
27
Key West , FL
North Charleston , SC
Charleston , SC
Cedar Falls , IA
Cedar Rapids , IA

How might this be useful? Well, its free. You get alias, other cities a person may have connections to (good for fugitives), and relatives. Not bad for open source. Test it out and tell me what you think. I recently used to it assist a local police department in locating relatives of a person who had passed away.

Quick Comparisons (Tip)

AreaConnect is a local search tool, allowing you to search multiple yellow pages, white pages and directories...all tailored for your US city or town. They also allow you to do crime stat comparisons. Compare FBI Part I crime rates between two cities quickly using http://www.areaconnect.com/crime/compare.htm.

You can also obtain census tract data quickly by visiting http://www.dataplace.org.

 
First | Previous | [ 1 ] | 2 | 3 | Next | Last

 

Filter by Category

Search Resources

 

Submit a Resource

 

Subscribe

IACA members can subscribe to the RSS Resource Center. Members can receive notifications of all new resources, specific resources, all new comments, or comments only on a specific item.

If you are not member, click here to apply.