By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,152 Members | 2,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,152 IT Pros & Developers. It's quick & easy.

Ensuring that time periods in MS Access databases don't overlap

P: 1
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?
3 Weeks Ago #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,560
Hi SCary.

Welcome to Bytes.com.

Congratulations on expressing your question so fully & clearly. You may want to consider breaking up the flow of information into separate paragraphs as one long stream can appear daunting, and is usually the sign of a disorganised ramble. Not in your case thankfully, but separate paragraphs are still more comfortable to read so a good idea anyway.

I don't believe you can enforce it with validations rules as such. Their scope is limited to the current record. You can have Field-specific Validation Rules but, and not everyone knows about this, you can also have Record-specific ones (Open a table in Design View then open Properties from there - Alt-Enter or View | Properties.) that have access to the relationship between all the Fields and get applied when the record is saved.

However, if you make sure that records are only ever added via your designed interface you could add some code in a Form_BeforeUpdate() event procedure that checks for any overlap of the date periods (Time Interval Overlapping (MS Access 2003)) and sets Cancel to True if it finds one.
3 Weeks Ago #2

ADezii
Expert 5K+
P: 8,666
I created a Demo for you that will allow Duplicate Records in the PERS-ORG_RLTNP Table based on [Pers_ID], [Org_ID], [Pers-Org_Rltnp_Type], [Effective_From_Date] and [Effective_To_Date] as long as there is no Date Overlapping with existing Records in the Date Fields in PERS-ORG_RLTNP. I found it too difficult to explain, so I just created a simple Demo in the hopes that 'A picture is worth a thousand words'. The Code has minimally been tested and appears to work as intended but I'll leave that up to you to decide. Simply change the Value of the Constants in the Click() Event of the only Command Button and view the results.
Attached Files
File Type: zip Overlap.zip (24.3 KB, 3 views)
3 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.