Trouble with Sum(iif... statement

[Chad Access]

Hello,



I have the following query:

SELECT Data.[Item Size], Data.[Color], Sum(Data.[Line Item Qty]) AS [SumOfLine Item Qty], Sum(IIf(Data.[Dec Description] Like “*xyz*”,Data.[Count],0)) AS [Second Total]
FROM Data INNER JOIN [Sales Orders] ON Data.[SO #] = [Sales Orders].[Sales Orders]
GROUP BY Data.[Item Size], Data.[Color]
HAVING (((Data.[Color])<>"NA"));

I keep getting a parameter prompt for the first asterisk in my like comparison, a second parameter prompt for the xyz, and a third prompt for the second asterisk.

I haven't used Access in quite some time. Does anyone know what I am doing wrong?

Thanks!

[ranman256]

your design is wrong,
1 field has the *xyz*
2nd field has the sum.

dont do it all in 1 field.

[Chad Access]

Sorry I'm pretty rusty. What do you mean by one field. The first sum is being summed on one alias name, and the second sum has a different alias name.

So a basic example of the data:

Description - Count Color
abc 4 Green
xyz 5 Green
def 2 Green
xyz 4 Green

So I want the results to look like this

Color Total Second Total
Green 15 9



So I want it to tell me for each color, how many are there total, and how many of the total have a description (XYZ)

[pbaldy]

Can you attach a sample of the db here? In a brief test, my query ran without prompts:

Sum(IIf([l_name] Like "*ew*",[zone],0)) AS Whatever

[ranman256]

dont put the IF in the sum stmt.
if you must, make 2 querys
Q1 sum the [ Item Qt] per [Dec Description]
Q2 on Q1, NOW do the IF,
select * ,iif([Dec Description] like “*xyz*”),0,SumOfItmCount) from Q1

[pbaldy]

dont put the IF in the sum stmt.

I'm curious why not. I also don't think your 2-query solution returns the data as desired, unless I'm missing something.

[Chad Access]

Quick Test - Copy.accdb

Hopefully this clears up what I'm trying to do. Thanks!

[pbaldy]

Your query is:

SELECT Data.[Item Size], Data.Color, Sum(Data.[Line Item Qty]) AS [SumOfLine Item Qty], Sum(IIf(Data.Description Like [“]*xyz*[”],Data.Count,0)) AS [New Total]
FROM Data INNER JOIN [Sales Orders] ON Data.[SO #] = [Sales Orders].[Sales Orders]
GROUP BY Data.[Item Size], Data.Color
HAVING (((Data.Color)<>"NA"));

note the bracketing in your IIf(). Try:

SELECT Data.[Item Size], Data.Color, Sum(Data.[Line Item Qty]) AS [SumOfLine Item Qty], Sum(IIf(Data.Description Like "*xyz*",Data.Count,0)) AS [New Total]
FROM Data INNER JOIN [Sales Orders] ON Data.[SO #] = [Sales Orders].[Sales Orders]
GROUP BY Data.[Item Size], Data.Color
HAVING (((Data.Color)<>"NA"));

which works without prompt for me.

[Chad Access]

Perfect! Thanks so much! That is a huge help. I did not realize the bracketing was such a big deal.

[Chad Access]

Perfect! Thanks so much! That is a huge help. I did not realize the bracketing was such a big deal.

Unfortunately when I make the change in my full scale DB, I get the same parameter prompts. The only difference is the fields in the table have names that contain a space, and thus require the brackets.

What can I do? I do not think I can change the data file because it comes in from another source.

[pbaldy]

What is the SQL of the query in the full scale db?

[Chad Access]

SELECT Data.[Item Size], Data.[Lid Color], Sum(Data.[Line Item Qty]) AS [SumOfLine Item Qty], Sum(IIf(Data.[Dec Description] Like [“]*xyz*[”],Data.[Cup Count],0)) AS [TotalTwo]
FROM Data INNER JOIN [Sales Orders] ON Data.[SO #] = [Sales Orders].[Sales Orders]
GROUP BY Data.[Item Size], Data.[Lid Color]
HAVING (((Data.[Lid Color])<>"NA"));

[pbaldy]

Does this work?

SELECT Data.[Item Size], Data.[Lid Color], Sum(Data.[Line Item Qty]) AS [SumOfLine Item Qty], Sum(IIf(Data.[Dec Description] Like "*xyz*",Data.[Cup Count],0)) AS [TotalTwo]
FROM Data INNER JOIN [Sales Orders] ON Data.[SO #] = [Sales Orders].[Sales Orders]
GROUP BY Data.[Item Size], Data.[Lid Color]
HAVING (((Data.[Lid Color])<>"NA"));

[Chad Access]

YES! Yes, it does! I'm looking for the difference still, what was it???

Much appreciated! Thanks.

[pbaldy]

Basically I just got rid of the brackets, but I also noticed your SQL had angled quotes and mine were straight. It could be that they are treated differently.