Problem with expressions in a Totals Query

[RomeoJuliet]

I have what I think should be a fairly table structure for orders: the tables are [Orders] and [Order Details], and I have queries [Orders Qry] and [Order Details Extended]. This is all based on Northwind.

I want to create a query which summarises orders, to be used in Excel; the user wants to use Excel for analysis.

Sales can be made in a variety of ways including via PayPal. I therefore want to add some expressions at the end of the query to calculate the Paypal charges and the nett total, but only if [PaymentType] = “PayPal”.

The query (based on [Orders Qry] and [Order Details Extended]) looks like this (with irrelevant bits excluded):

[FONT=Arial]SELECT DISTINCT [Orders Qry].OrderID, [/FONT]
[FONT=Arial]Sum([Order Details Extended].ExtendedPrice) AS SumOfExtendedPrice, [/FONT]
[FONT=Arial]First([Orders Qry].PaymentType) AS Payment_Type, [/FONT]
[FONT=Arial]IIf([PaymentType]="PayPal",[SumOfExtendedPrice],Null) AS PP_Gross, [/FONT]
[FONT=Arial]IIf([PaymentType]="PayPal",([PP_Gross]*0.034)+0.2,Null) AS PP_charges, [/FONT]
[FONT=Arial]IIf([PaymentType]="PayPal",[pp_gross]-[pp_charges],Null) AS PP_nett[/FONT]
[FONT=Arial]FROM [Order Details Extended] INNER JOIN [Orders Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID[/FONT]
[FONT=Arial]GROUP BY [Orders Qry].OrderID[/FONT]
[FONT=Arial]ORDER BY [Orders Qry].OrderID;[/FONT]

… however when I try to open it the response is “You tried to execute a query that does not include the specified expression ‘IIf([PaymentType]="PayPal",[SumOfExtendedPrice],Null)’ as part of an aggregate function.

What can I do about this?

Thanks
Mike.

 

[namliam]

You have to add your IIFs to the group by...

 

[pbaldy]

Shot in the dark, but try

Sum(IIf([PaymentType]="PayPal",[ExtendedPrice],Null)) AS PP_Gross

 

[RomeoJuliet]

Thanks Mailman, but could you show me what the SQL should look like? I'm new to this...

pbaldy, I tried your suggestion and this looks like part of the answer, since the interpreter has got a bit further: now, I get ''You tried to execute a query that does not include the specified expression ‘IIf([PP_Gross]*0.034)+0.2,Null)’ as part of an aggregate function" instead. Still don't know what to do next though...

Thanks for your help, any more advise greatly appreciated!

Mike.

 

[pbaldy]

Another shot in the dark

Sum(IIf([PaymentType]="PayPal",([PP_Gross]*0.034)+0.2,0) AS PP_charges

but you may have to recreate the first formula in this one. I also changed the Null to 0, as generally Nulls may cause problems down the road. Your call on that one.

 

[RomeoJuliet]

I tried that and - you guessed it - the same error ocurred for the last expression, so I summed that too.

Now, I have a new problem! The error reads: "Subqueries cannot be used in the expression (Iif([PaymentType]="PayPal",[PP_Gross]*0.034+0.2,0))."

Subqueries...?

 

[namliam]

If you are "Group by"-ing all your field have to either be in the Group by part of the query or has to have something like Sum(), Avg() etc in the select part.

So after fixing the 0.034 line you also have to fix the line after that...

 

[pbaldy]

What's the full SQL now?

 

[RomeoJuliet]

... so Mailman, would that look like

[FONT=Arial]GROUP BY [Orders Qry].OrderID, PP_Gross, PP_Charges, PP_nett[/FONT]

... ?

I've tried that and get the same error: "You tried to execute a query that does not include the specified expression ‘IIf([PaymentType]="PayPal",[SumOfExtendedPrice],Null)’ as part of an aggregate function"

Thanks
Mike

 

[RomeoJuliet]

pb, the full SQL (following your suggestions) is:

SELECT DISTINCT [Orders Qry].OrderID,
Sum([Order Details Extended].ExtendedPrice) AS SumOfExtendedPrice,
First([Orders Qry].PaymentType) AS Payment_Type,
Sum(IIf([PaymentType]="PayPal",[ExtendedPrice],0)) AS PP_Gross,
Sum(IIf([PaymentType]="PayPal",([PP_Gross]*0.034)+0.2,0)) AS PP_charges,
Sum(IIf([PaymentType]="PayPal",[pp_gross]-[pp_charges],0)) AS PP_nett
FROM [Order Details Extended] INNER JOIN [Orders Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID
GROUP BY [Orders Qry].OrderID
ORDER BY [Orders Qry].OrderID;

Thanks!
Mike.

 

[pbaldy]

That throws an error? I've seen times when it can only go so far with aliased fields, and I've had to recreate the calculations instead of using them. Can you post a sample db?

 

[Brianwarnock]

I think Paul is correct and you cannot use the Akiasses and have to use the formulae therefore

SELECT DISTINCT [Orders Qry].OrderID, 
Sum([Order Details Extended].ExtendedPrice) AS SumOfExtendedPrice, 
First([Orders Qry].PaymentType) AS Payment_Type, 
Sum(IIf([PaymentType]="PayPal",[ExtendedPrice],0)) AS PP_Gross, 
Sum(IIf([PaymentType]="PayPal",([ExtendedPrice]*0.034)+0.2,0)) AS PP_charges, 
Sum(IIf([PaymentType]="PayPal",([ExtendedPrice]-(([ExtendedPrice]*0.034)+0.20),0) AS PP_nett
FROM [Order Details Extended] INNER JOIN [Orders Qry] ON [Order Details Extended].OrderID = [Orders Qry].OrderID
GROUP BY [Orders Qry].OrderID
ORDER BY [Orders Qry].OrderID;

Brian

 

[RomeoJuliet]

Thanks everyone, I'll give that a shot tonight, gotta dash now (been failing to get the db small enough to upload).

Mike.

 

[RomeoJuliet]

Success! Thank you Brian for spelling out what Paul was saying, and thanks Paul for helping me along the way.

Mike.