Access 2010 Iif Query

[PWD]

Good morning I'm trying to put together an update query to populate an 'exclusion' field - "Not on Product Look-Up" and have put this in the Update To line:

IIf([SampleCombined]![excl_prod]="Yes","",IIf([SampleCombined]![no_lookup]="Yes","Yes",""))

What I intend is:-

If field excl_prod = "Yes" then don't put anything in Not on Product Look-Up. If it's not then if field no_lookup = "Yes" then put "Yes".

The database consists of over 112,000 rows and around 100 columns and this query takes a while to run and updates all the rows. Is there a better way to write this?

This is the first of several similar queries that will populate each row of data with only one 'exclusion' field per row. We had been doing this (successfully!) on a weekly basis with Excel but are now having to put together a 6 month summary and there's just too much data for it to cope.

Many thanks,
D€$

 

[SkipVought]

forum701?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Not sure if the syntax would be different for Access (probably) but this is how I would write it in SQL:

UPDATE SampleCombined
SET [Not on Product Look-Up] = 'Yes'

FROM SampleCombined a
INNER JOIN (SELECT UniqueRef FROM SampleCombined WHERE excl_prod != 'Yes' and no_lookup = 'Yes') b
on a.UniqueRef = b.UniqueRef

UniqueRef is whatever uniquely identifies your rows in the table

effectively, inner join from your table to a subset of itself where your test conditions are true and update only those rows

If your products can flip flop from one state tot he other you can always do:
UPDATE SampleCombined
SET [Not on Product Look-Up] = ''

1st and then run your update to set producst to 'Yes' as per above



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question