SELECT COUNT (PPL.id) FROM
JOIN INT ON PPL.id = INT.id_PPL
WHERE INT.id_SSN IN ( session list )
if you know the key of the sessions, and i presume they are in the INT table, then you don't do join SSN at all. Unless you need extra info from the SSN table... But may be first try this construct to see if you at least get the people you are looking for :-)
DISTINCT gives you unique values. But my question is if you need the resulting value interspersed with your current queries or stand alone in another virtual list?
That's very helpful, and yes, it does return the correct people count. However, it's very slow to do so (unacceptably so), and I still need to build in the additional joins to group by Programme Title, as in the other examples, which will only make it slower. So I think I must have issues with my data - one step forward and another one back!
But thanks for helping me to see how to get the People count.
Hi Beverly. The People count needs to be amongst other values. It's currently a 6 column Virtual List:
- Label - i.e. the Programme Title
- Tot 1 - no. of sessions
- Tot 2 - no. of attendances
- Tot 3 - no. of people
- Tot 4 - no. of anonymous attendances
- Tot 5 - no. of frequent attenders
Here's the portal so far, displayed on a popover:
Please let me know if there's a better way to do this!
if it needs to be separate queries (and it may), then I wonder if there's a possibility to plug into the Virtual List from two (or more) different variables (set by ExecuteSQL).
Or, I wonder if it's possibly a nested SELECT (one of the columns would get the added count). But that can get overly complex and really bog down.
Or, I wonder if you have your Virtual List so far and add a calculation field (or set field with loop) based on the values in each "virtual" record - and using ExecuteSQL.
We don't have temp tables and query-a-query in FMP like in SQL, so we improvise (aka work-around).
I just don't know all the variables. Think about the possibilities here and you may need to combine a few techniques for sake of speed.
I'm not sure but, s.id should be primary key and it has only one s.id_PGM as foreign key for each s record, same as PTI and PGM, so why do you need DISTINCT in COUNT() ?
Maybe a two step approach. Because you could just use the INT table and retrieve the keys of the people and you know the SSN keys already.
Then do a separate query (or a FileMaker relation) to retrieve people data and programme title.
How is the speed if you just do a
SELECT INT.id_PLL FROM INT WHERE INT.id_SSN IN (......)
You could probably make it faster if you can use a date range and it would be helpful if the date was also in the INT table. This would mean storing the SSN date in the session as well as INT, but if you can use a date range that way, it will save a join and speed things up greatly.
Also some one mentioned to me, that getting the keys from SQL and then doing a value count of the resulting list is faster.
Thanks - the other issue that I had was with the same analysis function, but relating to different data. This other problem was not resolved using eSQL - I couldn't get it to work (my inadequacy, I'm sure...), so I went for a native FM solution, which is OK, albeit with the drawbacks of needing additional relationships and summary fields. Beginning to think that's the way to go here.
How about creating a aggregated INT table with all the info in there with a nite schedule. Past attendances won't change, so it's easy to create a table with totals from past months or week or even day. If you need up to date info you can use a UNION operator to do a construct like this.
SELECT program_title, tot_sessions, tot_attendances FROM INT_Aggregated WHERE program+key is 123
SELECT .... put the join query here from active tables for the current day or week i.e. . Just make sure the order, number of columns and type is the same.
Thanks for this suggestion. You're absolutely right, the data won't often change, so this fits well with my strategy of avoiding doing calculations 'on the fly' where it can be avoided.
Thanks again (and I'm sorry for the late reply - I've ben away),