Moving from MS Access to Filemaker

[IsleofGough]

I am trying to eventually not have to maintain software on OSX and Windows. I have used a variety of database solutions in the past (dBase, Foxpro, Paradox, and Access) and tried Filemaker 11 several years back, but could not do the data mining I could do easily in Microsoft Access. I purchased a copy but probably never gave it a proper trial. Most of the data mining I did in Access involved linking select queries with group queries (with occasional update, append, or delete queries). In Filemaker, at the time, it appeared that calculated table fields and the find box were close to select queries, but the group by queries were done by reports in Filemaker, and could not be linked back to the find selections. I gave up.

I want to try Filemaker again, and have purchased FM14. Most of the queries are health care related, but I was wondering if I could give a simple example and get your advice if this is going to be possible in Filemaker.

Assume I run a company that sells items on the internet, and I have three tables to manage my customers and items sold. The customer table has a customer number, name, and address. The second table has items available and has item number, description, price, and category (food, book, software, housewares, etc.). The third table has purchases and has customer number, item number, date purchased.

I want to create a flier to mail to the top 1000 customers who purchased the most number of items in calendar year 2015 and have the flier advertise items that roughly match the percentage of each category of these customers purchases over the past year. Since I want to create only 1000 fliers, I will use a tie breaker of total of purchase prices if there are more than 1000 customers who bought the same number of items.

In Microsoft Access, one would first create a group query: grouped by customer number, where purchase year = 2015, and a field that counted purchases, and a field that counted cost of these purchases. It would be sorted in descending order on the last two calculated fields.

The top 1000 records would therefore match the top 1000 customers for 2015. I would create a new query that grouped by categories of purchases (food, book, etc.) for these customers for calendar year 2015 and calculate percentages. I would then create the flier and use the customer information on the 1000 customers to create a mailing.

In MS Access, all select and summary queries are just queries, so they can be linked together. Can Filemaker do this, or are the summeries only available in reports and can they be somehow linked to a "find" selection? I know that calculated fields in Filemaker are put in the original table as calculated fields, unlike in Access where they are in a query, but if there is a way to do this in Filemaker, I might be able to abandon maintaining Bootcamp sooner. Thank you very much!

 

 

 

[Wim Decorte]

You've got some answers already on TechNet, but I'll repeat mine here: do some research into the Virtual List / Virtual Table technique to display results of a query.

Some of the alternatives presented call for adding calc and summary fields to your main table.  I'm not a big fan of that since it clutters the table definition and - when used carelessly - can lead to performance degradation when the solution needs to scale up.

[ggt667]

Can't you just ORDER BY and LIMIT the SELECT query?

Edited November 30, 2015 by ggt667

[Josh Ormond]

Possibly you would have to test it to make sure it works. The problem if I remember it correctly is that the data is coming from more than one table. Definitely a key candidate for Virtual List.

[IsleofGough]

Thank you for the replies. I will try to find some information on Virtual Lists. I did a google search, and it does not seem very intuitive, but I will follow a simple example.

[Josh Ormond]

Here is a very simple example. As you google and search more, you will see more implementations of it.

https://filemakermisc.wordpress.com/category/virtual-list/