MySQL query conversion to MSAccess 2003 query for report

  • Thread starter Thread starter BluesPhone
  • Start date Start date
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)
 
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);
 
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)
 
Duane,

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

Many thanks.

Best regards,

Jon

Duane said:
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
Click to expand...
[quoted text clipped - 25 lines]
GROUP BY left(b.orderaddress,6)
Click to expand...
Click to expand...
 
Don't act so surprised!

--
Duane Hookom
MS Access MVP

BluesPhone said:
Duane,

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

Many thanks.

Best regards,

Jon

Duane said:
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
Click to expand...
[quoted text clipped - 25 lines]
GROUP BY left(b.orderaddress,6)
Click to expand...
Click to expand...
Click to expand...
 
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

John said:
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
Click to expand...
[quoted text clipped - 25 lines]
GROUP BY left(b.orderaddress,6)
Click to expand...
Click to expand...
 
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

John said:
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
Click to expand...
[quoted text clipped - 25 lines]
GROUP BY left(b.orderaddress,6)
Click to expand...
Click to expand...
 
Sorry, you've lost me.

Try posting a new thread.


BluesPhone via AccessMonster.com said:
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

John said:
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
Click to expand...
[quoted text clipped - 25 lines]
GROUP BY left(b.orderaddress,6)
Click to expand...
Click to expand...
Click to expand...
 
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:
Click to expand...
[quoted text clipped - 17 lines]
Click to expand...
 
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

BluesPhone via AccessMonster.com said:
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:
Click to expand...
[quoted text clipped - 17 lines]
GROUP BY left(b.orderaddress,6)
Click to expand...
Click to expand...
Click to expand...
 
Duane,

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

Thnks for your help

Jon

Duane said:
I thought you were to ask this question in a new thread and probably in a
more appropriate news group...
Duane,
Click to expand...
[quoted text clipped - 22 lines]
Click to expand...
 
Back
Top