CODEWITHSUNDEEP
ms-access
QtheNovice
QtheNovice

Reputation: 95

Access table validation using data from other tables/queries

I am trying to put together a database in Access 2003, and I have a field in one table that I would like to validate based on the data in another related table.

For instance, I have a table of Categories and their minimum Goals. I am creating another table to hold the actual Goal for each Category that I will set independently each month. So I need a validation rule to warn me if I do not meet the minimum Goal each month. Can I do this and how?

Upvotes: 1

Views: 10104

Answers (2)

HansUp
HansUp

Reputation: 97101

You can use a check constraint to enforce the requirement that the current category goal be greater than or equal to a minimum goal in a reference table.

In this example, the current goal values are stored in a table named current_goals, which includes 2 fields: category_id; and goal.

The reference table is named min_goals, and includes 2 fields: category_id; and goal_floor.

ALTER TABLE current_goals
ADD CONSTRAINT valid_goal
CHECK (
    goal >= (
            SELECT goal_floor
            FROM min_goals
            WHERE min_goals.category_id = current_goals.category_id
            )
      );

You must execute the DDL statement under ADO; DAO can't handle DDL which contains check constraints. So you load the statement text in a string variable and execute it from CurrentProject.Connection (which is an ADO object):

CurrentProject.Connection.Execute strDdl

Upvotes: 2

Matthew Dally
Matthew Dally

Reputation: 412

It depends on when and where you would like the validation completed. In Access 2003 it is not possible to have validation completed in the table like you can in SQL Server.

A simple solution would be to use the DLookup function to lookup the Goal value from the Goal table and then compare that value to the minimum goal in the Category table.

If CategoryGoalValue == DLookup('MinGoal', 'Goal', 'Goal = ''' & CategoryName & '''') Then    
  'Minimum Goal Met    
Else    
  'Warning - Minimum Goal Not Met    
End If

Please note though that the DLookup function is memory intensive and on a large amount of records (i.e. if used within a query), the time taken to complete the query is significant and noticeable.

Upvotes: 0

Related Questions