Validation Rule Involving Sum & Group By?

[jgoodman00]

I have a table which is going to store information about species for a particular area, & relative abundance.

The fields are:

AreaID, SpeciesID, RelAbundance.

For any given AreaID I need to create a validation rule to ensure the Sum(RelAbundance) <= 100

Any ideas?

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[PaulBricker]

Create your Totals query that does the summations and GroupsBy the AreaID and SpeciesID. Then create a Select query that adds your Table and the Totals query. Join the AreaID and SpeciesID fields. Add the fields you need and then use this query as the Record Source for a Form. You can then use the Validation Rule property on the SumOfRelAbundance control to test for values <= 100.

Paul

 

[jgoodman00]

Unfortunately I cannot use a query because the program which will be inserting the data is now aware of queries (ArcGIS). I therefore need to validate at the table level.

I think it might support a vba function, although I am not sure how to handle the returned value at this stage. I will look into it...

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[PaulBricker]

Well, you could create a separate table that has the three fields, then create your totals query, then create an update query that would update the values from your Totals query to the fields in your Table. That might work.

Paul