sccardais

Relationship Question based on dates

Discussion created by sccardais on Oct 21, 2016
Latest reply on Oct 21, 2016 by sccardais

I am trying to get a count of records in a related table based on date fields but having trouble defining a relationship that will do what I want.

 

PERIODS: is used to create Rolling 12 Month reports. Each record is a 12 month period defined by two fields:

 

  • PERIODS::DateStart
  • PERIODS::DateEnd.

 

ORGS: each records holds customer information. Two fields in ORGS should give me what I want;

 

  • ORGS::Date Ordered
  • ORGS::DateCanceled

 

I want two new fields in PERIODS to show the client count at the beginning and end of each period.

  • PERIODS::ClientsAtStart
  • PERIODS::ClientsAtEnd


The calculation for PERIODS::ClientsAtStart seems simple. ORGS::DateOrdered < PERIODS::DateStart AND ORGS::DateCanceled is NOT < PERIODS::DateStart.

 

It's the "NOT"  part of the relationship that I can't work out.

 

Can this be done with a Relationship?

Outcomes