Sum IIF query not working!

[Distinctive]

Hi,
I want to write a query in Access to calculate vendor cost for each project by using conditions.
There are two tables created in Access: Vendor_Rates and DB. Vendor_Rates has rates of vendors as per the product offered by them and DB is the table that has information about the work order. In the Vendor_Rates table there are three types of rates - RateW for number of words they have worked on, RateH for number of hours they have worked on and RateP for number of pages they have worked on. When any review work is done by vendors, they do not work on the complete volume and only partial volume is given to them, which is mentioned in the Out by Vendor field. Otherwise, the vendor has to work on the complete volume.
What I want to do is if the Task performed by the vendor is 'Review' then 1/2 of rates mentioned in Vendor_Rates table should be multiplied by the partial Volume sent to the vendor - entered in Out by vendor. If the Task performed is other than Review then the rates should be multiplied with Count, which is the total volume sent to them.

I have written the below query without the 1/2 of rates aspect as I am not able to know how to add this in the query. Please help correct the problem as Access is giving Syntax error (comma) error.



SELECT Vendor_Rates.RateW, DB.Product, DB.[Client Name], DB.[Project Name], DB.Unit, DB.[Out by Vendor], DB.[Delivery Date], DB.Status, DB.[Count], DB.Task, Sum(IIf(DB.[Task]='Review', Switch([DB].[Unit]='Word(s)', ([Vendor_Rates.RateW]*[DB].[Out by Vendor]), [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Out by Vendor]), [DB].[Unit]='Hour(s)',([Vendor_Rates.RateH]*[DB].[Out by Vendor], IIf(DB.[Task]<>'Review', Switch([DB].[Unit]='Word(s)',([Vendor_Rates.RateW]*[DB].[Count]), [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Count]), [DB].[Unit]='Hour(s)', ([Vendor_Rates.RateH]*[DB].[Count]))))))) AS VendorBilling
FROM DB INNER JOIN Vendor_Rates ON DB.Product = Vendor_Rates.Product
WHERE (((DB.[Delivery Date]) Between #9/16/2013# And #9/20/2013#))
GROUP BY Vendor_Rates.RateW, DB.Product, DB.[Client Name], DB.[Project Name], DB.Unit, DB.[Out by Vendor], DB.[Delivery Date], DB.Status, DB.[Count], DB.Task, Vendor_Rates.RateP, Vendor_Rates.RateH;

[June7]

That's a lot of fields in the grouping. Not sure you will get the aggregation at the level you want. The more fields, the more refined the summation.

Think missing a paren:

,([Vendor_Rates.RateH]*[DB].[Out by Vendor]),

Then think will be too many parens:

))))))) AS VendorBilling


Are you building this query with the Access query designer?


Consider building a report using Grouping & Sorting features with aggregate calcs in group sections footers.

[Distinctive]

Could you give some specific suggestions please? I am using Access after many years and finding it difficult to recall, but have to use this in a report.

I tried adding the ) as suggested but it is now giving "Wrong number of arguments" error.

[ssanfu]

You have a field name "Count". This is a reserved word in Access and shouldn't be used as an object name.

You have special characters in field names. Should only use letters, numbers and possibly the underscore.
You have open & close parenthesis in field names and spaces.

In the SUM(), you don't need the 2nd IIF() function.

Try this (I think I might have the parenthesis right:

SELECT Vendor_Rates.RateW, DB.Product, DB.[Client Name], DB.[Project Name], DB.Unit, DB.[Out by Vendor], DB.[Delivery Date], DB.Status, DB.[Count], DB.Task, Sum(IIf(DB.[Task]='Review', Switch([DB].[Unit]='Word(s)', ([Vendor_Rates.RateW]*[DB].[Out by Vendor]), [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Out by Vendor]), [DB].[Unit]='Hour(s)', ([Vendor_Rates.RateH]*[DB].[Out by Vendor])), Switch([DB].[Unit]='Word(s)',([Vendor_Rates.RateW]*[DB].[Count]), [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Count]), [DB].[Unit]='Hour(s)', ([Vendor_Rates.RateH]*[DB].[Count])))) AS VendorBilling
FROM DB INNER JOIN Vendor_Rates ON DB.Product = Vendor_Rates.Product 
WHERE (((DB.[Delivery Date]) Between #9/16/2013# And #9/20/2013#)) 
GROUP BY Vendor_Rates.RateW, DB.Product, DB.[Client Name], DB.[Project Name], DB.Unit, DB.[Out by Vendor], DB.[Delivery Date], DB.Status, DB.[Count], DB.Task, Vendor_Rates.RateP, Vendor_Rates.RateH;

[June7]

Don't need: IIf(DB.[Task]<>'Review'

Also, remove unnecessary parens (Access might throw them back in but easier to see structure without them).

Sum(IIf(DB.[Task]='Review', Switch([DB].[Unit]='Word(s)',[Vendor_Rates.RateW]*[DB].[Out by Vendor],
[DB].[Unit]='Page(s)',[Vendor_Rates.RateP]*[DB].[Out by Vendor],
[DB].[Unit]='Hour(s)',[Vendor_Rates.RateH]*[DB].[Out by Vendor]),
Switch([DB].[Unit]='Word(s)',[Vendor_Rates.RateW]*[DB].[Count],
[DB].[Unit]='Page(s)',[Vendor_Rates.RateP]*[DB].[Count],
[DB].[Unit]='Hour(s)',[Vendor_Rates.RateH]*[DB].[Count]))) AS VendorBilling

If Access still complains, use quote mark instead of apostrophe to delimit the text. Use apostrophe if constructing the SQL in VBA.

[Dal Jeanis]

Just so you can see what it should look like, here's a formatted version of what your current code looks like...

Sum(IIf(DB.[Task]='Review', 
    Switch([DB].[Unit]='Word(s)', ([Vendor_Rates.RateW]*[DB].[Out by Vendor]), 
           [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Out by Vendor]), 
           [DB].[Unit]='Hour(s)', ([Vendor_Rates.RateH]*[DB].[Out by Vendor],
####missing two right parens )) before comma, to finish off the first Switch#### 
    IIf(DB.[Task]<>'Review', 
####unneeded IIF, since this is the third argument of the first IIF - ####
####what to do if the first argument is false.  ####
    Switch([DB].[Unit]='Word(s)', ([Vendor_Rates.RateW]*[DB].[Count]), 
           [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Count]), 
           [DB].[Unit]='Hour(s)', ([Vendor_Rates.RateH]*[DB].[Count]))
))))
#### two of these parens should have been above, and one is for the unneeded IIF ####
#### the "wrong number of arguments" message is because neither IIF got what it ####
#### needed - precisely three arguments. ####
) AS VendorBilling

And what it should look like ...

Sum(IIf(DB.[Task]='Review', 
    Switch([DB].[Unit]='Word(s)', ([Vendor_Rates.RateW]*[DB].[Out by Vendor]), 
           [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Out by Vendor]), 
           [DB].[Unit]='Hour(s)', ([Vendor_Rates.RateH]*[DB].[Out by Vendor]))
    Switch([DB].[Unit]='Word(s)', ([Vendor_Rates.RateW]*[DB].[Count]), 
           [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Count]), 
           [DB].[Unit]='Hour(s)', ([Vendor_Rates.RateH]*[DB].[Count]))
    )) AS VendorBilling

[Distinctive]

Hi All,
A BIG Thank you for your help. Your suggestions were helpful.

Dal Jeanis I used your suggestion and it worked. Thank you for not only helping me with the solution but also guiding where the mistake was.

[Distinctive]

Hi,
Could you please also suggest if instead of "complete rates", 1/2 of rates mentioned in Vendor_Rates table should be multiplied by the partial Volume sent to the vendor - entered in Out by vendor. How I define it in the below code:


Sum(IIf(DB.[Task]='Review', Switch([DB].[Unit]='Word(s)', ([Vendor_Rates.RateW]*[DB].[Out by Vendor]), [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Out by Vendor]), [DB].[Unit]='Hour(s)', ([Vendor_Rates.RateH]*[DB].[Out by Vendor])) Switch([DB].[Unit]='Word(s)', ([Vendor_Rates.RateW]*[DB].[Count]), [DB].[Unit]='Page(s)', ([Vendor_Rates.RateP]*[DB].[Count]), [DB].[Unit]='Hour(s)', ([Vendor_Rates.RateH]*[DB].[Count])) )) AS VendorBilling

[Dal Jeanis]

You would multiply each term in the first switch statement by 0.5, for instance

(0.5*[Vendor_Rates.RateW]*[DB].[Out by Vendor]),

Also, don't forget the comma between the first and second switch statements.