CODEWITHSUNDEEP
ms-accesstimerelationshiptable-relationships
SCary7470
SCary7470

Reputation: 1

Ensuring relationships stored in an MS Access linking/relationship table are not permitted to overlap with respect to time

I have an MS Access 2016 database with Person and Organization tables (PERS and ORG respectively) and a relationship table (PERS-ORG_RLTNP), which stores data about relationships between people and organizations, for example, relationships that show when particular people were members of an organization. The PERS and ORG tables have autonumber primary keys (Pers_ID and Org_ID) that are migrated as foreign keys into PERS-ORG_RLTNP. PERS-ORG_RLTNP also has a relationship type field (Pers-Org_Rltnp_Typ) as well as Effective_From_Date and Effective_To_Date fields that show when each relationship was in effect.

I want the database to permit multiple relationships of the same type between the same person and organization, but I don't want those relationships to overlap. That is, I don't want the database to permit the Effective_From_Date of the second relationship (the one that starts later) to be during the time period between the Effective_From_Date and Effective_To_Date of the first relationship. For example, if Joe Smith is a member of organization XYZ beginning on 1 Jan 1019 and ending on 15 June 2019 and then becomes a member again, I don't want the database to allow the insertion of a record for that second relationship that begins (has an Effective_From_Date) during the time period between 1 Jan 1019 and 15 June 2019.

If there is a way using indexes and validation rules in MS Access that I can enforce this constraint, how do I do it?

Upvotes: 0

Views: 33

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49329

Unfortantly, such business rules can't be defined, or managed at the table level by use of indexing, rules, or relationships. You could start to adopt store procedures, but at the end of the day? Say if database could do this? Well, your user interface would really lack the ability to give feed back to the user.

As a result, it is rather simple matter to code this right into your data entry form(s). That way the user gets user friendly prompts, and messages.

To prevent collisions, the logic here is quite simple:

A collision occurs when:

RequestStartDate <= EndDate and RequestEndDate >= StartDate

The above is thus a rather simply query, but if any collision occurs, the above will return records..and you simply don't allow the booking. In other words, since we NEVER allow booking with a collision, then the above simply statement will work for us. 

dim strWhere                 as string 
dim dtRequeestStartDate      as date 
dim dtRequestEndDate         as date 

dtRequestStartDate = inputbox("Enter start Date") 
dtRequestEndDate = inputbox("Enter end date") 


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _ 
 " and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "#  >= StartDate" 


if dcount("*","tableBooking",strW­here) > 0 then 
   msgbox "sorry, you cant book 
...bla bla bla....

The above is just an example, and I am sure you would build a nice form that prompts the user for the booking dates or catch this during data entry.

However, what is nice here is that the simple conditions and logic outlined above does return ANY collisions or any overlap in that date range. This includes one inside, or both ends starting outside, or either of one end between the start/end gap.

Upvotes: 1

Related Questions