Help with Select Sum

[hair on fire]

Hi all,

I'm relatively new to the ExecuteSQL statement but I want to learn more about it in order to create a dashboard of charts without having to worry about the underlying layout's base table.  I am a lab manager who oversees 4 funds each with different fiscal years.  I'd like to see real time totals for each fund.   I'm stuck with how to incorporate the various fiscal years to the total spend for each fund.  Do I need a statement for each fund?

I have tables for Orders, Funds and a Dashboard.

I was able to successfully write an SQL statement that allows  me to chart how much each lab user is spending each calendar year (via a User table).  But this has me stumped!

Let me know what information would be helpful for those out there who might help me!

Many thanks in advance!

 

 

[Fitch]

You can use greater than / less than symbols with the fiscal years (or BETWEEN but I've found that to be slow) and GROUP BY.

Not sure if that's what you're asking about.

Maybe this would help: seedcode.com/sql-subqueries-in-filemaker/

Also look for ExecuteSQL articles at filemakerhacks.com

Welcome to the forums!

[hair on fire]

I'll have a look at your links, thanks!

The 4 different date ranges are what is confusing me.

 

M

 

[Fitch]

Probably not a big deal to do four separate finds if it's easier on your sanity.

[hair on fire]

I shall investigate.  What I'm learning is that there are several ways to accomplish the same thing.  And you are right - it's about what's easiest on my sanity.  Thanks for your help, I'll keep  you posted!

[beverly]

can you explain (in steps) how you would get the necessary data (to write down on a piece of paper for a report)? using finds/sorts even in this must be done multiple times. often clearly defining what's needed and how you might do "native" fmp to get values can help create any queries. If you are using relationships and/or filtered portals, please list those as well with relavent matches and calculations.

thnx

beverly

[hair on fire]

On 3/25/2016 at 4:25 AM, beverly said:

can you explain (in steps) how you would get the necessary data (to write down on a piece of paper for a report)? using finds/sorts even in this must be done multiple times. often clearly defining what's needed and how you might do "native" fmp to get values can help create any queries. If you are using relationships and/or filtered portals, please list those as well with relavent matches and calculations.

thnx

beverly

Sure!  I currently I do a find for each fund.

Find:  

Fund  -  ABC

Date Ordered - 6/1/2015...5/31/2016

Total - calculates the total of line items for ABC between June 1, 2015 and May 31, 2016 (the fiscal year for ABC) 

I've attached a file that shows my set up.  Log in as Guest for full access.

LAB_orders_Copy.fmp12