IIf or Lookup function (1 Viewer)

jyadayada · Dec 19, 2018

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 · Dec 19, 2018

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 · Dec 19, 2018

[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 · Dec 19, 2018

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 · Dec 19, 2018

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

arnelgp · Dec 19, 2018

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:

Code:

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 · Dec 19, 2018

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 · Dec 19, 2018

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 · Dec 19, 2018

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 · Dec 19, 2018

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 · Dec 19, 2018

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 · Dec 19, 2018

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.

IIf or Lookup function (1 Viewer)

jyadayada

Registered User.

Isskint

Slowly Developing

jyadayada

Registered User.

Isskint

Slowly Developing

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

jyadayada

Registered User.

Attachments

plog

Banishment Pending

jyadayada

Registered User.

plog

Banishment Pending

jyadayada

Registered User.

plog

Banishment Pending

Similar threads

Users who are viewing this thread