Using IIF statements within an update command

[preed]

Hi everyone,



First time poster, but I have been directed to this site by google several times over the past few months. I'm still learning Access, and am working on some reports that had been done by my predecessor. They were kind enough to leave detailed notes on how the reports were generated, but the steps involve running a lot of queries (that are very basic in and of themselves). We decided to change the data window for the report, so I'm going back and re-calculating for the past 5-years. Needless to say, I thought "there has to be a better way."

I'm looking at student placement into ESL classes. After separating out some of the information I was given, I have several different categories of placements. These are indicated by my "Maxscore" field. Students are placed in a pattern as follows:

0 = ESL 51A
1 = ESL 51B
2 = ESL 51C

The original steps were:
Is it a 0? Run this query to make it say "ESL 51A"
*change query to say*
Is it a 1? Run this query to make it say "ESL 51B"
*change query to say....*
Is it a 2? Run this query to make it say "ESL 51C"........

I wanted to save time, so I learned that MS Access has an analogue to the nested if statement, I thought I could use that to put in all three statements into 1 query.

UPDATE Placements_FA11_ECC INNER JOIN MaxScores_ESL_2011 ON Placements_FA11_ECC.StudentID = MaxScores_ESL_2011.StudentID 
SET Placements_FA11_ECC.ESLO = 
IIf([MaxScores_ESL_2011].[MaxScore]="2","ESL 51C",
IIf([MaxScores_ESL_2011].[MaxScore]="1","ESL 51B",
IIf([MaxScores_ESL_2011].[MaxScore]="0","ESL 51A","")));

That is the code I used. On the surface, it seemed to work. Some of the records updated appropriately. However, there are several cases where the student should have placed into one of those categories but were evaluated to be "false." I haven't noticed any commonality as to why this is happening. Some of the improper falses are 51A, some are 51B, and some are 51C.

Is there something that I'm missing? I keep checking for the "any" key, but I can't find it . And I have tried restarting Access as well (aka, "turning it off and on again"). Apologies for any no0bishness.

[JoeM]

What is the data type of your MaxScore?
If it is numeric and not text, you should not have double-quotes around your values, i.e.
IIf([MaxScores_ESL_2011].[MaxScore]=2,"ESL 51C",...

And, if it is numeric, you may have a rounding or floating arithmetic error in some cases, in which case you may need to use something like:
IIf(Round([MaxScores_ESL_2011].[MaxScore],0)=2,"ESL 51C",...

[preed]

Good questions JoeM -- It is text. There's a good reason for that, but I don't want to bore you too much. Essentially, student IDs sometimes have leading 0's which are important. Unfortunately they wind up being dropped off, which can lead to issues with linking. So I've gotten in the habit of saving things as text rather than numeric (unless it needs to be numeric).

In this case, 2 is what was removed from a clearance that the student was given of "LESLO2". The "LESLO" tells me that it was the oral level of ESL (as oppose to reading/writing). The number tells me what level of oral ESL.

[preed]

*Update*
I tried both ways suggested by JoeM -- I first changed the field to numeric and removed the quotes. Same issue. Next, I tried using the Round function in case there was some other issue with arithmetic. Same issue. Unfortunately neither worked.

IIf(Round([MaxScores_ESL_2011].[MaxScore],0)=2,"ESL 51C",
IIf(Round([MaxScores_ESL_2011].[MaxScore],0)=1,"ESL 51B",
IIf(Round([MaxScores_ESL_2011].[MaxScore],0)=0,"ESL 51A","")))

[June7]

If you want to provide sanitized database for analysis, follow instructions at bottom of my post.

[JoeM]

Yes, as June suggested, the best thing would probably be to provide a copy of your database so we can see what you are working with.

[preed]

Hi all,

Thanks for the offers of help. To best de-identify the table, I imported the tables and query into a new db (using external Data > MS Access). Then I created a random ID variable and updated that to both tables (so that the cases could still be linked -- given this is student data, it's important to de-identify). I didn't want to upload the entire db because that would be very large (lots of different types of assessment analyses calculated over the years).

The weird thing: I ran the query now (in the imported tables/query), and it seems to have worked. There were 150 students who were updated into a placement (which makes much more sense than 4).

Any ideas why this may have happened? I can still upload the new copy of the .db, but I don't think that would be helpful considering that the query seems to work now....

It seems like I could do this to get the right numbers with the other assessments and years, but I would like to know what I'm doing to confuse Access so much. I want to make sure I don't do it again in the future. As always, help is greatly appreciated.

[June7]

I suspect it has something to do with the link between the two tables but without analyzing the original arrangement, can't be more specific.

[preed]

One thing I did do differently: In order to make the random IDs stick, I made sure to make all of the ID fields not indexed. Previously, they were Indexed: "Yes, duplicates ok". Would this mix anything up?

[June7]

Yes, that could cause issue if there really were duplicate IDs. Indexing should not be the cause but duplicates could be.

[preed]

Yes - there are duplicates (students can be cleared to three different types of ESL classes: Oral, Reading, and Writing). So there could be between 0 and 3 records per ID.

So, would your recommendation would be to un-index the ID's?

[June7]

You can try but I doubt it will make a difference. Although I am still baffled by failure of the IIf. If MaxScore is synonymous with the class level, seems it should work.

[preed]

Yeah....that's strange. So when I removed the indexing, it worked. Does that mean I should mark this as solved? Haha.

[I've only been at this job 6 months, and most of my work has been doing very basic things in Access and Excel. Before this job, I had never used Access (other than accidentally opening it). I know there has to be better ways of doing things, so I definitely appreciate any and all help/advice from the web-community.]

[June7]

Apparently I was wrong about the indexing. I just read another thread that seemed to be the same sort of issue and indexing was at the root. I don't have to routinely do mass updates of data so this is new to me. I am not sure why the indexing makes a difference.

Solved without full understanding.

[preed]

*Because I like to tinker*

I was thinking that it would possibly be the duplicates that were messing things up. Like I mentioned, each case could have 3 scores (one that indicates Oral, one for Writing, and one for Reading). Under the assumption that it was the duplicates, I made my tables indexed again ("duplicates OK") and added a selection criteria to my query and tested that. It worked!

UPDATE Placements_FA11_ECC INNER JOIN MaxScores_ESL_2011 ON Placements_FA11_ECC.StudentID = MaxScores_ESL_2011.StudentID SET Placements_FA11_ECC.ESLO = 
IIf([MaxScores_ESL_2011].[MaxScore]="2","ESL 51C",
IIf([MaxScores_ESL_2011].[MaxScore]="1","ESL 51B",
IIf([MaxScores_ESL_2011].[MaxScore]="0","ESL 51A",Null)))
WHERE (((MaxScores_ESL_2011.Acronym)="LESLO"));

So June7, you were definitely correct that it is the duplicates that mess things up. If your query can make it so that it evaluates only one row of data (rather than apparently all rows with that ID) per ID, then it's ok to be indexed. If you can't identify each row uniquely (and the query reads duplicates), you may not get the correct answer without removing the index.

Having said that, I'm glad that even a Mod was (briefly) stumped by the issue I ran into. Thanks again!