Primary Key & Preventing Overlapping Dates Validation

[RTB3001]

Hi everyone,

Hopefully someone can help me.

In Table 1 - I have a set of contracts that have a unique number. However, although the primary key is the contract number - these numbers can be duplicated as long as their start and end dates don't overlap. So my question is, how do I make the Primary key the contract number plus the start and end date? Is this possible?

In Table 2 - I have a list of orders that relate to Table 1. i.e. Each order has a contract it relates to. How do I make sure that in a query, the order number picks up the correct contract for the corresponding date.

For example...

Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details

Table 2
Field 1: Order Number
Field 2: Order Date
Field 3: Contract Number

My query would pick up Order Number, Order Date, Contract Number, Contract Details.

Thanks in advance for any help folks,

Ross.

 

[Joe4]

For the first part of your question, you can select multiple fields for your Primary Key. Simply hold down the Control Key as you pick the fields for the Primary Key.

For the second part, are there any rules pertaining to Contract Start/End Dates (i.e. beginning of month, end of month, duration, etc), or can they be just about anything?

 

[RTB3001]

Access stuff...

I know I can select multiple fields for the primary key, but this has a problem...

Here are 3 examples of how Table 1 could be constructed and the Table outline...

Table 1
Field 1: Contract Number
Field 2: Contract Start
Field 3: Contract End
Field 4: Contract Details

Records
Contract Number/Contract Start/Contract End/Contract Details


001/01-Apr-07/31-May-07/Items Cost £1
001/01-Jun-07/31-Jul-07/Items Cost £2
001/01-May-07/30-Jun-07/Items Cost £1

Now, if I select Contract Number/Contract Start/Contract End as the primary fields, the above would not create any conflicts. However, the third record overlaps the first two records date barriers - and I don't want that to be possible.

Any new record's start date MUST be after any existing record with the same Contract Number's End Date. Also, any new record's end date MUST be before any existing record with the same Contract Number's Start Date.

I hope I'm making sense...

In answer to the second part, the dates can be anything as demonstrated above.

I think what I'm looking for is validations in the fields. But I'm not entirely sure how to implement those.

Thanks again for any assistance.

 

[Joe4]

Wow, that is a tricky one. I am not sure how to accomplish that. I am guessing some VBA may have to be involved.

I'll send a call out to some friends to see if they might have some ideas...

 

[Andrew Fergus]

Hi

I don't believe you can do this at the table design level. As jm14 mentioned, it will require VBA code to test the imputs at the time of data entry, which would have to be via a form. For the controls to work you would have to force the data entry to occur via a form and not directly in the table. Given the constraint of not being able to do this at the table design level, is that the sort of solution you are seeking?

Andrew

 

[SydneyGeek]

Hi Ross,

I'd go one step further than the comments so far.

Although you *can* create a compound primary key, you may find it easier to create an Autonumber primary key and handle the data entry in the 3 fields by record-level validation in a form. It will take some code to accomplish it, but it's definitely doable.

When you select the corresponding contract for the next table, you can have a combo box with 4 fields -- the Primary Key and the 3 data fields.
Easy for the users to select the record they want, and you won't be storing as much data in the foreign key fields.

If you need more details, please post back.

Denis

 

[SydneyGeek]

Here is a sample of how you can do the validation in the form. It is based on the Autonumber primary key that I

mentioned before.

The form (called frmContacts) is based on tblContacts, and has these fields:

ContractID        Autonumber
ContractNumber    Text
ContractStart     Date/Time
ContractEnd       Date/Time
ContractDetails   Text (or Memo, if >255 chars required)

It also has an unbound field called txtDate, with the caption Barrier Date --
You can locate it wherever you like on the form. To stop people modifying it, set these properties:

Enabled    No
Locked     Yes

OK, now for the code. To place this on the form, go to the Form properties.
1. Select the Events tab.
2. In the blank line for On Current, double-click till you see Event Procedure.
Do the same for the following controls:
AfterUpdate (Control: ContractNumber)
BeforeUpdate (Control: ContractStart)
BeforeUpdate (Control: ContractEnd)
3. Click the Builder (...) button to go to the code window.
4. Select All (Ctrl+A) and replace it with the following:

Option Compare Database
Option Explicit

Private Sub ContractEnd_BeforeUpdate(Cancel As Integer)
    'validate: must be >ContractStart
    If ContractEnd < Me.ContractStart Then
        MsgBox "End date cannot be before Start date", vbExclamation
        Cancel = True
    End If
End Sub

Private Sub ContractNumber_AfterUpdate()
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim prm As DAO.Parameter
    Dim sSQL As String
    
    sSQL = "SELECT TOP 1 tblContracts.ContractEnd " _
        & "FROM tblContracts " _
        & "WHERE tblContracts.ContractNumber = [Forms]![frmContracts]![ContractNumber] " _
        & "ORDER BY tblContracts.ContractEnd DESC;"

    'grab the latest date for this contract.
    'if the contract does not yet exist, use 1 Jan 2006
    'place this date in txtDate
    Set dbs = CurrentDb()
    Set qdf = dbs.CreateQueryDef("", sSQL)
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset
    If Not rst.EOF Then 'records exist
        Me.txtDate = rst!ContractEnd
    Else
        Me.txtDate = DateSerial(2006, 1, 1)
    End If
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
End Sub

Private Sub ContractStart_BeforeUpdate(Cancel As Integer)
    'validate: must be >txtDate
    If ContractStart < Me.txtDate Then
        MsgBox "This date MUST be greater than the Barrier Date on this form", vbCritical
        Cancel = True
    End If
End Sub

Private Sub Form_Current()
    'move cursor to ContractNumber
    Me.ContractNumber.SetFocus
End Sub

What it does:
ContractNumber_AfterUpdate pushes the validation date to txtDate
ContractStart_BeforeUpdate checks that the entry is >txtDate
ContractEnd_BeforeUpdate checks that the entry is >ContractStart
Form_Current moves focus to ContractNumber

Note:
You will need to change names to suit, if your database has different field, table and form names.

Denis