"Burial" report based on keywords

[dmohn]

I have a very large database with 44,000+ places.  Several thousand have names indicating a particular burial place.  In order for me to focus on building the Cemetery functions in TNG,  I need to capture those Places that have the largest numbers of Burial entries (eliminating Burial counts that just contain more generalized "city, county, state, country" entries or the hundreds of cemeteries having only one or two burials recorded.)

I need some help with a script for a report that would

• select all Places containing "cemetery", "memorial", "burial"
• generate a count of all Burial events associated with the selected Places
• sort the selected Places in descending order of Burial count
• preferably allowing for an input variable for minimum Burial count (but with at least a minimum count of 25 -- I could manually modify the script if needed)

This should be a fairly simple script, but I'm not totally up to date on script writing.

[GOGGS]

Here's a query (below) that you can paste into the Reports tool to generate the results.

Since TNG has the specific burialplace field in the people table, you probably wouldn't need to filter for places that have something like "cemetery" in the field, right? If you query on the places table, that's a different set of data. Maybe try without the filters - mine yielded the same result, except the first/largest entry was blank, with a count of about 33,000, for people that don't have a burialplace entered.

I didn't add a limiter on how many records are returned because they are sorted, and the report will page them by groups of 100. I added a couple of other keywords for a burial place, and you could add more or remove the ones I added. My places are organized with commas such that placelevel  = 2 is a burial place - if you have something similar, you can check yours for other descriptors. 

SELECT burialplace, count(ID) as count 
from tng_people 
where 
burialplace LIKE '%cemetery%' or 
burialplace LIKE '%memorial%' or 
burialplace LIKE '%burial%' or 
burialplace LIKE '%garden%' or 
burialplace LIKE '%mausoleum%' 
group by burialplace 
order by count(ID) DESC;

 

Cheers

GOGGS

[dmohn]

Thanks for the script.😃