Access 2007 and MS MapPoint

[Billiam]

I have an Access 2007 DB which tracks Teachers address and qualifications to
teach 4 courses: N, R, H, W. ALL teachers are "N" qualified. Others can teach
any other combination of theses qualifications, which gives 8 unique sets:

1. N
2. NR
3. NRH
4. NRHW
5. NH
6. NHW
7. NW
8. NRH

In the Access table, each qualification is a title, and in each Teacher
record is YES or NO if they are qualified to teach that qualification. ( I
know this is not an ideal setup, but this is what I have inherited, and I do
not have time to redo the setup)

What I want to know is what would be the reccomended way to convert these
unique sets automatically to a single number which I would export to excel to
import into MapPoint. The reason i want a single number for each
qualification set is so I can assign a unique PushPin based on the teachers
qualifications and address.

Any help would be really appreciated!!!
Thanks,
Billiam

 

[John Spencer]

Assuming that you have 4 fields that are yes/no fields and they are
named N, R, H, and W. You can use

Abs(N*1 + R*2 + H*4 + W*8)

that will return
1 for N
2 for R
3 for NR
4 for H
5 for NH
6 for RH
7 for NRH
8 for W
9 for NW
10 for RHW
11 for NHW
12 for HW
13 for NHW
14 for RHW
15 for NRHW

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Billiam]

Hi Steve,

I will do this for all new Teachers, but I have 500 more to update, and so I
was hoping that it could be done "automatically" as a part of an export to
Excel for import into MapPoint. If I was to try an update query, how would I
do that for each of the unique situations?

 

[Billiam]

Hi John,

This looks like what I am after, however, there are only 8 possible
qualification sets as ALL Teachers are N, and so MUST ALWAYS include N in
their qualification set. Can this still be done using your expression
example? Also, how do I convert the yes/no under each heading to the
expression below?
Thanks for your help!
Billiam

 

[Billiam]

And sorry., I noticed my 8th case should say NRW not NRH.
Billiam

 

[Billiam]

Hi John,

If I remove N from your expression then it will work., as each case would
evaluate to a unique number. I believe that you are using -1=true or Yes and
0 = false or No...and you are using ABS to get the absolute value (make it
positive regardless of whether it is negative or not. if this is right, does
the expression automatically "convert the YES/NO to the appropriate number?

If I understand you correctly then:

ABS(R*2 + H *3 + W*4)

which would yield:
1. N evalutaes to zero
2. NR evaluates to 2
3. NRH evaluates to 5
4. NRHW evaluates to 9
5. NH evaluates to 3
6. NRW evaluates to 7
7. NW evaluates to 4
8. NRW evaluates to 6

Is this correct? I am assuming I would do this as a query for export to
Excel...Could I program a button to run this query/export and save the file
as a specific name and date (i.e Excel Export May 21 2009.xls). Also, i need
a way to remind the DB User to run this export to Excel whenever someones
qualifications change...how would i do that?
Thank you for your help...the formula is brilliant! Hope i am following you
correctly!
Thanks very much for your help,
Billiam

 

[Billiam]

Case 6 should say NHW evaluates to 7 (NOT NRW=7)

 

[John Spencer MVP]

Are your fields boolean fields (that is Yes/No fields)? If so, the actual
stored value is 0 (for No) and -1 for Yes.

So you should be able to use
Abs(R*1 + H*2 + W*4)

This assumes that N is ALWAYS checked.

The results should be as follows.
0 = N (only)
1 = NR
2 = NH
3 = NRH
4 = NW
5 = NRW
6 = NHW
7 = NRHW

I suggest you try it and see if it works. If not, post back with your
problems. If there are cases where N is not checked, you could use an
expression like

IIF(N=False,0, Abs(R*1 + H*2 + W*4)+1)
Then
0 = nothing checked
add 1 to all the other values in the above.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Billiam]

Hi john,

It worked like a dream! Thanks so much for your help and great idea...it is
working perfectly!