MySQL query conversion to MSAccess 2003 query for report

[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)

 

[Duane Hookom]

Try this SQL in an Access query:

SELECT sum(Abs(left(b.orderaddress,6) = "000101")) as SalesOrder,
sum( Abs(left(b.orderaddress,6) = "000A101")) as Prospect,
sum(Abs(at.OrderReason=1234)) as PartsReplacement,
sum(Abs(at.OrderReason=2345)) as NewParts

FROM order at, account b

WHERE at.customer_id = b.accountnum
AND at.create_time Between #2006-10-01# and #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);

 

[John Spencer]

SELECT sum( IIF (Left(b.orderaddress,6) = '000101' ,1 ,0)) as SalesOrder,
sum( IIF( LEFT(b.orderaddress,6) = '00A101',1,0)) as Prospect,
sum(IIF(OrderReason=1234 ,1,0)) as PartsReplacement,
sum(IIF(OrderReason=2345,1,0) as NewParts

FROM order at, account b

WHERE at.customer_id = b.accountnum
AND at.create_time>= #2006-10-01 00:00:00# and
at.create_time <= #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)

You could use

SELECT Abs(SUM( b.orderaddress LIKE '000101*')) as SalesOrder
, Abs(SUM(b.orderaddress LIKE '00A101*')) as Prospect
, Abs(SUM(OrderReason=1234)) as PartsReplacement
, Abs(SUM(OrderReason=2345) as NewParts
FROM order at INNER JOIN account b
ON at.customer_id = b.accountnum
WHERE at.create_time >= #2006-10-01 00:00:00# and
at.create_time <= #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)

 

[BluesPhone]

Duane,

Wow, you are awesome. Holycow, It worked!!!! !!!!

Many thanks.

Best regards,

Jon

Try this SQL in an Access query:

SELECT sum(Abs(left(b.orderaddress,6) = "000101")) as SalesOrder,
sum( Abs(left(b.orderaddress,6) = "000A101")) as Prospect,
sum(Abs(at.OrderReason=1234)) as PartsReplacement,
sum(Abs(at.OrderReason=2345)) as NewParts

FROM order at, account b

WHERE at.customer_id = b.accountnum
AND at.create_time Between #2006-10-01# and #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);

Please help me converting this query to MS Access 2003

[quoted text clipped - 25 lines]

GROUP BY left(b.orderaddress,6)

 

[Duane Hookom]

Don't act so surprised!

--
Duane Hookom
MS Access MVP

Duane,

Wow, you are awesome. Holycow, It worked!!!! !!!!

Many thanks.

Best regards,

Jon

Try this SQL in an Access query:

SELECT sum(Abs(left(b.orderaddress,6) = "000101")) as SalesOrder,
sum( Abs(left(b.orderaddress,6) = "000A101")) as Prospect,
sum(Abs(at.OrderReason=1234)) as PartsReplacement,
sum(Abs(at.OrderReason=2345)) as NewParts

FROM order at, account b

WHERE at.customer_id = b.accountnum
AND at.create_time Between #2006-10-01# and #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);

Please help me converting this query to MS Access 2003

[quoted text clipped - 25 lines]

GROUP BY left(b.orderaddress,6)

 

[BluesPhone]

Hi John,

This is very helpful and much more pertinent to my db query needs.

Again, thanks a whole lot, just great timing!

Best regards,

Jon

SELECT sum( IIF (Left(b.orderaddress,6) = '000101' ,1 ,0)) as SalesOrder,
sum( IIF( LEFT(b.orderaddress,6) = '00A101',1,0)) as Prospect,
sum(IIF(OrderReason=1234 ,1,0)) as PartsReplacement,
sum(IIF(OrderReason=2345,1,0) as NewParts

FROM order at, account b

WHERE at.customer_id = b.accountnum
AND at.create_time>= #2006-10-01 00:00:00# and
at.create_time <= #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)

You could use

SELECT Abs(SUM( b.orderaddress LIKE '000101*')) as SalesOrder
, Abs(SUM(b.orderaddress LIKE '00A101*')) as Prospect
, Abs(SUM(OrderReason=1234)) as PartsReplacement
, Abs(SUM(OrderReason=2345) as NewParts
FROM order at INNER JOIN account b
ON at.customer_id = b.accountnum
WHERE at.create_time >= #2006-10-01 00:00:00# and
at.create_time <= #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)

Please help me converting this query to MS Access 2003

[quoted text clipped - 25 lines]

GROUP BY left(b.orderaddress,6)

 

[BluesPhone via AccessMonster.com]

John,

I have a new question for you.

Now that I link 2 databases on 2 different servers into the query you
suggested, and it worked, how can I automate the link from a report basis to
execute it automatically for a midnight run?

Again, thanks for your help.

Best regards,

Jon

SELECT sum( IIF (Left(b.orderaddress,6) = '000101' ,1 ,0)) as SalesOrder,
sum( IIF( LEFT(b.orderaddress,6) = '00A101',1,0)) as Prospect,
sum(IIF(OrderReason=1234 ,1,0)) as PartsReplacement,
sum(IIF(OrderReason=2345,1,0) as NewParts

FROM order at, account b

WHERE at.customer_id = b.accountnum
AND at.create_time>= #2006-10-01 00:00:00# and
at.create_time <= #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)

You could use

SELECT Abs(SUM( b.orderaddress LIKE '000101*')) as SalesOrder
, Abs(SUM(b.orderaddress LIKE '00A101*')) as Prospect
, Abs(SUM(OrderReason=1234)) as PartsReplacement
, Abs(SUM(OrderReason=2345) as NewParts
FROM order at INNER JOIN account b
ON at.customer_id = b.accountnum
WHERE at.create_time >= #2006-10-01 00:00:00# and
at.create_time <= #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)

Please help me converting this query to MS Access 2003

[quoted text clipped - 25 lines]

GROUP BY left(b.orderaddress,6)

 

[John Spencer]

Sorry, you've lost me.

Try posting a new thread.

John,

I have a new question for you.

Now that I link 2 databases on 2 different servers into the query you
suggested, and it worked, how can I automate the link from a report basis
to
execute it automatically for a midnight run?

Again, thanks for your help.

Best regards,

Jon

SELECT sum( IIF (Left(b.orderaddress,6) = '000101' ,1 ,0)) as SalesOrder,
sum( IIF( LEFT(b.orderaddress,6) = '00A101',1,0)) as Prospect,
sum(IIF(OrderReason=1234 ,1,0)) as PartsReplacement,
sum(IIF(OrderReason=2345,1,0) as NewParts

FROM order at, account b

WHERE at.customer_id = b.accountnum
AND at.create_time>= #2006-10-01 00:00:00# and
at.create_time <= #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)

You could use

SELECT Abs(SUM( b.orderaddress LIKE '000101*')) as SalesOrder
, Abs(SUM(b.orderaddress LIKE '00A101*')) as Prospect
, Abs(SUM(OrderReason=1234)) as PartsReplacement
, Abs(SUM(OrderReason=2345) as NewParts
FROM order at INNER JOIN account b
ON at.customer_id = b.accountnum
WHERE at.create_time >= #2006-10-01 00:00:00# and
at.create_time <= #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)

Please help me converting this query to MS Access 2003

[quoted text clipped - 25 lines]

GROUP BY left(b.orderaddress,6)

 

[BluesPhone via AccessMonster.com]

Duane,

How can I automate the manual process of "link tables" from "get external
data" to link 2 databases from 2 different servers. I'd like to automate this
process to run the query in the middle of the night.

Thanks for your help, once again.

Jon

Duane,

Wow, you are awesome. Holycow, It worked!!!! !!!!

Many thanks.

Best regards,

Jon

Try this SQL in an Access query:

[quoted text clipped - 17 lines]

 

[Duane Hookom]

I thought you were to ask this question in a new thread and probably in a
more appropriate news group...

--
Duane Hookom
MS Access MVP

Duane,

How can I automate the manual process of "link tables" from "get external
data" to link 2 databases from 2 different servers. I'd like to automate
this
process to run the query in the middle of the night.

Thanks for your help, once again.

Jon

Duane,

Wow, you are awesome. Holycow, It worked!!!! !!!!

Many thanks.

Best regards,

Jon

Try this SQL in an Access query:

[quoted text clipped - 17 lines]

GROUP BY left(b.orderaddress,6)

 

[BluesPhone via AccessMonster.com]

Duane,

I posted this question as a new thread in the "queries" discussion group.

Thnks for your help

Jon

I thought you were to ask this question in a new thread and probably in a
more appropriate news group...

Duane,

[quoted text clipped - 22 lines]