In FileMaker, queries are finds. And you can use relationship to filter your queries. So go to your graph (File > Mangage > Databases and select your Relationships tab. Select your table occurrence and click ++ to duplicate it. Then use cursor to drag line from Station Code to Station Code (it will establish relationship 'filter' on Station Code using = ). This is called a selfjoin so for this example, name it sj.
Now create a calculation in your table as:
Let ( [
first = sj::out - sj::in ;
last = Last ( sj::out ) - Last ( sj::In )
last - first
There are other approaches but it would depend upon 1) your FM version, 2) how you want the results displayed (whether you want to see each calculation result when view the Station Code records or whether you want a report.
If you want a report and the results to look something like this ...
62753 ... 558
62755 ... 90
62756 ... 110
TOTAL July, 2010 ... 700
62753 ... 300
... then we would need to also join on month/year and you will need to create a find for the time period and then create a columnar list layout grouped first by month/year and then by Station Code with sub-summary.
how can I define in this way start and end date? My result should be displayed im "Form View"
becouse of better visualisation i will use some grafics in background.
1) FM 11
2) Want the results in Form View
Maybe it is simpler when you see what i mean:
Great example! I've uploaded your file with the changes. I'm not clear whether you want the start to be EQUAL to the timestamp you enter or if you want simply the earliest Station Code entry after the start time you entered. Same with end. And it seemed confusing that the In is greater than the Out, so this can't be time seconds.
I'm sure you can adjust to meet your needs; if not please let us know and we can help you adjust it. One thing - you will want to remove the dash from your field name because FM (and many database programs) don't like it. :^) You can download the revised file here:
UPDATE: For some reason, I cannot use links - sorry about the format of it.
This is extra good! :-) thanks LaRetta!
Now i understand how to use self-join, FM rocks :-)