Newbie Advice on doing comparisons in Access (VBA or SQL) 1

[everest]

Hi,

I have a project that requires me to compare bunch of water chemistry data with certain limits. I might have 10s or 100s of data points for a certain chemical and I need to get a percentage of data points that exceed the limits.

For example, I might have five data points for mercury that read: 2, 5, 7, 9, 11. And let's say the limit for mercury is 10. I need access to tell me that 1 out of 5 (20%) of the data points exceeded the limit. It sounds pretty simple, but it would be nice to have a way of automating it for hundreds of different chemicals each having hundreds of data points.

I'm relatively new to using Access and my question is, which approach would be best to run this comparison, writing a VBA module or running a bunch of SQL queries? Basically, I need someone to point me in the right direction. This project isn't due for a few more months, so I think I would have enough time to learn how to do it.

I would be grateful if I could recommendations on any books, websites, or any advice to help. Thanks.

Everest

 

[RickSpr]

I can't recommend any books or websites that would specifically help you design solutions for problems like this. I think it's a matter of being familiar with all the functions available in Access. Any tutorial book on Access should help you develop that experience.

For this specific problem, it seems to me you'll probably want to use a so-called "domain aggregate" function. The DCount() function returns the number of rows that meet a criterion you provide as a parameter, and that have any non-null value in a column you also provide. For example, the number of rows (data points) which have a value greater than 10 in the Mercury column would be returned by this function call:
DCount(&quot;Mercury&quot;, &quot;<table name>&quot;, &quot;Mercury > 10&quot
The total number of rows which have any non-null value in the Mercury column would be:
DCount(&quot;Mercury&quot;, &quot;<table name>&quot
(Here you don't specify the optional criterion, so all rows in the table are considered.)
A simple division gives you the percentage.

The nice thing about DCount() and other domain aggregate functions is that they're fast, and they can be used in either SQL statements (including stored queries) or in VBA code.

Thus, the decision of whether to use queries or VBA code will be made on the basis of other considerations, such as whether you're creating an application with a predefined set of features or an ad hoc tool for researchers to use. In other words, do your users need an idiot-proof application, or a tool flexible enough to adapt to their needs of the moment? In the former case, you'll probably need some VBA code and you'll want to hide the inner workings of Access--but you might still choose to do the data analysis with queries run by the VBA code and displayed on forms. In the latter case, it might be better to expose the queries to the users directly, so they can use them in novel ways.

As a general rule, when developing an Access application, design the tables and maybe some queries first, then the user interface. Once that is done, the design of the application code will probably be obvious to you. In designing the user interface, consider the users' level of sophistication, any specific requirements they (or the project owner) have detailed, and how information is typically presented in Windows applications. If possible, involve the users in the interface design by showing them prototype forms and reports and asking for their feedback. (Make sure they understand, though, that the prototypes are a long way from the finished product. I've gotten in trouble with engineers who assumed I was nearly done when I showed them a few prototype forms. After all, it looks done, to them.) Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein