I'm helping with a local food pantry and shifting their data mgmt from google sheets to airtable. They register clients in a 1-time data entry task, then each time that client visits, they record that visit. So I have a Clients table and ClientVisits table. One client can obviously visit many times. Sometimes twice in a month.
I am feeding this data into interfaces for the users.
Each month the food pantry needs to run a report for:
Number of visits
Number of unique clients
Some other demographic info
Number of visits is quite easy of course. How do I get the number of unique clients counted? In MySQL or SQL Server this is what I would want to do:
SELECTCOUNT(DISTINCT clientid)FROMClientVisits WHERE VisitDate >= '12/1/2022' AND VisitDate <= '12/31/2022';
Hm, if you're able to use a script for this I'd recommend you just do that
If not, the only way I can think of to do this involves linking each of your visits to another record that represents the month-year of the visit, and then doing a `COUNTA(ARRAYUNIQUE(values))` like so:
If the report that the food pantry needs to run is something that then needs to be typed into another spreadsheet - like for county or state reporting* - you could create a monthly reporting interface that pulls the numbers you need and the person can access what they need and put it where it needs to go. You can create a filter based on start/end date of the month and use the number widget's record summary to show the unique visits.
*all of whom seem to have their own idiosyncratic Excel spreadsheet formats.....