IIf or Lookup function

[jyadayada]

I'm not quite sure how to go about this, I want a calculation field in my query but the amount to multiply by depends on a critera, without the criteria it would look like this:


[Sectional_Space_Available_CM] x X , X being 0, 1 or 2


but whether it is 0,1 or 2 depends on what the value in [Ranked_Group] is, for example:


Group One x2
Group Two x2
Group Three x0
Group Four x1
Group Five x1


I gues it's something like

[Sectional_Space_Available_CM] x ([Ranked_Group]lookup result)


I just don't know where to start with a multiplication by a lookup result rather than a field value

 

[Isskint]

Is [Ranked Group] on a separate table or in the same table as [Sectional_Space_Available_CM] ?
If on a separate table, you could use a Dlookup() to return the calculation value form another field.
If not then nested IIF() statements are the answer

 

[jyadayada]

[Ranked Group] is in one of the source tables and is also brought into the query that I'm building that contains [Sectional_Space_Available_CM] as a calculation field, I want this field [Sectional_Space_Used_CM] to be another calculation field in the same query, based on the lookp result of what to multiply by


If that makes any sense?

 

[Isskint]

Sort of making sense

OK so in the table you have [Ranked Group], do you have another field that stores the multiplier factor (EG [Ranked Group] = Group One [OtherField] = 2?

This would result in [Sectional_Space_Available_CM] * dlookup("[OtherField]","[SourceTable]","[Ranked Group]='" [Ranked Group] & "'")

I would suggest this is the better way as you can then add extra Groups and edit the multipliers if necessary.

If you do not have the multiplier factor stored in a field, you would use;

Iif([Ranked Group]="Group One",2,Iif([Ranked Group]="Group Two",2,Iif([Ranked Group]="Group Three",0,1)))

 

[Minty]

If the ranking table is joined you should be able to just refer to the field ?

 

[arnelgp]

you don't need to use DLookup if the table is already Joined in your query.
it is better not to use an Expression within another Expression:

Expr1: ([Space Available (cm)] / 6)*Switch([[Ranked_Group]="Group One",2,
[Ranked_Group]="Group Two", 2, [Ranked_Group]="Group Three", 0,
 [Ranked_Group]="Group Four", 1, [Ranked_Group]="Group Five", 1, True, 1)

 

[jyadayada]

I've got an IIF function running, but i'm starting to think that I can maybe build all my rules into one query, I'm currently creating 4 queries that i'll then have to merge into one output, As well as ranked group being a criteria there are a couple of others [Territory] and [Space_Type], across these 3 criteria the impacts of the rules are that one mix of outcomes seees the [Total_Space_Available] divided by either 6 or 10 to give {Group_Space_Available], and the subsequent multiply by value to generate [Group_Space_Used] what would be the best way to build these rules into one query?


I've attached an xls table showing the 3 existing fields, and depending on the mix of the contents of these, the desired output into the query calculation field

 

[plog]

That excel spreadsheet needs to become a table.

The first 3 columns remain as is.

[Group_Space_Available_CM] name changes to [Total_Space_Available_Divisor] and the value in the field is just 6 or 10--the numerator goes away.

[Group_Space_Used_CM] name changes to [Group_Space_Available_CM_Multiplier] and the value in the field is the number after the multiplication sign.

If the first 3 rows of this table are themselves calculated or all from different tables, you build a sub-query to get them all into one datasource.

Then, in your query you bring that new table made from the spreadsheet in, link the first 3 rows appropriately and use the Divisor/Multiplier fields to calculate the values you need.

 

[jyadayada]

I follow with making a reference table with the divisors and multipliers in, I got lost at


"If the first 3 rows of this table are themselves calculated or all from different tables, you build a sub-query to get them all into one datasource.

Then, in your query you bring that new table made from the spreadsheet in, link the first 3 rows appropriately and use the Divisor/Multiplier fields to calculate the values you need."


In my query, if I have the divisor and multiplier data in a table, in the query field for say [Group_Space_Used_CM] how do I write the expression that will reference the table matching the 3 criteria to get the correct multiplier and multiply it by [Group_Space_Used_CM]?

 

[plog]

You JOIN the spreadsheet table.

You have 3 fields in it that relate to other tables/data sources. In Design View of the Query builder you simply drag and drop those 3 fields to the fields that exist in other data sources. Then to reference them you simply use their names.

Perhaps you can post a screenshot of your query design view.

 

[jyadayada]

The three fields all come in from seperate tables, they only exist as three fields in the resulting queriy, the same query I want this [Group_Space_Used_CM] field in

 

[plog]

The three fields all come in from seperate tables...

If the first 3 rows of this table are themselves calculated or all from different tables, you build a sub-query to get them all into one datasource.

Build a subquery.