By John Paul Kemp

The following is a brief outline used at a training to show how to use MapPoint by Microsoft to map information contained in Clients Case Management. It should be used as a guide with MapPoint. The whole process of creating a map, after having done one, should take about ten minutes. The maps can be generated by state, county, zip code or census tract. However, since our client's usually don't know what census tract they are in, you would have to use a program to glean this information from the zip code and address.

1. Get Map Point 2001 and install (about $210 street price)

2. Decide what you want to map

3. Different approaches to mapping

a. Use the internal data located in Map Point
b. Link to a Query
c. Link to a Table
d. Link to Table but use Map Point to limit

4. For speed and ability to check I like to:

a. Make table query
b. Look at Table for accuracy
c. Open up Map Point
d. Use Map Wizard
e. Link to Table
f. Make Changes

5. Example of a Make Table Query

a. Open up Clients
b. Drop into Access (Clients for Windows / then Close OR Button on button of notebook disguised as part of the Notebook)
c. What happen if Gray screen?

i. You either aren’t running full version of Access OR
ii. Your startup properties on Icon or Start Button uses a /runtime and fools the program into looking like runtime
iii. If ii, then change properties of Icon or Start Button choice OR start up Access from Start Button and then choose CFW2000 from list of recent files.

d. Example of Cases Closed by Zip Code

i. Go to Queries Tab
ii. Push New Button
iii. Add the ClientsW table
iv. Press Close Button in Small Window
v. Add Czip and Casenum to grid by double clicking on them
vi. Press the Totals button (the sigma character)
vii. Change the pull down under Casenum from Group by to Count
viii. Run the Query to test (red exclamation button)
ix. Eyeball to see if data makes sense
x. Press blue triangle button on far left to go back to grid
xi. Choose Query from top line
xii. Change the type from Select to Make Table
xiii. Name the table _CasesByZipCode, the underline places it at the beginning or ending of the list of queries (it varies)
xiv. Run the query, it’ll tell you how many records will be created
xv. IMPORTANT – The table is stored in your ClientsW on your local workstation, unless you did the above steps after opening ClientsT2000.MDB directly on the server.
xvi. Save the Query under the name _QCasesByZipCode

e. Example of Percentage of Single Parent Households

i. Go to the Queries Tab
ii. Open up the _QCasesByZipCode in the Design Mode
iii. Go to File and choose Save As and type in _QSingleParentsByZipCode
iv. Add Adults and Children to the Grid
v. Change the Group By under each to Where
vi. Under Adults place 1 on the Criteria Line
vii. Under Children place >0 on the Criteria Line
viii. Reselect Make Table Query and when it asks for a table name, type in _SingleParentsByZipCode
ix. Run the query, it’ll tell you how many records will be created
x. IMPORTANT – The table is stored in your ClientsW on your local workstation, unless you did the above steps after opening ClientsT2000.MDB directly on the server.
xi. Now Create a new Query
xii. Go to Queries Tab
xiii. Push New Button
xiv. Add the _SingleParentsByZipCode and _CasesByZipCode tables
xv. Press Close Button in Small Window
xvi. Drag the Czip from one table and drop on top of Czip on other table; a line now joins the tables.
xvii. Add Czip to grid by double clicking on either one
xviii. Put your cursor in the top row of the second column
xix. Type in (_SingleParentsByZipCode!CountofCASENUM / _CasesByZipCode!CountofCASENUM) *100
xx. Run the Query to test (red exclamation button), this should give you the percent of single parent households in each zip code
xxi. Eyeball to see if data makes sense
xxii. Press blue triangle button on far left to go back to grid
xxiii. Change the Expr1: in front of your formula to PercentSingleParent:
xxiv. Choose Query from top line
xxv. Change the type from Select to Make Table
xxvi. Name the table _PerSingleParentByZipCode, the underline places it at the beginning or ending of the list of queries (it varies)
xxvii. Run the query, it’ll tell you how many records will be created
xxviii. IMPORTANT – The table is stored in your ClientsW on your local workstation, unless you did the above steps after opening ClientsT2000.MDB directly on the server.
xxix. Save the Query under the name _Q PerSingleParentByZipCode

Copyright © 2002 All rights reserved Kemp's Case Works, Inc.