Access SQL Server Query

[cascot]

I am using Access 2000 front end with an SQL Server 7.0 backend database.

I have a table with Orders. Each order record (amongst other things) has a CustomerId, a TypeId, and each Order record has a TotalOrderValue.

I want to show a list of Customers, with a sum of TotalOrderValue, plus a break down by Type. There are only a handful of types and they are fixed, so I want it to look like this:

Total Type1 Type2 Type3 Type4
Customer_A 10245.67 2478.21 3952.00 1743.58 2071.88

In plain old Access 2000 I would do this as two seperate queries - one with the grandTotal, the other using IIF(Type="Type1", TotalOrderValue,0) type statements. I would then embed this one with the split amounts into the main one and use the normal sum totals.

The problem seems to be that with SQL Server, this leads to the 2nd (IIF) query pulling through every record, rather than only those it needs as indicated from query one (the one with the grand total, which incudes start date / end date parameters). This leads to the query taking a very long time to run.

I know about pass through queries, but I don't know how I would merge the SQL of the two queries to create one that works and is efficient.

Can anyone offer any help with the best approach to this type of query?

 

[cmmrfrds]

You can do it in SQL Server it is called a derived table. Do a search on derived tables in sql server for some examples. Basically, it is a subquery in the From clause and then this subquery is joined to the outer Select statement.

 

[warthog72]

SELECT CustomerID, SUM(TotalOrderValue) as GrandTotal,
Abs(SUM((Type="Type1&quot*TotalOrderValue)) as Type1Total,
Abs(SUM((Type="Type2&quot*TotalOrderValue)) as Type2Total,
...
FROM ORDERS
GROUP BY CustomerID


In this manner, for each CustomerID, if expression (Type="Type1&quot evaluates to TRUE or FALSE (1 or 0) and for when its true, it will sum only where Type is the specific type, otherwise the expression evaluates to 0 (and 0 times anything is always 0). Didn't actually test the total results for time, but this has worked well for me in the past.

Hope that helps!

 

[cascot]

Thanks for your reply warthog72. I was hoping this would work, and indeed it does on a simple SELECT statement, but whenever I introduce any kind of grouping (GROUP BY, etc), I get the following error:

ODBC--call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='(#170)

Maybe this works okay in pure Access but SQL Server does not seem to like it.

Maybe I am doing something wrong? Have you used this approach before with an SQL Server backend?

 

[cascot]

Thanks for taking the time to reply cmmrfrds.

I will check the SQL Server Books Online for some details.

 

[cmmrfrds]

Actually I believe from what I see it can be done in 1 query. Make it a pass-thru query so it needs to be transact-sql syntax.

Select customerid,
sum(case when type='type1' then TotalValue else 0 end) as type1,
sum(case when type='type2' then TotalValue else 0 end) as type2,
sum(case when type='type3' then TotalValue else 0 end) as type3,
sum(TotalValue) as SumTotal
From Orders
Group by customerid

 

[cascot]

Thanks for your follow up post cmmrfrds.

I eventually opted to do it as a single conventional Access query using IIF's as follows...

Sum(IIF([Type]="Type1",[TotalValue],0))

This is unlikely to be the most efficient, but there is no obvious speed issue with it in observed performance, so I will stick with it.

Thanks once again for your help, and to warthog72.