Add a number to a field based of the number of occurences of specific text in another field

Question

I have a field that has several selections for trainings that can be input into my access database. I have an index set to not allow duplicates where the training title and date match, but there is one generic training that I'd like to allow to have duplicates. I'm thinking that if I can create another column with something like an auto number to the index then that might solve my problem. However I don't know how to do that.

What I'm thinking is that each time the training "Additional Training" is selected, this other column will detect that and add an auto number (or other unique identifier). It will not do this for any other entries, thereby allowing only that one training to be duplicated.

Any suggestions?

Answer 1

The above answer is probably a more elegant solution to what I asked but in the mean time, I was able to find a way to get the result I wanted.

I added a [short text] column to the table and included it in the key with my title and date columns. Now the key looks for duplication across three fields instead of two.

On the form that enters the data, I created a non-visible field that links to that column and adds a time stamp if the user selects the "generic training" and a "c" (for copy) if they select anything else. This has solved the problem that I was having. Here's the code I added to the form:

 Private Sub TrainingTitle_Change()
      If Me.TrainingTitle = 45 Then
           Me.Text50 = Now()
           Me.Text30.Visible = True
           Me.Label31.Visible = True

      Else
           Me.Text50 = "c"
           Me.Text30.Visible = False
           Me.Label31.Visible = False

    End If

 End Sub

The additional code you see makes a separate field visible or invisible based on the same criteria so that the user can specify the number of hours a generic training counts for.

Thanks for helping me think about this and come to a solution.

Answer 2

First of all, don't do a new AutoNumber field since that will give you the opposite of what you need... it would by definition generate unique values, thereby nullifying enforcement of uniqueness of the two principle fields (date and training).

If you have at least Access 2010 then I recommend using Data Macros to set a third column (as you suggest). One alternative would be to relax the actual table constraints (i.e. indexes) and then put all the validation in the entry form code. That can work sufficiently well if there are limited forms & only one database user at a time, and no rogue code that will contradict your rules, but I find that both cumbersome and contrary to the idea of using the database to enforce as much of the data rules as possible. Also, there is really no way for "this other column [to] detect" changes in other fields. An individual column has no such ability. A Data Macros can indeed inspect, validate and change values for inserted and updated rows, so perhaps that satisfies what you meant but it operates on a table and row basis.

Since the question supplied no formal database schema, here is a schema on which the example data macro is based:

Table [Training]: [ID] AutoNumber, Primary Key
                  [Title] Text, Required
                  [Multiple] Boolean, Default false

Table [Schedule]: [ID] AutoNumber, Primary Key
                  [TrainingDate] DateTime, Required
                  [TrainingID] Long, Required (FK to Training.ID)
                  [MultipleSeq] Integer, NOT Required, Default 0
                  Unique index on [TrainingDate], [TrainingID] and [MultipleSeq]

[MultipleSeq] is not required (Required: No) so that setting its field to null allows multiple records despite the uniqueness constraint on the index.

The following Before Change data macro updates the additional indexed column to either null or a static value, depending on the selected training course. This overall solution could also work with a single training field, where the data macro checks the training title instead of the [Training].[Multiple] column. The benefit of the schema I show is that you could have multiple such exceptions to the unique constraint and the name of the "generic training" could be updated without updating all rows (this is really just an effect of properly normalized tables).

Paste the following into the Before Change data macro of the Schedule table:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">
<DataMacro Event="BeforeChange">
  <Statements>
    <LookUpRecord>
      <Data>
        <Reference>Training</Reference>
        <WhereCondition>[Training].[ID]=[Schedule].[TrainingID]</WhereCondition>
      </Data>
      <Statements>
        <ConditionalBlock>
        <If><Condition>[Training].[Multiple]=True</Condition>
          <Statements>
            <Comment>The specified training allows multiple entries per date, so set  [MultipleSeq] to null to circumvent the uniqueness requirement.</Comment>
            <Action Name="SetField">
              <Argument Name="Field">Schedule.MultipleSeq</Argument>
              <Argument Name="Value">Null</Argument>
            </Action>
          </Statements>
        </If>
        <Else>
          <Statements>
            <Action Name="SetField">
              <Argument Name="Field">Schedule.MultipleSeq</Argument>
              <Argument Name="Value">0</Argument>
            </Action>
          </Statements>
        </Else>
        </ConditionalBlock>
      </Statements>
    </LookUpRecord>
  </Statements>
</DataMacro>
</DataMacros>