Ideas for calculating shares for store (using ONLY attendance & sections)

[Sherri]

Note: The general information in this post is applicable in the Cloud database's Windows App as well as in the Legacy database; however, sql statements would need modification for the Windows App.

This is our first year using Approved Workman & our commander is a bit resistant to too many changes too quickly.  So, her request is that we continue to use bucks/shares instead of points & that we continue to award shares ONLY for club attendance and completed sections (1 per share per night attended + 1 share per section), plus "Other" shares a clubber may get for an exceptional good deed or attitude. (I keep track of those in the "Points - Other" column on the check-in page.)

I can't just change the point values of all the other check-in items to zero, because we use those points to determine team winners each night.  And I can't just convert the points on the "Point List and Worksheet" Report to shares because those points include all the other check-in items.

Your first reaction, I'm sure, is "why not just use all the points and convert them to shares -- it doesn't hurt anything".  And the reply from our commander to that question (I know, since I also asked it) was that it has taken several years of trial and error to get the prices of the items in the store to work out well with the average number of shares the clubbers receive to spend & she just doesn't want to hassle with changing it.  So, I respect that and I'll provide her with the info she requests, but I was hoping that someone with more experience with the software could come up with a clever solution.

From what I can figure, I'll need to manually create a report in order to capture the info that the store manager will need.  I can look on the clubber grid to easily see how many days each clubber has attended & award one share for each.  And I can run the Achievement Report to easily see how many sections a clubber has completed.  Then I'm planning on adding those up in my head, along with the "Other Points" and typing them into an Excel or Word document next to each clubber's name.

Does anyone else do anything similar to this?  An enhancement could be to add a "use for store" checkbox next to each type of Points in the "Club Names, Logos, Dues and Points" tab.  But I'm guessing the better answer is to eventually raise the store prices to allow clubbers to receive shares for all the other check-in items.

Any ideas or opinions?

[thewatkinsons]

This is our first year with Awana, so we are choosing not to do the Awana store to keep things a little simpler.  How often do you run the store, and how want to keep track of bucks "spent?"

If you are opening the store monthly, during the third month, how will you know to exclude bucks spent in the first and second months?

From what I can figure, I'll need to manually create a report in order to capture the info that the store manager will need.  I can look on the clubber grid to easily see how many days each clubber has attended & award one share for each.  And I can run the Achievement Report to easily see how many sections a clubber has completed.  Then I'm planning on adding those up in my head, along with the "Other Points" and typing them into an Excel or Word document next to each clubber's name.

Not necessarily, the following solution will at least save you the trouble of typing each clubbers name and adding up the various data points.  It can be modified depending on how you manage your store/keep track of shares spent.
1) Go to Admin->Database Utilities->SQL Workbench
2) Copy and paste the text below into the white open window:

SELECT P.Person_ID, P.First_Name AS "First Name", P.Last_Name AS "Last Name", SUM(PS.Attend + PS.Sections + PS.Other_Points) As "Awana Store Shares"
FROM Person P, Person_Session PS
WHERE P.Person_ID = PS.Person_ID and PS.Club_Year = '2009-2010'
GROUP BY P.Person_ID

3) Select "Run SQL"
4) You can select Export to send the data to Excel
5) You can also "Save SQL" so that you can store these commands on your computer and open and run them later.

The AwanaStoreShares is the sum of the following columns for all club weeks during the 2009-2010 club year: Attendance, Sections Completed, and Other Points.  You'll need to change the text above 2009-2010 each club year.

One way that you could keep track of bucks "spent" is entering negative values into Other Points--the commands above will continue to work, but this has other consequences when adding up all points to determine the night's winners.

There are other ways the commands could be written to accommodate how you run your store--just let me know and I'll see what I can do.

[CommClay]

We had the same issue when we started to use the software.  The first year I used our old system for store.  Last year I was determined to start using the software store system.  This is how I determined how to raise our prices.  I took the store report from the system and exported it to Excel.  Then I calculated the shares that would have been earned under the old system.  I put the old number of shares in the column beside of points.  I calculated the ratio of points earned to shares earned and raised the store prices accordingly.

Before somebody asks:  Yes some clubbers came out ahead and some behind with the conversion.  But it was worth it to use the database as it was designed.

[Sherri]

Thanks for the great ideas.  I had not thought about running a sql command to get the info.  That will definitely work better!  & now that I know those table names, I can tailor the sql command as needed (I don't want to include the leaders & I'll probably group them be club/team color).

I think using a negative number in 'other points' to keep track of spending will work too, as long as I enter them after the team points have been calculated for the night.

And hopefully I'll be using that point conversion idea in the near future!!

Thanks again!

[Rick Leffler]

I don't recommend modifying data through SQL statements and I can't support it.
However, with that said... if you DO want to record points spent during store night and are working with SQL to do this, you should use the Person_Session.Points_Spent field instead of the "Other" field.  This is essentially what the Tools > Club Store screen uses to record the number of points spent.    And, since you're using SQL and not the supplied interface, you'll need to remember to run the Tools > Recalculate Total Points utility.

[Sherri]

Ha! No worries -- I'm not too keen on the idea of actually UPDATING the database using SQL.  I just queried it to get the data I needed for a spreadsheet.  It worked well, too!

We decided that I would not keep up with the shares spent within AWdb, but that we would keep any left over shares in an envelope with each kid's name on it.  Each store, I'll just add new shares to their envelope given info from the query (with dates updated, of course):

SELECT P.Person_ID, PS.Club_Name,P.Mailing_Name AS "Name", SUM(PS.Attend) AS "Attendance", SUM(PS.Sections) AS "Sections", SUM(PS.Other_Points) AS "Other", SUM(PS.Attend + PS.Sections  + Other_Points) As "Total Shares"
FROM Person P, Person_Session PS
WHERE P.Person_ID = PS.Person_ID and P.Member_Type = 'Clubber' and (Status = 'Active' or Status = 'Visitor') and PS.Session_Date between'2009-09-09' and '2009-10-14' and (PS.Club_Name = 'Sparks' or PS.Club_Name = 'T&T')
GROUP BY P.Person_ID
ORDER BY PS.Club_Name, P.Mailing_Name

[DavidCrow]

This is our first year using Approved Workman & our commander is a bit resistant to too many changes too quickly.  So, her request is that we continue to use bucks/shares instead of points & that we continue to award shares ONLY for club attendance and completed sections (1 per share per night attended + 1 share per section), plus "Other" shares a clubber may get for an exceptional good deed or attitude. (I keep track of those in the "Points - Other" column on the check-in page.)

I can't just change the point values of all the other check-in items to zero, because we use those points to determine team winners each night.  And I can't just convert the points on the "Point List and Worksheet" Report to shares because those points include all the other check-in items.

Your first reaction, I'm sure, is "why not just use all the points and convert them to shares -- it doesn't hurt anything".  And the reply from our commander to that question (I know, since I also asked it) was that it has taken several years of trial and error to get the prices of the items in the store to work out well with the average number of shares the clubbers receive to spend & she just doesn't want to hassle with changing it.  So, I respect that and I'll provide her with the info she requests, but I was hoping that someone with more experience with the software could come up with a clever solution.

From what I can figure, I'll need to manually create a report in order to capture the info that the store manager will need.  I can look on the clubber grid to easily see how many days each clubber has attended & award one share for each.  And I can run the Achievement Report to easily see how many sections a clubber has completed.  Then I'm planning on adding those up in my head, along with the "Other Points" and typing them into an Excel or Word document next to each clubber's name.

Does anyone else do anything similar to this?  An enhancement could be to add a "use for store" checkbox next to each type of Points in the "Club Names, Logos, Dues and Points" tab.  But I'm guessing the better answer is to eventually raise the store prices to allow clubbers to receive shares for all the other check-in items.

Any ideas or opinions?

The problem we have in trying to keep track of Awana Shares via the software is that they can be handed out anytime during club.  There's no way that information could accurately get back to the secretary's records.  We hand shares out before club starts to those clubbers that are sitting down quietly, during gametime for those clubbers that are being good sports, helping, etc, during Handbook time for completing verses, sections, books.  We also found it promotes responsibility to have the clubbers keep track of their shares.  The clubbers also get to see/touch the shares so they know at an instant how they are doing.