I have an Access table called Engagement with four fields:

Emp_id, Year, Week, Act_id

It records when an employee is/was engaged in an activity. Each field is Number and each makes up part of the composite primary key. The semantics of the application was that each entry (each Emp_id-Year-Week-Act_id combination) must be unique. That is, while an employee will generally have different year-weeks and different activities, an employee can sometimes engage in the same activity on different year-weeks or even engage in different activities in the same year-week. Nulls were not allowed. Everything worked.

Now I need to expand/modify the semantics to allow unknown – or more aptly, not disclosed – Year-Week values associated with any given employee-activity. Of course, attempting to enter a row with empty Year-Week results in "Index or primary key cannot contain a Null value". So I need a change to the table design.

One thing I tried was to convert the primary key index into a non-primary index by turning Primary off (and leaving Unique on) in the Indexes window. This correctly prevents duplicate records where Year-Week values are non-empty -- but it allows duplicate records where Year-Week are empty.

For example, using the above non-primary, unique index, the following data are allowed:

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Same activity,
     7  2015    22      31  } different dates.
     7  2015    33      32
     7  2015    40      33  } Same dates,
     7  2015    40      34  } different activities.
     7  2016     2      36
     7                  38  } Different activities,
     7                  39  } undisclosed dates.

And none of the following additions are subsequently allowed:

Emp_id  Year  Week  Act_id
     7  2014    12      31  } Both records are 
     7  2015    33      32  } duplicates of above.

So far so good (behavior matches requirements). However, both the following additions are subsequently allowed, but should not be:

Emp_id  Year  Week  Act_id
     7                  38  } Both records are
     7                  39  } duplicates of above.

Why is that?

What is a good way around this problem of, on the one hand, allowing something equivalent to null for Year and Week and, on the other hand, constraining each Emp_id-Year-Week-Act_id combination to be unique?

I can think of two other (untried) solutions:

  1. Choose an equivalent-to-null numeric value, such as zero or -1 and somehow explain this to users.
  2. Convert the Year-Week field types from Number to Text and simply use "" (empty string) for null.

In your experience, what is a good solution in this kind of situation?

I'm aware of What's wrong with nullable columns in composite primary keys? and NULL value in multi-column primary key, which explain certain things but don't provide a solution.

  • 1
    You say "This correctly prevents duplicate records where Year-Week values are non-empty -- but it allows duplicate records where Year-Week are empty." What is the desired behaviour? Do you want to forbid two rows with same EmployeeID and same ActivityID and both Year and Week null? And why? You say in the beginning that you want to allow not disclosed Year-Week values. Which of the two is correct? The requirements are not clear. – ypercubeᵀᴹ Jun 15 '16 at 23:05
  • 2
    And how will you store the fact that employee=7 did activity=38 in two different (but unknown) year-weeks? – ypercubeᵀᴹ Jun 16 '16 at 0:06
  • @ypercubeᵀᴹ - Without going into details we have fairly good reason to accept that such a situation is not allowed (won't happen). Well spotted though. – Martin F Jun 16 '16 at 14:20
  • I'd recommend adding an autonumber field for a primary key (and enforcing your additional unique composite key). Otherwise your DB will experience more issues than just this one. If you're using the Access GUI, what are the buttons/wizards that you used to create the UNIQUE index? If using Jet/SQL, what is your full statement? – mpag Jun 16 '16 at 22:17
  • It looks like you wouldn't want a WITH clause by syntax at this link as you neither want to ignore nulls, nor enforce no nulls. So, CREATE UNIQUE INDEX uidx_engagement ON Engagement (Emp_id ASC, Year ASC, Week ASC, Act_id ASC) – mpag Jun 16 '16 at 22:25

The strongest advice I can give is to keep the primary key immutable unless it is absolutely unfeasible for your use-case. You appear to be describing 2 different datasets.

  1. User-Actions taken during a given Period ( defined by the composite key of Year-Week )
  2. User-Actions taken during an unknown Period

My initial impression is that the unknown period must needs be a different dataset. Surely if you don't know when an action has taken place you also do not yet know enough to move it from its staging environment to the table where User-Action-Period is the unique identifier. I don't know MS Access well enough to comment on the specifics of null keying in that environment, but I strongly recommend defining your data model for yourself in such a way that you know if you need to include the "equivalent-to-null" keyed records on the same table.

From your description, it seems that the problem occurs when attempting to insert multiple instances of a User-Action where the Period occurred is not known. Once again, if you do not know enough to uniquely identify the committed time of the User-Action, it is appropriate to stage the metrics you are recording about the same User-Action elsewhere until you can appropriately identify the relationship this User-Action has to the other similar User-Actions that occurred at another key-able time Period.

I see two paths forward:

If only one User-Action with "unknown" time-key is permissible

Example: The action is "pending" or "to-be-completed". The user will always be submitting data for a future / not-completed period.

Solution: Pick your favorite method of keying the time period as "equivalent-to-null" and make the key columns not-nullable. You may continue to store these datasets in the same table

Gotcha: If the user submits an action with a committed period while an unknown period exists for the same User-Action, you may run into a situation where data is committed where the period is known but other data already committed for an unknown period ( which happens to be the same one ) is left in the "unknown period" state and lost for that User-Action-Time record and the user doesn't immediately know why.

Multiple User-Actions with "unknown" time-keys are permissible

Example: Users are able to uniquely identify the same action type with a different identifier for an unknown Period.

Solution: You must define another way to key the data ( where the time the action occurs is a metric of the other multi-keyed record ). Allow the users to submit data to this separately stored dataset and merge it into your User-Action-Time period dataset using appropriate logic. It's more work, but there's no way around it if the datasets are bona-fide separate.

  • I appreciate your input. You have focused on "unknown" period over "non-disclosed" period for good reason. However, i have changed the emphasis in the Q to "non-disclosed". Unknown cases can simply be handled via approximate values and such values can easily be edited later. The reason for not-disclosed is actually to allow older employees not to give clues about their age. ... "I have a degree, but (in this DB) i'm not saying when." – Martin F Jun 16 '16 at 18:20
  • I don't see a problem with allowing the primary (or other) key being mutable, certainly not from a semantic PoV. Perhaps you're thinking of performance? – Martin F Jun 16 '16 at 18:23
  • I think splitting the table into two -- to match two slightly different semantics -- would require more work than is warranted. – Martin F Jun 16 '16 at 18:27
  • Including time period in the primary key implies the same user-action can be completed multiple times. 1) If this is not permissible for your model, then you should remove it from the PK. 2) If it is permissible then you need to decide whether or not you want to allow manipulation of multiple same user-actions concurrently. If yes, then you need to identify them some other way. If no, then you can use the "equivalent to null" solution. It's not a performance issue ( at least in the context of your OP ). It's a decision on at-what-grain you are managing the data. Should time BE part of the PK? – Peter Vandivier Jun 16 '16 at 18:29

You can use a default date of something way out of bounds. e.g. in the table definition YEAR INTEGER DEFAULT 1776, WEEK INTEGER DEFAULT 99

That way you know that everything gets entered with some date. It will continue to not allow you to enter in duplicate data.

In addition, you can create an exception report based off these values kicked off to someone letting them know that they need to update this date to the correct one.

This solution will only work in Access 2010+ (e.g. 2013) and only if the file is saved as an accdb (this solution doesn't work for MDBs, even in Access 2010). You may also have to enable macros on the saved document for the checks to actually...you know...work.

Set up the unique index as before, keeping in mind that it's won't actually be unique if you have any null values until the rest of this solution is put into action. SQL code for this would be

CREATE UNIQUE INDEX uidx_engagement
  ON Engagement (Emp_id ASC,  Act_id ASC, Year ASC, Week ASC)

In datasheet view of the table, click on Table under Table Tools in the upper ribbon. Click Before Change in Before Events section of the ribbon.

the first action should be

SetLocalVar
Name    vm
Expression     = ValidateMe()

The second action should be

If    Not [vm]   Then

This should create a If..End If block

In the middle of the If block, create another statement. I believe the actual error number doesn't matter.

RaiseError
Error Number   1
Error Description   Duplicate Record Entered

Then save and close the Before Change macro call.

Now you'll need to create the VBA code. Click on the Database Tools ribbon. In the Macro section, click Visual Basic

In the upper-left panel that opens, you should see a file tree thing. One of these objects should be an entry for your database. Right click on the name, and select Insert->Module. Then in the main window that opens, paste the following.

Option Compare Database

Public Function ValidateMe() As Boolean
'idea from http://www.utteraccess.com/forum/lofiversion/index.php/t1965349.html'
    Dim tabl As Object
    Dim my As Object
    Dim tablName As String
    Dim str As String
    Set tabl = Application.CurrentData.AllTables(Application.CurrentObjectName)
    Set my = Application.Screen.ActiveDatasheet.Controls
    tablName = tabl.Name
    str = "Emp_id=" & my!Emp_id & " AND Act_id=" & my!Act_id & " AND " & IIf(IsNull(my!Year), "ISNULL(Year)", "Year=" & my!Year) & " AND " & IIf(IsNull(my!Week), "ISNULL(Week)", "Week=" & my!Week) 'need "'"s if non-numeric data types'
    If DCount("[Emp_id]", tabl.Name, str) > 0 Then ValidateMe = False Else ValidateMe = True
End Function

Save this module and the VBA code in general. I named my module "Validator", but it shouldn't matter what you call it...you may have to also compile the module under the Debug menu.

See edit history for this thread for previous attempt at a solution. Bah.

  • 1
    As a side issue, i don't see why adding a separate primary key is useful; it will never be referenced elsewhere. – Martin F Jun 28 '16 at 20:53
  • that's unfortunate. I apologize for the red herring. – mpag Jun 29 '16 at 21:57
  • my impression was that without a PK, the table is stored as a heap and is not index optimized, even if you later create a unique index for it. However, I fully acknowledge that I may be spouting misinformation. – mpag Jun 29 '16 at 22:07
  • @MartinF please look at edited solution. good times. – mpag Jul 1 '16 at 19:21
  • rather than using tablName (or tabl and tabl.Name) you could ditch those two variables and just use Application.CurrentObjectName. But they were handy for debugging. – mpag Jul 1 '16 at 19:28
up vote 1 down vote accepted

The first part of the solution was – as I said in the question – to convert the primary key index into a non-primary index by turning Primary off (and leaving Unique on) in the Indexes window.

An alternative means for creating such an index, as suggested by @mpag, is via SQL:

CREATE UNIQUE INDEX uidx_engagement ON Engagement (Emp_id, Year, Week, Act_id)

The second part of the solution was to notice that I was already using a parameterized SQL query, get_engagement, in the code for my forms where the user enters or deletes engagement data:

SELECT * FROM Engagement WHERE
Emp_id = e AND Act_id = a AND Year = y AND Week = w

And then modify it so that it could handle the null cases:

SELECT * FROM Engagement WHERE
Emp_id = e AND Act_id = a
AND IIF (IsNull(y), IsNull(Year), Year = y)
AND IIF (IsNull(w), IsNull(Weak), Weak = w)

It was @mpag's idea for validation code that got me thinking on the right track.

You could do a lot of stuff but integer and text do not sort the same.

Text would break a search on week > 6.
In text 10, 11, 12 are not > 6.

I would use 0 for no date

Not that size is the big of a deal but you would use tinyint for week and smallint for year

one varchar is the size of a smallint

  • 1
    This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review – mustaccio Jun 15 '16 at 23:14
  • @mustaccio Break numeric sort is a big deal. Consider 3 and 13. OP acknowledged that in a comment he deleted. – paparazzo Jun 16 '16 at 1:55
  • 1
    if the field is text, as all week values will be 0-52 or 1-53, the OP can code the week values as 0x for single digit xs and sort will behave naturally. However, he would then have to have enforcement of all week values being two characters in the set [0-9], as otherwise a week value of Z would be accepted. – mpag Jun 16 '16 at 23:11
  • @mpag Yes OP could. This answer identifies they don't sort the same. – paparazzo Jun 16 '16 at 23:23

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.