How to get Summary Field Right?
Doing some "hack analysis" on my server, I'm trying to get a list (single table) of fields that confirm to the following SQL Query:
max(DateOfHack) AS max_date,
FROM "firewall fields"
WHERE YEAR (dateOfHack)= 2015
GROUP BY DateOfHack, remoteIP HAVING COUNT(*) > 5
ORDER BY GroupCount DESC, DateOfHack DESC
Now, although this query took only about 5 minutes to get right, trying to do this in FMP with FMP tools on a FMP layout is causing me problems.
In particular, I can't see quite how to get a summary field for a layout (like the group count of the individual IPs) that I can further limit via a FIND so I'm only looking at groups greater than a certain count.
Even with the data sorted, a calculation field for COUNT(remoteIP) or a summary field (also for count) both give the wrong answer (1 vs. the total record count).
This isn't a report, but a layout I'm trying to create for data analysis.
Thanks in advance,