Expression too complex

[uapak90]

I want to run a query, rather an easy one but I'm a beginner at it so I am not sure how to actually condense all this information.
I have the following fields that I really care about:
Room/Location
Schedule
Pre
Post

I have the following Rooms
Compounding
Ointment
Liquid
AIrlock
Corridor
Gowning

I want to see the total samples of the day by saying If the room is compounding, and it is schedule A and the pre is yes and the post is yes then there should be 21 samples.
There will be a lot of different combination of the thing I said above, this was the code so far:

I know, too many ifs, but I had no other clue how to do it, and up to a certain point, it told me that it was too complex.. Whats another way I can reduce the information and still see the total number of samples each day:

SELECT [Rodac Plates (2008)].Date, [Rodac Plates (2008)].Schedule, [Rodac Plates (2008)].[Room/Location], (IIf([Room/Location]="Compounding" And ([Schedule]="A" Or [Schedule]="C") And [Pre]="Yes" And [Post]="Yes",21,(IIf([Room/Location]="Compounding" And ([Schedule]="B" Or [Schedule]="D") And [Pre]="Yes" And [Post]="Yes","20",(IIf([Room/Location]="Compounding" And ([Schedule]="B" Or [Schedule]="D") And [Pre]="Yes","14",(IIf([Room/Location]="Compounding" And ([Schedule]="A" Or [Schedule]="C") And [Pre]="Yes","15",(IIf([Room/Location]="Compounding" And ([Schedule]="B" Or [Schedule]="A" Or [Schedule]="C" Or [Schedule]="D") And [Post]="Yes","6",(IIf([Room/Location]="Liquid" And ([Schedule]="A" Or [Schedule]="B" Or [Schedule]="C" Or [Schedule]="D") And [Pre]="Yes" And [Post]="Yes","24",(IIf([Room/Location]="Liquid" And ([Schedule]="A" Or [Schedule]="B" Or [Schedule]="C" Or [Schedule]="D") And [Pre]="Yes","16",(IIf([Room/Location]="Liquid" And ([Schedule]="A" Or [Schedule]="B" Or [Schedule]="C" Or [Schedule]="D") And [Post]="Yes","8")))))))))))))))) AS [Total Number of Samples]
FROM [Rodac Plates (2008)];

 

[Remou]

First, create a table called say, Rules, with the following data:

[tt]
Room Schedule Pre Post Samples
Compounding A Yes Yes 21
Compounding C Yes Yes 21
Compounding B Yes Yes 20
Compounding D Yes Yes 20
Compounding A Yes No 15
Compounding C Yes No 15
Compounding B Yes No 14
Compounding D Yes No 14
Compounding A No Yes 6
Compounding C No Yes 6
Compounding B No Yes 6
Compounding D No Yes 6
Liquid A Yes Yes 24
Liquid C Yes Yes 24
Liquid B Yes Yes 24
Liquid D Yes Yes 24
Liquid A Yes No 16
Liquid C Yes No 16
Liquid B Yes No 16
Liquid D Yes No 16
Liquid A No Yes 8
Liquid C No Yes 8
Liquid B No Yes 8
Liquid D No Yes 8[/tt]

You will need to add suitable data for:
Ointment
Liquid
AIrlock
Corridor
Gowning

Next, join the table to the main table on all the relevant fields to get the number of samples.

 

[uapak90]

thanks so much
helped a lot
and will keep helping!