Need SQL expert!

[auujxa2 via AccessMonster.com]

I have SQL that I am trying to put into the SQL view of an access query, that
is not working. At a glance, can you scan this and tell me what you think is
wrong? I know you have to write a certain syntax for SQL using VBA, but I
didn't know I did for SQL view in access queries.

Thank you in advance. (p.s. I know one thing is brackets near the AS
statements)

--- start of class level totals
SELECT
Dmd_Ctr
, DC_Name
, Dept
, Dept_name
, Class
, Class_name
, sum(Min_qty) as min_soq_$
, (sum(Min_qty) / sum(total_$) * 100 ) as MIN_PCT
, sum(For_qty) as fcst_soq_$
, (sum(For_qty) / sum(total_$) * 100 ) as FOR_PCT
, round(sum(total_$),2) as total_ORIG_$

from
(
SELECT
SOQHIST.CLASS1ID AS Dmd_Ctr
, CLASS1.DESCRIPTION AS DC_Name
, SOQHIST.CLASS2ID AS Dept
, CLASS2.DESCRIPTION AS Dept_Name
, CLASS3.CLASS3ID AS Class
, CLASS3.DESCRIPTION AS Class_Name
, decode(SOQHIST.minshelf , SOQHIST.topuplevel, (SOQHIST.ORIGINALQTY *
SOQHIST.RETAILPRICE) , 0 ) as Min_qty
, decode(SOQHIST.minshelf , SOQHIST.topuplevel, 0 , (SOQHIST.ORIGINALQTY *
SOQHIST.RETAILPRICE) ) as For_qty
, (SOQHIST.ORIGINALQTY * (SOQHIST.RETAILPRICE + 0.0000001)) as total_$
---- add small number to avoid dividing by 0
FROM
CLASS2 INNER JOIN CLASS1

INNER JOIN CLASS3

INNER JOIN SOQHIST
ON CLASS3.CLASS1ID = SOQHIST.CLASS1ID
AND CLASS3.CLASS2ID = SOQHIST.CLASS2ID
AND CLASS3.CLASS3ID = SOQHIST.CLASS3ID

ON CLASS1.CLASS1ID = SOQHIST.CLASS1ID

ON CLASS2.CLASS1ID = SOQHIST.CLASS1ID
AND CLASS2.CLASS2ID = SOQHIST.CLASS2ID


WHERE
SOQHIST.CLASS1ID = 361_DEMAND_CENTER
and SOQHIST.LASTREVIEWDATE = to_dateP361_CREATE_DATE,'MM-DD-YYYY')

) t2
group by
Dmd_Ctr
, DC_Name
, Dept
, Dept_name
, Class
, Class_name

) t3
--- end of class level totals

 

[akphidelt]

Read this article about surviving subqueries from Allen Browne

http://www.allenbrowne.com/subquery-02.html

 

[Michel Walsh]

- 1- Try adding parenthesis in your joins:

FROM
CLASS2 INNER JOIN ( CLASS1 ' <--- here

INNER JOIN ( CLASS3 ' <--- here

INNER JOIN SOQHIST
ON CLASS3.CLASS1ID = SOQHIST.CLASS1ID
AND CLASS3.CLASS2ID = SOQHIST.CLASS2ID
AND CLASS3.CLASS3ID = SOQHIST.CLASS3ID) ' <--

ON CLASS1.CLASS1ID = SOQHIST.CLASS1ID) ' <--

ON CLASS2.CLASS1ID = SOQHIST.CLASS1ID
AND CLASS2.CLASS2ID = SOQHIST.CLASS2ID



- 2 - Remove the comments, Jet does not use them, even though it may
find it is comment, it will remove them...or may miss the fact you use a
comment.

---- add small number to avoid dividing by 0



- 3 - :name is not a valid syntax in Jet


361_DEMAND_CENTER
361_CREATE_DATE


if they are parameters, just remove the : . Jet simply assumes that any
unknown (field) value is a parameter.



- 4 - to_date( ) is not a valid Jet function. You may use:

Format( argument, "MM-DD-YYYY")


instead.


- 5 - You may try to make a query from the inner subquery that you
actually alias as t2. That may allow simpler debugging, since t2 can now be
debugged all by itself, and even, in some case, it allows faster execution
time.




( I may have missed a couple of problems)

Vanderghast, Access MVP