Counting manually entered dates in date range

Hello! Im totally newbie at Airtable and databases. Please help me to find out couple things. I am working on base for tracking crossfit gym membership.

Each client purchase membership for 30—40 days. Every membership has {Start Date} and {End Date}. End Date field is calculated with formula by adding days to {Start Date}. Сlient shows up during membership period (once a day). Every visit fills up with form to Visits table.

I have three tables:

  1. Clients — {Name}, {Email}, {Phone} etc
  2. Memberships — {MembershipID}, {Customer} (linked to Clients), {Type}, {Start Date}, {End Date}, {Ammount} etc
  3. Visits — {Date of visit}, {Visitors} (Multiple links to Clients)

Is there a way to take {Date of visit} and compare it with date range of active membership? Take all {Date of visit} and count how many of them lays between {Start Date} and {End Date} for every {MembershipID}?

Show me the way please or maybe I look at this completely wrong.

Hi There, the solution below may be able to help, I have modified the visits table by adding the column membershpID to connect it to the membership table. I have then used rollup to bring in the start and end dates. The last colum calculates if the date of visit is in range. If you want you can now add a column to the membership tableusing the Rollup feature to bring back the sum of all visits in range from the “Visits” table. Hope this helps.
Victor



This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.