Operating System Version? Filemaker Version? Local or network Database?
Sometimes screen shots of the tables help explain.
I understand Store No. Article No is What? Category Is What? Sales is What? Number Field.
There are five database design links at the start of this List http://forums.filemaker.com/posts/f6ed4be796?commentId=222931#222931
Google "report portals site:forums.filemaker.com"
Version 11 on OS X. Local.
All of those are fields and only sales is a number the other are text.
I have read all of those.
When you sort your records to get your summary report subtotals, there's an option to re-order your groups by the computed sub total. I'm not sure from your original description, but that may be all you need in order to "rank them by total sales".
There are also a number of other options for computing totals and sub totals that may serve.
I don't know what happened to my previous response to PhilModJunk but I will try again.
In Access what I did to get the information I wanted was create a query using fields from both tables that selected the sales by category and had a calculation field that summed each record and then I simply did another query that gave me only the store no and the computed calculation and sorted it descending and I added a count field that I used <100 in the criteria to give me the top 100 stores.
In FM I can get a report that will find the sales by category but the report will only give me a detailed list with a summary for each customer. It gives me each individual record with a sub total summary following it. I want a layout that only gives me the summary and the subtotal and then I want to be able to sort and count those subtotals.
I can't believe this can't be done and I can't believe it would require a script to do it.
In a summary report, If you want only one row for each group of records, you can set up a layout where there is no body layout part--only one or more subSummary layout parts.
How do you then reference those summaries in other layouts? Say want the top 100 by that summary or I wish to then sort by state or something?
You'd use a different method for computing those sub totals. You might, for example, use ExecuteSQL to acquire those subtotals.
Ok that is beyond me. Wish I had my money back for the three versions I have bought over the years!
Thanks for the time and help. I will just upgrade VM Fusion and Access and stay with something I know.
I am sure Fm is great for database IT people but for managers of businesses like me that need to crunch numbers quickly and easily it is just too complex. My customers give me this data and it comes in different forms. I can't pay a consultant to build a new FM data base for every possible file format of sales history some big box retailer gives me.
You can also use relationships based on your category fields to compute the same sub totals.
I will just upgrade VM Fusion and Access and stay with something I know.
Strange, anytime I work with Access I end up devising SQL queries--the very thing you need to know to get ExecuteSQL to work.
I can't pay a consultant to build a new FM data base for every possible file format of sales history some big box retailer gives me.
I see no reason why it would be necessary to "build a new database" for "every possible file format"....
You can also use relationships based on your category fields to compute the same sub totals
I did do the relationships.
I see no reason why it would be necessary to "build a new database" for "every possible file format"..
That is because the field names are never the same. The information is never the same. They all have different article numbers for the same product for example. They may give me information that I will only crunch one time.
Look you are just a lot smarter than me and it is not my fault I can read and learn Access without help and cannot read and learn FM with provided documentation
If queries are SQLqueries then call them queries and make them easy to use---make them so you can run one and then build another off of the first in less than a minute and then provide some documentation on that. The tutorials spend at least 80% of their time on formats and hardly any on data drilling.
Nobody is a bigger Mac fan than me but I am just not smart enough to do FM for the tasks that I do. If I were building the proverbial image database or contact database or non profit contributor database or something I am sure FM would be fine. I don't need any of those.
What I need it do it does not do easily. In access I imported the 42000 records from a spread sheet to make a sales detail table. made a table of items and categories and imported that spreadsheet --ran a query by customer by item category--ran a second summary query and sorted it and did all of that in less than ten minutes.
I still do not have a clue how to work with sub total summaries in FM. I don't even know what a SQLquery is.
It is all my fault. I am obviously an idiot since I can not after hours of reviewing documentation and offering to pay for support even determine the terminology needed to find my solution in FM support.
I am sure some of the greatest databases of all time were built with FM but it is a myth that is easy for non DB developers to use to make anything other than simple databases.
Mike I understand you frustration.
Although it is ver9, it will explain sub-summaries.
This is another source for help on sub-summaries.
If you are willing to pay for a fix, I have done so when I was needed something faster than I was learning it, try this for someone to do what you need. Or just google filemaker pro consultant. It is much easier to use than Access. But I would never go to Microsoft for an Access help either. I hope this helps
For every Number or Calculation field I create, I immediately create a summary field. I use the same field name + .sum. If I have a field for InvoAmt then I would have a matching field InvoAmt.sum. Use the summary field in the sub-summary part to get subtotals, such as by vendor in this example. Or, putting the summary field in Trailing Grand Summary to get totals for the report.
Right. You will in see my screen shot the summary fields in the table I set up.
What I cannot easily do is to use the results found in a summary field after a find or in a layout.
Using your screen shot how would rank Acctg_MSV.Sum by vendor of Dep3.sum by date and return the top ten vendors for department 3 for example.
I am sure you can do it in FM but not as easy as in Access where Acctg_MSV.Sum is a expression field in a query and can then be grouped, summed, counted, averaged, used in another expression ect in a subsequent query.
How would you use TotalDailyDeposit in future layouts easily? What were the 10 worse days for TotalDailyDeposits from Dep3 in 2013? The 10 best?
No question all of these things can be done with FM but not with simple finds and layouts. PhilModJunk is correct it will require some knowledge of SQL commands.
He was right too when he posted this statement in another thread about FM vs. Access
The biggest difference between the two systems is that to get anything significant designed requires a basic Knowledge of the query language called SQL. This query language is very powerful and makes possible certain queries that FileMaker either can't do or can't do easily. But this language is also very cryptic to the new user and thus requires a fair amount of study to learn unless you are already familiar with how programming languages work.FileMaker, out of the box, doesn't query tables with SQL. It uses more of a "query by example" method that is much more accessible to new users and makes setting up simple searches of your data very simple to do. But there's a point, as you attempt to produce more and more complex queries where SQL will out perform the way FileMaker works--so there is no simple way to say one product is better than the other.
I am no programmer and do not want to be. Excel to numbers was easy. Word to Pages was easy. Powerpoint to Keynote was easy. Mail is better than Outlook. Access to FM is not easy.
FM should amend their claims of ease of use in their advertising. It is easy to use if you can manipulate your business to use their templates. It is easy to use if you are an experienced database developer. It is easy to use if you want simple databases. It is not easy to use if you expect to crunch numbers like you did in Access.
Thanks for trying to help.