The developers of this software have made every attempt to make this search tool as easy to use as possible. But in the end we find it impossible not to empower you with a more powerful query tool that gives you the flexibility to ask questions that we have not considered. This tool provides you with great flexibility however it comes at the cost of being a bit more complex than we desire. So here we will make an effort to provide you with a brief explanation and some examples.
The first thing to keep in mind is that you can't break anything by playing around with this tool. This is a read only tool. Every effort has been made to eliminate the possibility of you being able to damage the database. Having said that, I know that some users will take that on as a challenge and to them I add that we have backups for the databases and we will continue to improve our software to eliminate any threats to the integrity of the data base.
This query tool utilizes a subset of the Structure Query Language "SQL". If we did our job correctly then you have access only to the select statements which give you the power to ask questions and get answers about the data. The other tools provide you with the ability to summarize information about "Vendor" or "Spenders". This tool is unique in that in will take any string of the form "tbl_(any three letters)_(any four numbers)" and substitute that string with the all the table names between From: Month/Year to To: Month/Year. So if you pick a From Month/Year as Sep 2007 and a To: Month/Year as Aug 2008 then this software will run the query on the following tables::
tbl_sep_2007, tbl_oct_2007, tbl_nov_2007, tbl_dec_2007,tbl_jan_2008,tbl_feb_2008, tbl_mar_2008, tbl_apr_2008, tbl_may_2008, tbl_jun_2008, tbl_jul_2008, and tbl_aug_2008. It can also give you sub totals for each month and a find grand total for all months selected. So let's run through a couple of examples to see how you can use this tool.
So let's say that you are at the home page and you select VENDORS. Then you select From: Month/Year as Sep 2007 and then the LOW $ threshold as 20000. When you click submit you will be taken to a page that shows all of the vendors who made sales above $20,000.00 for each of the months selected. However, it does not show you the month subtotals nor the grand total for this group. If you want that, then you can take any one of the queries shown on that page and copy them to your clip board and open this query tool and paste it into the qry text box on the top. If you have taken the first query then the contents of the text box are:
select merchant, sum(amount)as total, avg(amount)as average, count(amount) as items, count(distinct name) as users from tbl_sep_2007 group by merchant having total>=20000 order by total desc
In order for this query tool to give you sub total and totals for each month then the term "sum(amount) as total" needs to be changed to sum(amount) as GTOTAL" (note: this is something that is unique to this tool) and you can accomplish this replacement very easily by clicking on the "Replace" button just below the Search String text box. Next change the From: Month/Year to Sep 2007. Now all you have to do is click on RUN SEARCH and wait. You will get a month by month summary with the same data as before except that now you also get running sub total and a grand total at the bottom.
Next, let say that you want more detail so you clicked the first detail link on the first vendors page. Again you'll get a query on the top of the page. Select it and copy it to the clip board. Launch the query tool and paste into the text area. Replace "total" with GTOTAL. Change the From: Month/Year to the desire date range and hit RUN SEARCH..
If you click the last detail link to get to the lowest level of detail on the Vendor side then again you will have a query at the top of the page. Copy the query to your clipboard. Launch the query tool and paste your query into the text area. Next to the "Replace" button you will see the word "total" in the text box. Change it, to read "amount" (don't include the quotes). and change the the next text box after "WITH" to read "amount as GTOTAL" again don't include the quotes and then Click the Replace Button.. Then change the From: Month/Year drop down to select the appropriate time frame and hit RUN SEARCH. You'll get the same level of detail for all months in the selected range with sub totals for each month and a grand total at the end and these results are include the unique fields in the database which depend upon which vendor they came from.
Well let's just launch the query tool by itself. Lets say that you want to know what your school principal is spending their money on. Then just click the SELECT SPENDOR button and find their name on that extensive list of the SPENDORS. When you find their name on the list click on it and it will return to the query tool with the selected name in the name text box. Click the Make Search String button and Click RUN SEARCH and you'll get a summary of everything that your principal has been purchasing with credit cards for the time frame selected. Again it will have sub totals for each month and a grand total at the end. Don't be to harsh on them because much of what they spend is determined by the requirements of certain government grants. So If at first you get a little angry besure to check out all of the facts before you go and make a big deal about it.
Here's a more advanced use of the Query Tool. Let's say that you noticed that the Office Depot is a regularly used vendor and you would like to know how much is spent on all OFFICE DEPOTS. Then just type in OFFICE DEPOT in the merchant text box and click on Make Search String. The click on RUN SEARCH and you get a big unordered list so try typing in the following phrase and the end of the qry in the text area "order by amount" without the quotes and a space between it and the last character already there. You'll get a list which is ordered such that the lowest amounts come first and if you add "desc" so that it becomes "order by amount desc" then you get to see the big spenders on the list first because desc mean descending (starting with the big and getting smaller).
When I first looked at the data in Jeffco Schools Data Base I was taken back by the amount of money spent on Pizza. So let's use the query tool to check it out. First just put the word pizza in both the merchant text box and the description text box, then click on the "ors" radio button and then press Make Search String and RUN SEARCH. WOW that's a big number. Just for kicks press the Make Search String again and then press RUN SEARCH. Wow . That's not as big. That's because in the first case we took record that had the word pizza in either the description or the merchant in the second case we only took the cases where the word appeared in both cases. I think we'd all agree that the first case is closer to the truth in this situation. Don't get angry remember there is approximately 85,000 hungry students in Jeffco Schools and they can eat a lot of pizza. Also keep in mind that most of this pizza is for lunches and that gets reimbursed by the cost of lunches.
Okay here's a real interesting one. Let's say you want to get all of the records in the database that have the words breakfast, lunch or dinner in the description. Enter this into the description text box. breakfast~~~lunch~~~dinner the ~ is a tilde. We use three in a row because you almost never will encounter three tildes in a row in normal text. Click the Make Search String and this is what you see.
select amount as GTOTAL, name, description, merchant, posted from tbl_abc_1234 where description REGEXP '.*(breakfast|lunch|dinner).*'
Wow. That looks complicated doesn't it. It is complicated and it involves something that's way beyond the scope of this document. If you want to know more then check out the MYSQL Reference Manual , but I warn you it's hard to learn just a little. Any way just go ahead and click the RUN SEARCH button and you get a list of everything in the database with either breakfast, lunch or dinner in the description. If you like try adding "order by amount desc".
Just one more thing. A lot of times you want to reduce the size of a list by choosing to look at a subset of the data. For example, the last search results in a lot of records. Perhaps you would be happy to only look at the items which cost more than $20.00. Then this is what you want to put into the SEARCH STRING text box.
select amount as GTOTAL, name, description, merchant, posted from tbl_abc_1234 where description REGEXP '.*(breakfast|lunch|dinner).*' and amount>10 order by amount desc
The order by always comes last. You could also create a range like this.
select amount as GTOTAL, name, description, merchant, posted from tbl_abc_1234 where description REGEXP '.*(breakfast|lunch|dinner).*' and amount>10 and amount<1000 order by amount desc
If you sometimes see records that appear to be the same and you want someway of knowing whether they are or not. The add the term pkid as shown below. PKID is unique for every record in a given table. If the PKID's are different then the records are different.
select pkid, amount as GTOTAL, name, description, merchant, posted from tbl_abc_1234 where description REGEXP '.*(breakfast|lunch|dinner).*' and amount>10 and amount<1000 order by amount desc
Well I think that's enough for now. As We get more questions from you we'll expand upon this document. Good Luck and Good Searching. Stay Calm, Check out the facts and remember that Jeffco Schools is a big organization don't assume that they're spending your money unwisely until you check out all of the facts. Thanks