473,320 Members | 1,823 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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?
Nov 10 '19 #1
2 1603
NeoPa
32,556 Expert Mod 16PB
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.
Nov 10 '19 #2
ADezii
8,834 Expert 8TB
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, 47 views)
Nov 11 '19 #3

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

Similar topics

1
by: Graeme Longman | last post by:
Hi everyone, I was wondering if anyone has written some Python code which uses a start date and end date and a given interval (day, month or year) and outputs all the time periods for that range...
2
by: TS | last post by:
Does anyone have any experience creating an ODBC Connection to multiple Access databases? I need to be able to connect several databases, rather than linking to the tables. My only experience...
7
by: Joe Wasik | last post by:
Hello, Currently I used Biomobility's DataOnTheRun for the Pocket PC. It's a little slow, but it does exactly what I need -- and it does it well. Unfortunately, now I need to have the same...
4
by: Ivan | last post by:
I will be willing to help, with access databases, in same order request is received. The web site is at www.jaxdatasolutions.com. This is for a limited time only.
7
by: bill.brennum | last post by:
Hi, Have a number of Access Databases that I inherited and want to zip a few of them. My concern is that other active Microsoft Applications may be linking to the database or its tables. Is...
9
by: =?Utf-8?B?RnJpdHo=?= | last post by:
Hi all, I am having the following problems in getting Microsoft Visual Studio 2005 Professional to link to an Access .mdb database. Please help me to sort this out. Problem 1: ======= The...
14
by: --CELKO-- | last post by:
Can I get a little help? Given a very simple table of events, with their durations, I want to consolidate time periods for an event that (1) touch each other or (2) overlap each other. CREATE...
6
by: GregG | last post by:
Greetings, I've inherited a project which requires the use of multiple Access databases, each containing a dozen or so tables each. I need to perform queries which included relations and results...
5
by: hugobotha | last post by:
Hi all, Help will be much apreciated here. How can I query two different access databases that has same table and fields. This is how i access the one database but I want to be able to do it...
13
by: Charlotte | last post by:
Hi, i've googeled to find a asp-script that can compare all the records in two different access databases the mdb's have exactly the same tables what i want is that (the output) all the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.