Problem counting using database function 1

[keysol]

Hi,

I'm using a database function to determine the number of occurences of a particular disease in a column list. The function works fine except for one hitch. The column list comprises "varations" of a disease. For example there might be
10 occurences of Abcessus
2 occurences of Abcessus diffus
5 occurences of Abcessus pulm.

If I write criteria for each variation, the function returns
17 for Abcessus
2 for Abcessus diffus and
5 for Abcessus pulm
How do I write criteria that will recognize each variation as being unique so that I may get the correct count?

P.S. the reason I failed to specify the database function:
BDNBVAL()

that I'm using is because my version of Excel is french and I don't know the english equivalent.

Thanks,

Gerr

 

[DaleWatson123321]

Hi keysol,

I've created a sample file which works.

The simple solution I used was to make the criteria for "Abcessus" unique, by adding 2 spaces... i.e. "Abcessus ". This is required in both the criteria and in the database.

Without the added spaces, the criteria is of course counting ALL the instances of "Abcessus " - i.e. with the 1 space.

I hope this helps. Please advise.

If you would like a copy of the file, simply email me, and I'll return the file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[keysol]

Hi Dale,

Your proposed solution would of course work but I had hoped to find a more "automated" way to go about this. You see my column listing of diseases is 4,000 entries long and there are many variations. Here is just a small sampling copied from those starting with "A".

Abcessus
Abcessus
Abcessus & Syphilis
Abcessus chronic
Abcessus fistul
Abcessus fistul
Abcessus gengiv.
Abcessus gengiv.
Abcessus gengival
Abcessus gengival
Abcessus in perin.
Abcessus in perin.
Abcessus in perin.
Abcessus pulm.
Abcessus pulm.
Abcessus pulm.

Wearing my rose-tinted glasses I had hoped that there existed some sort of "switch" or "qualifyer" that would force the function to evaluate the entry as a whole and not parse it to find strings.

Gerr

 

[DaleWatson123321]

Gerr,

Quick update... I have identified the proper syntax for the criteria, and am working on VBA code for extracting and summarizing the data - for all unique entries in the database.

If you can send me your email address, I will return the file. I should have it completed later today.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[keysol]

Hi Dale,

I don't know what to say! The best I was hoping for was being pointed in the right direction. Thank you very much for your generosity. Your code works superbly. In fact I was able to use it to generate additonal tables that required this type of manipulation. If there is one thing that you've proven, it's that the real power lies in the ability to program thereby exploiting the real potential of this software.

Regards,

Gerr