MySQL way

Z

zerocold

Dear all,

How do I "translate" this MySQL query into Access query:
--------------------------------------------------------
select a.ttl_qty - b.ttl_qty from
(select sum(qty) ttl_qty from money where type="I") a,
(select sum(qty) ttl_qty from money where type="O") b

Thanks.
 
M

Michel Walsh

Hi,


SELECT a.ttl_qty - b.ttl_qty
FROM ( SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ) AS a,

( SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ) AS b


to keep the same logic (note that I just added AS keywords). I assume you
use Jet 4.0 (Access 2000 or later)



Hoping it may help,
Vanderghast, Access MVP
 
Z

zerocold

Sorry Michel,

I need to do it in Access97.
If I do it in your suggested way, it prompt
"Syntax error in FROM clause".
Any idea?

Thanks in advance.
 
J

John Vinson

I need to do it in Access97.
If I do it in your suggested way, it prompt
"Syntax error in FROM clause".
Any idea?
Click to expand...

It was a very peculiar syntax in A97:

SELECT a.ttl_qty - b.ttl_qty
FROM [SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I"]. AS a,

[SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O"]. AS b

Note the square brackets not parens, and the required period after the
close bracket.

Or, you can use the DSum() function:

SELECT DSum("[qty]", "[money]", "[Type] = 'I'") - DSum("[qty]",
"[money]", "[Type] = 'O'") As Profit FROM <whatever table>

John W. Vinson[MVP]
 
M

Marshall Barton

A97 and earlier is a little weird. If I remember that far
back:

SELECT a.ttl_qty - b.ttl_qty
FROM [ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ]. AS a,

[ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ]. AS b

Note the funky Dot after the ]
 
Z

zerocold

Hi,

What if the table name is two seperate words, let's say "Transaction History"?
How to quote it?
Can't just quote it with bracket as usual.

Marshall Barton said:
A97 and earlier is a little weird. If I remember that far
back:

SELECT a.ttl_qty - b.ttl_qty
FROM [ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ]. AS a,

[ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ]. AS b

Note the funky Dot after the ]
--
Marsh
MVP [MS Access]

I need to do it in Access97.
If I do it in your suggested way, it prompt
"Syntax error in FROM clause".
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Marshall Barton said:
A97 and earlier is a little weird. If I remember that far
back:

SELECT a.ttl_qty - b.ttl_qty
FROM [ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ]. AS a,

[ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ]. AS b

Note the funky Dot after the ]
--
Click to expand...


Same is true for ACCESS 2000 and 2002... the funky dot syntax is needed in
these versions, too.
 
Z

zerocold

Hi all,

Can I do it this way:
SELECT iif(isNull(Sum(iif(Type="I", qty)),0,Sum(iif(Type="I", qty))) -
iif(isNull(Sum(iif(Type="O",qty))),0,Sum(iif(Type="O",qty)))
FROM [Transaction History];


Ken Snell said:
Marshall Barton said:
A97 and earlier is a little weird. If I remember that far
back:

SELECT a.ttl_qty - b.ttl_qty
FROM [ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ]. AS a,

[ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ]. AS b

Note the funky Dot after the ]
--
Click to expand...


Same is true for ACCESS 2000 and 2002... the funky dot syntax is needed in
these versions, too.
Click to expand...
 
K

Ken Snell [MVP]

No.

--

Ken Snell
<MS ACCESS MVP>

zerocold said:
Hi all,

Can I do it this way:
SELECT iif(isNull(Sum(iif(Type="I", qty)),0,Sum(iif(Type="I", qty))) -
iif(isNull(Sum(iif(Type="O",qty))),0,Sum(iif(Type="O",qty)))
FROM [Transaction History];


Ken Snell said:
Marshall Barton said:
A97 and earlier is a little weird. If I remember that far
back:

SELECT a.ttl_qty - b.ttl_qty
FROM [ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ]. AS a,

[ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ]. AS b

Note the funky Dot after the ]
--
Click to expand...


Same is true for ACCESS 2000 and 2002... the funky dot syntax is needed
in
these versions, too.
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

As I recall, you cannot use table names that have spaces in them with this
syntax. If that is what you have, you likely will need to create and save a
query that does what is in the [ ]. syntax, and then use that saved query as
a data source for the final query.

--

Ken Snell
<MS ACCESS MVP>

zerocold said:
Hi,

What if the table name is two seperate words, let's say "Transaction
History"?
How to quote it?
Can't just quote it with bracket as usual.

Marshall Barton said:
A97 and earlier is a little weird. If I remember that far
back:

SELECT a.ttl_qty - b.ttl_qty
FROM [ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ]. AS a,

[ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ]. AS b

Note the funky Dot after the ]
--
Marsh
MVP [MS Access]

I need to do it in Access97.
If I do it in your suggested way, it prompt
"Syntax error in FROM clause".


:
SELECT a.ttl_qty - b.ttl_qty
FROM ( SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ) AS a,

( SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ) AS b
Click to expand...
Click to expand...
Click to expand...
 
Z

zerocold

Hi Ken,

May I know the reason?
'Cos it seems to work in my Access97.

Ken Snell said:
No.

--

Ken Snell
<MS ACCESS MVP>

zerocold said:
Hi all,

Can I do it this way:
SELECT iif(isNull(Sum(iif(Type="I", qty)),0,Sum(iif(Type="I", qty))) -
iif(isNull(Sum(iif(Type="O",qty))),0,Sum(iif(Type="O",qty)))
FROM [Transaction History];


Ken Snell said:
A97 and earlier is a little weird. If I remember that far
back:

SELECT a.ttl_qty - b.ttl_qty
FROM [ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ]. AS a,

[ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ]. AS b

Note the funky Dot after the ]
--


Same is true for ACCESS 2000 and 2002... the funky dot syntax is needed
in
these versions, too.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

I beleive I misunderstood what you were posting. In reviewing your SQL
again, it appears that you are summing values from fields, which will work.
My initial read of the statement was that you were trying to insert field
names based on the results of the IIf statements, and that was the focus of
my answer.

My apologies.
--

Ken Snell
<MS ACCESS MVP>



zerocold said:
Hi Ken,

May I know the reason?
'Cos it seems to work in my Access97.

Ken Snell said:
No.

--

Ken Snell
<MS ACCESS MVP>

zerocold said:
Hi all,

Can I do it this way:
SELECT iif(isNull(Sum(iif(Type="I", qty)),0,Sum(iif(Type="I", qty))) -
iif(isNull(Sum(iif(Type="O",qty))),0,Sum(iif(Type="O",qty)))
FROM [Transaction History];


:

A97 and earlier is a little weird. If I remember that far
back:

SELECT a.ttl_qty - b.ttl_qty
FROM [ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="I" ]. AS a,

[ SELECT SUM(qty) AS ttl_qty
FROM money
WHERE type="O" ]. AS b

Note the funky Dot after the ]
--


Same is true for ACCESS 2000 and 2002... the funky dot syntax is
needed
in
these versions, too.
Click to expand...
Click to expand...
Click to expand...
 
Top