Validation - How to identify records that fail a rule

[Mark.T]

Background: I am an ICT teacher in a UK secondary school. I am on a
placement in a different school for a few weeks and they have an Access
issue that I have not had to deal with before.

Students are expected to create a basic database, import from a text
file and then add a (very simple) validation rule that some data will
fail, then correct the data.

All goes well until the validation.

Access correctly reports that 'n' records have failed but it does not
then show us the records that have failed. It seems obvious that the
user would want to see what records have failed but Access appears not
to do this.

Question: Can Access be persuaded to actually show the records it is
unhappy with as part of the validation check after a new rule is added?
The solution needs to be carried out wholly with the validation process
and not require any macros or VBA. IF this cannot be done, we can go
back to the examining board and get them to modify/clarify the
procedure requirements.

Notes:

1) I know how to go around the problem in a number of ways such as
creating a separate query to select records that fail the rule but this
is not what the students need to do.

2) I have exhausted my own modest Access knowledge and the online and
offline help before posting. I also quizzed Microsoft staff at the
recent BETT Show in London (British Education Training & Technology)
and all present said it could not be done.

Best Wishes

Mark Townsend

 

[Jeff Boyce]

Mark

If I were asked to identify which rows in table2 were not appended to
table1, I would use a query. Perhaps one of the other newsgroup readers is
aware of a way to have the append query itself disclose the rows that
fail...

Why do you consider a secondary query to be an unacceptable solution? What
other constraints are involved in your students completing the assignment?

 

[Mark.T]

Dear Jeff

The problem with using a query or even exporting to excel is twofold...

1) It is daft! Having told me that some records have failed the
validation, why not give me the option of seeing those records.

2) Main Reason: We have to stick very closely to the currciulum
specification we are given and the current wording could be interpreted
to preclude a query which would be a separate step.

Now that I can show what has been done to resolve the issue, I can
refer this back to the provider, explain that it cannot be done as
written and get them to move the goal posts.

Best Wishes

Mark Townsend

 

[Jeff Boyce]

Mark

I believe it could be done "as one step" ... depending on your definition!

There's no reason the additional query could not be embedded within a
procedure (i.e., one step).

Or is that splitting hairs...?

 

[Mark.T]

Dear Jeff

There's no reason the additional query could not be
embedded within a procedure (i.e., one step).

Can you be more specific?

This could not, for example, include the use of VBA or a macro as this
is beyond the skill of the students involved.

Or is that splitting hairs...?

Sadly, hair difurcation is the name of the game!

Best Wishes

Mark Townsend

 

[Jeff Boyce]

Mark

Actually, I was considering VBA specifically.

As close as I can come, using queries only, is my original idea, of writing
a second query that looks for "unmatched".

Remember, though, this is JOPO (just one person's opinion).

Good luck!

Jeff Boyce
<Office/Access MVP>