If statement in query

Usaskater

New Member
Joined
May 7, 2010
Messages
33
I have one field that counts the number of a particular item. It is working great but a couple of the items have 6 in them and should be counted as one. For example:
Actual (current outcome right now in the query)
Field name=item
Kleenex 400
Cigarettes 50
Coffee maker 1
I wanted to create a If statement in the query if possible to say : If item is Kleenex divide by 200 and if Cigarettes divide by 25 and if anything else without stated if statement then leave it as is.
What I want it to show is:
Kleenex 2
Cigarettes 2
Coffee maker 1
Thanks in advance
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,701
Office Version
  1. 365
Platform
  1. Windows
An IF statment would work fine if you only have a few products where you need to do this division by, but if you have a lot, it won't be feasible to use an IF statement.

A better solution may be to create another table that lists how many items are in one box/package.

Then link these two tables together in a query (on the product name), and divide the two numbers.
 

Phildaburn

Board Regular
Joined
Feb 4, 2011
Messages
146
Actual: iif([item]="Kleenex",[item_quatity_field_name]/200,iif([item]="Cigarettes",[item_quantity_field_name]/50,[item_quantity_field_name]))
Joe's solution is a better one, though, but this is what you were asking for.

Phil...
 

Usaskater

New Member
Joined
May 7, 2010
Messages
33
Thanks for the advice.

Sorry for the delay. I am stuck in a meeting.

Best regards!
 

Usaskater

New Member
Joined
May 7, 2010
Messages
33

ADVERTISEMENT

My actual field name is "Kit Type" does the space cause problems. The query is a group by query with "kit type" with count. Can I place this in the query?
iif([item]="Kleenex",[item_quatity_field_name]/200,iif([item]="Cigarettes",[item_quantity_field_name]/50,[item_quantity_field_name]))

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
Thanks


<TABLE border=0 cellSpacing=0 cellPadding=6 width="100%"><TBODY><TR><TD style="BORDER-BOTTOM: 1px inset; BORDER-LEFT: 1px inset; BORDER-TOP: 1px inset; BORDER-RIGHT: 1px inset" class=alt2>Actual: iif([item]="Kleenex",[item_quatity_field_name]/200,iif([item]="Cigarettes",[item_quantity_field_name]/50,[item_quantity_field_name])) </TD></TR></TBODY></TABLE>
 

Phildaburn

Board Regular
Joined
Feb 4, 2011
Messages
146
Spaces in field names can cause problems. Using an underscore is better than a space and still gives you a division between words. So, "Kit_Type" or even "KitType" is better than "Kit Type". You can place it in your query within the square brackets though. Also, I noticed that what I wrote for cigarettes should have divided them by 25 rather than 50. Sorry about that.

Phil...
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,701
Office Version
  1. 365
Platform
  1. Windows
Spaces are not a problem if you use square brackets around the field name, though general programming practice is to avoid using spaces in object and field names (its just cleaner that way).

Also note that if one of your queries is an Aggregate Query, your field name may not be correct (if you are doing any Aggregate function on any field, the field name may be something like "SumOf..." or "CountOf...").

The best thing to do that would solve all these issues with field names is rather than copying the formula that is written and trying to edit it, try building it through the Expression Builder, selecting the fields from the appropriate sources. The Expression Builder will make sure all the field names are denoted properly.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,828
Messages
5,627,135
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top