If statement in query

[Usaskater]

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

 

[Joe4]

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]

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]

Thanks for the advice.

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

Best regards!

 

[Usaskater]

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-comfficeffice" /><o></o>
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]

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]

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.