Is Filemaker the Right Tootl for my project?

[Ranginald]

I am an access user and I need to make my access database work on the Mac. I am working with Filmaker 6 Pro trial version in order to see if this package will work for my needs. I am having quite a bit of trouble with more complicated queries (that were relatively easier to design in access) and I was wondering I what I am trying to do is even possible in Filemaker.

Again, I'm not looking for the details how to do these things (actually, I am) but for THIS question all I really want to know is if these things are possible. Once I know that it's possible, then I spend some time figuring it out. I don't want to waste anyone's time explaining something again that's probably explained somewhere else.

1. I can't seem to write and store SQL queries for my existing filemaker data. The program seems to only let me import from other data sources like access or sql serever --as if I only need queries from existing databases!

I tried to import my access queries, but those that span more than one table won't work. (I've used the SQL importer (don't know the name) in Filemaker to associate the fields correctly, but some of the fields in my query are associated with other tables (now filemaker files).

I can make a query for one file, but I can't seem to have a query that includes 3 or 4 filemaker files. In access I'd use a select statement, eg. SELECT * from tbl_1 AND tbl_2 AND tbl_3 WHERE etc. etc. Is there a way to write a SQL SELECT statement that spans mutliple files and can be stored as a layout?

2. Also, every time I create a query, it returns the data INTO the current file. Can I make a query as a script that can be called when I want it, not when I design it?

3. Does filemaker support queries upon queries upon queries? I have a situation where I 1) count the number of sales records for a specific department by each employee 2) I then use this query as the basis for a second query that returns the employee and the with the most sales in that department (e.g returns Joe Smith -- 5 Sales -- Record Department. Can this be done in filemaker?

4. Is Filemaker TRULY designed to be a relational database. Access, Oracle and SQL Server seem to be all about relational data, wherease filemaker's manual has one chapter on relationships. I've used Filemaker for other simple tasks, and I am finding it much easier to use for these simple things as compared to access. But when it comes to the tough stuff above, I wonder if it's the right way to go.

Thanks in advance for your help!

[Anatoli]

FM is different philosophy and World than standard SQL. I am 99% sure you can get to the required results, but you have to program that the FM fashion.

Scripts, calculation fields, globals are at your power. Not the power of SQL and SQL relations.

But you have powerful visual interface tools. Robustness and speed not matched by Access.

There is also FM version 7 in pipeline. If will probably follow more close the Database/Table model you are used to.

[Fenton]

Fields:

Sales (number)

SalesMark, calculation, = Case (SalesAmount > 0, 1, "")

Summary field, SalesMark_Sum, = Count of SalesMark

Summary field, Sales_Sum, = Total of Sales

Subsummary Parts (break field & sort by - fields on layout):

Department - Dept. field

Employee - Employee, SalesMark_Sum, Sales_Sum

Sort by Department, then Employee, then "include summary fields" and SalesSum (descending)

Enter Preview Mode. The top-selling employee for each department will be at the top of the list for each departement.

Of couse, the other's will also show. You can get what you asked, ie., only the topmost. But that requires self-relationships.

I've done it in a small file, if you want to see. But it's not a pretty sight.

Report Summaries.zip