Skip to main content

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:

 

SELECT COUNT(DISTINCT clientid) FROM ClientVisits
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.....

Terms & ConditionsAccessibility statement