Best Way to Do It

[Neil Greenough]

I am currently a police officer in the UK and I am trying to device a
spreadsheet / database that will highlight problem hotspots.

Now, I intend to create a spreadsheet that will highlight areas that are
affected by different crime. So, in one sheet I will have burglaries, car
crime in another, antisocial behaviour in another.

Now I am unsure what data the spreadsheet should contain and how to display
the results. I would like the results to come back on an individual name /
address basis per sheet in the file, but I would also like it to produce
results on road names.

So for example, if Mr Jones lives in 123 High Street, I would want the
spreadsheet to highlight it if Mr Jones gets burgled on two or more
occasions. Nonetheless, I would also like some results produced if two or
more burglaries are recored in High Street, regardless of the house number.

How would I go about this? Can I search cells in a column for specific
words, so in the above case, "High Street" regardless of number?

Many thanks

 

[Phillip Windell]

You would need a multi-table database,..not a spreadsheet.

Spreadsheets only physically "resemble" database tables but they are not the
same thing, do not have the same structure, and do not work the same way.

Here is an very rough idea, just to show what I have in mind.

Tables: (tbl means "table", fld means "field")
tblVictems
fldVictemID (Primary Key)
fldVictemName
fldVictemAddress
fldCrimeID (Foreign Key)

tblCrimes
fldCrimeID (Primary ID)
fldCrimeType (like Burglary, Rape, Murder)
fldCrimeTimeDate
fldCrimeStaus (like Solved, Unsolved, Investigating)

Then you could create a Query that would, for example, ask to "Show all
Victems where fldCrimeType equals Burglary between fldCrimeTimeDate and
fldCrimeTimeDate"

That isn't the real code for the query of course, that is just the human
readable version of what it is asking. Notice how I carefully choose the
Field names so that you know by looking at them which table they are from
(like "fldVictemName" instead of "fldName"), and how the tbl* and fld* show
if you are looking at a Table or a field in a Table.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.asp
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.asp

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp

Deployment Guidelines for ISA Server 2004 Enterprise Edition
http://www.microsoft.com/technet/prodtechnol/isa/2004/deploy/dgisaserver.mspx

 

[Phillip Windell]

Tables: (tbl means "table", fld means "field")

tblVictems
fldVictemID (Primary Key)
fldVictemName
fldVictemAddress
fldCrimeID (Foreign Key)

To search by Street Name reguardless of house number you would want to have
the Victem's address broken down by it parts like this:

tblVictems
fldVictemID (Primary Key)
fldVictemName
fldVictemAddressHouseNumber
fldVictemAddressStreet
fldVictemAddressCity
fldVictemAddressStateProvince
fldVictemAddressZipCode
fldCrimeID (Foreign Key)

You then Query on the basis of "fldVictemAddressStreet".
You could breakdown the Victem's Name by FirstName & LastName in the same
way. You don't have to use my names of course,...I'm only trying to show
the pattern of how to think about it.

Most likely something like this has already been commercially written and
you won't have to create it yourself. Ask around at other Police Stations,
particulary larger cities.

Phil

 

[John Welch]

Just don't confuse Mr. Tuttle with Mr. Buttle (I just saw Brazil last
night )