It's pretty easy to set up in Filemaker, though the approach is definitely different from how you'd do it with SQL and Access.
You'll need what's called a summary report. This report can show individual items within each category and with sub totals or you can set it up to just have the categories with sub totals. You can break things down by month or not if you want.
Here's a tutorial on summary reports. If you want just the categories and subtotals, look for the example near the end that describes deleting the body part from the layout.
Note to make this a report for a specific year, you'd perform a find on the data to find all records with week ending dates in a given range such as 1/1/2010...12/31/2010 (dates are in MM/DD/YYYY format). Such a find can be performed by hand or in a script. (This is analogous to the WHERE clause in SQL)
Don't forget that, to find a entire year's worth of records, you can simply type the year into date field in find mode.
Thank you for the tips. I will give it a try.
Ok I got it to summarize the data properly. I have one summary field that totals the CURRENT YTD sales and another summary field that totals the PRIOR YTD sales. I'm finding it difficult to calculate the percentage variance between these 2 summary fields. I tried creating a calculation field but did not get correct results. Any thoughts? Thanks.
I found my answer. I needed to use the GetSummary function to calculate the variance between the 2 summary fields. I'm not convinced yet that Filemaker is easier to use than Access though.
It takes learning some new approaches. Tricks that work with Access often are a bad approach and completely different methods are used filemaker so you find yourself having to relearn things.
I generally find that Filemaker enables simple tasks in a simple user friendly way such as finding all contacts in a contacts database that live in a particular town that are not simple at all in Access unless the developer specifically creates a tool for something which for FileMaker just works straight out of the box.
As the operations grow increasingly complex, however, the trade-offs between the two systems start cancelling each other out until the tipping point is reached where a decent SQL expression becomes simpler to implement than Filemaker's "Query by example" paradigm.