B
BluesPhone
Please help me converting this query to MS Access 2003
I am trying to convert the following query from MySQL but seeing a lot of
problems. Basically the query is querying data only from 2 tables: order and
account
Columns customer_id and accountnum are numerical values, others are all
chars
SELECT sum( case when left(b.orderaddress,6) = '000101' then 1 else 0 end) as
'SalesOrder',
sum( case when left(b.orderaddress,6) = '000A101' then 1 else 0 end) as
'Prospect',
sum(case when at.OrderReason=1234 then 1 else 0 end) as 'PartsReplacement',
sum(case when at.OrderReason=2345 then 1 else 0 end) as 'NewParts'
FROM order at, account b
WHERE at.customer_id = b.accountnum
AND ((at.create_time>= {ts '2006-10-01 00:00:00'} and
at.create_time <= {ts '2006-10-31 23:59:59'}))
AND left(b.orderaddress,6) in ( '000101' , '00A101' , '000B01')
AND at.OrderReason in (1234, 2345, 3456, 4567)
GROUP BY left(b.orderaddress,6)
I am trying to convert the following query from MySQL but seeing a lot of
problems. Basically the query is querying data only from 2 tables: order and
account
Columns customer_id and accountnum are numerical values, others are all
chars
SELECT sum( case when left(b.orderaddress,6) = '000101' then 1 else 0 end) as
'SalesOrder',
sum( case when left(b.orderaddress,6) = '000A101' then 1 else 0 end) as
'Prospect',
sum(case when at.OrderReason=1234 then 1 else 0 end) as 'PartsReplacement',
sum(case when at.OrderReason=2345 then 1 else 0 end) as 'NewParts'
FROM order at, account b
WHERE at.customer_id = b.accountnum
AND ((at.create_time>= {ts '2006-10-01 00:00:00'} and
at.create_time <= {ts '2006-10-31 23:59:59'}))
AND left(b.orderaddress,6) in ( '000101' , '00A101' , '000B01')
AND at.OrderReason in (1234, 2345, 3456, 4567)
GROUP BY left(b.orderaddress,6)