Record Validation rule between a table entry and a query result?

M

Mercy

Hey all,
I am trying to create a record validation rule that will
make sure that the Quantity in Table A will equal the Sum
of the related quantities in Table B.

Let's say Table A contains: Students, and how many fruits
they have.
Table B contains: Students, type of fruit they have, and
how many of each fruit they have.

I want to make sure that the qty in A matches the sum of
the matching qtys in B.

I created a query to sum up B grouped by student names.
But I don't know how to write the syntax for the
validation rule. I'm not even sure if I can refer to a
query like that?

Any help would be greatly appreciated it!

Thanks,
Mercy
 
J

Jeff Boyce

Mercy

First, why? If you already have the underlying counts, why do you need to
store a sum? The typical approach in Access is to calculate the value
whenever you need it.

This has advantages: if any of the underlying values change (data entry
error correction, change in data, ...), your version is out of sync, but a
"calculate on the fly" approach still gives a correct value. Also,
relational design argues for not storing calculated values, and storage
costs (albeit very little these days!).

Second, I believe what you're looking for would be available via "triggers"
in SQL Server and other heavier duty databases, but, alas, not in Access.

If you still feel you must, consider handling this kind of
coordination/synchronization via forms. You could add code in an
AfterUpdate event on your details form that updates the calculated value in
your summary table.

Good luck!

Jeff Boyce
<Access MVP>
 
M

Mercy

Hey Jeff,

I think I wasn't very clear in stating my problem. I
need the validation rule to make sure that the data entry
was correct. The datas for the two tables are entered at
the same time in the same form.

I wanted the validation rule to double check that the
total qty stated in Table A agrees with the related qtys
in Table B.

.... Maybe my entire design is wrong... heh heh ... but I
wasn't checking the value just for the sake of checking
the value :p

And you're right. I was hoping to find a trigger... but
the closest thing I could find was the validation rule in
the table properties. Thanks for telling me that
triggers don't exist in Access. :p

Thanks for replying!
Mercy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top