Author Topic: Calculating the cost of Awards given  (Read 8166 times)

Sherri

  • Global Moderator
  • Full Member
  • *****
  • Posts: 33
Calculating the cost of Awards given
« on: October 14, 2009, 04:27:39 PM »
Perhaps there was another way to do this within a Grid, but I couldn't find it...

We wanted to be able to track how much we spend on awards handed out throughout the year (to help with figuring out a budget).  I export the following query's results to Excel & then sum the Cost column.  It doesn't take into account the multipack purchases, but it's close enough for our needs.

SELECT Person.Mailing_Name AS "Name", Inventory.Description, Inventory.Cost / Inventory.Pkg_Qty AS "Cost" FROM Person, Award, Inventory where Award.Person_ID = Person.Person_ID AND Award.Item_Number = Inventory.Item_Number AND Inventory.Cost > 0 AND Award.Date_Earned between '2009-09-07' and '2009-10-14' ORDER BY person.Mailing_Name
Sherri Meadows
Monument, CO

Rick Leffler

  • Guest
Re: Calculating the cost of Awards given
« Reply #1 on: October 14, 2009, 06:34:48 PM »
Hey Sherri!  Good idea.    I think it is great when techy folks share their ideas like this.

If you don't need all the details of clubber names and would like to avoid having to export the data to MS Excel in order to use the SUM function on the cost column....   try the following SQL instead.

SELECT sum(Inventory.Cost / Inventory.Pkg_Qty) AS "Cost" FROM Award, Inventory
where Award.Item_Number = Inventory.Item_Number and Award.Date_Earned between '2009-09-07' and '2009-10-14'



Note to those not familiar with SQL...  you would of course need to edit the dates according to your preferences.

dave4him

  • Global Moderator
  • Full Member
  • *****
  • Posts: 48
  • I love this job!
    • First Baptist Church Awana Clubs
Re: Calculating the cost of Awards given
« Reply #2 on: October 15, 2009, 07:22:04 AM »
Here's another solution from an "old school" guy who just wishes he knew SQL  ;)

Assuming that your inventory items are properly recorded, i.e., as a fee or an award, just open the Inventory grid, select your custom date range in the "Last Ordered" column and tick 'True' in the "Is Award" column. This would include your present stock plus what you have given out. Not perfect solution but close... 8)

Oh....let me add that you would have to record all orders in AW for this to work well.
David Barnes
Awana Commander
First Baptist Church
Branford, FL
N.Fl. Awana Ministry Team

Mainah

  • Full Member
  • ***
  • Posts: 33
Re: Calculating the cost of Awards given
« Reply #3 on: October 15, 2009, 12:55:04 PM »
Yikes, I only have 2 Last Ordered dates and 8 last ordered items in my dropdown. 
I've ordered 5 times and at least 30 line items according to my AW Order grid.  Am I missing something?
The dates showing are my two most recent. 
Diane
Saved at Awana in 1975!
Office Director
Cornerstone Baptist Awana Club
Maine

dave4him

  • Global Moderator
  • Full Member
  • *****
  • Posts: 48
  • I love this job!
    • First Baptist Church Awana Clubs
Re: Calculating the cost of Awards given
« Reply #4 on: October 15, 2009, 02:46:29 PM »
Sounds like you are NOT updating your Inventory when items are Received. When you change the order from 'Submitted' to 'Received', little tick boxes appear in the line items. It took me a while to figure that one out, I was changing order status only and my inventory never worked...duh. Sorry to get this post off topic.  :o
David Barnes
Awana Commander
First Baptist Church
Branford, FL
N.Fl. Awana Ministry Team

Mainah

  • Full Member
  • ***
  • Posts: 33
Re: Calculating the cost of Awards given
« Reply #5 on: October 15, 2009, 02:53:29 PM »
Um, actually, I think it might be related to some problems I was having with my copy of the software.  Seems the timing is about right that it started working right after the problems were fixed.  I might go back in though and uncheck and recheck all of those received boxes to see if they then show up in that column.

(wish we had a Backordered option, although it rarely happens so its no biggie!)

Diane
Diane
Saved at Awana in 1975!
Office Director
Cornerstone Baptist Awana Club
Maine

Rick Leffler

  • Guest
Re: Calculating the cost of Awards given
« Reply #6 on: October 16, 2009, 05:36:03 PM »
Mainah, were the problems mentioned those relevant to the dropbox stuff? 

When an Order's Status is changed to "Received", there IS a "Qty B/O"   (back ordered) column that appears. Is that what you meant?

Mainah

  • Full Member
  • ***
  • Posts: 33
Re: Calculating the cost of Awards given
« Reply #7 on: October 16, 2009, 06:29:20 PM »
Yeah, I think it was related to the dropbox stuff.  Didn't want to bring up "that word!"  I'm thinking the only way to bring it all back is to recreate those orders and redo my inventory again.

 :-[ Um, yeah, that.  Qty B/O.  Um, was that always there?  Silly me.  Can't play with it now since all my stuff is in.  Hopefully I'll remember that the next time an item is back ordered!!
Diane
Saved at Awana in 1975!
Office Director
Cornerstone Baptist Awana Club
Maine