I use Query in Access Report.
it give syntax error.

"syntax error in query expression
sum(dbo_mh1) mh1 "

When I use query with out alias it give this error

syntax error missiong operator in query expression
sum(mh2)
,sum(mh3)
,sum(mh4)
,sum(mh5)

from select .......
.....
,mh1=case when b.weekno=1 then a.mh1 else 0 end
,mh2=case when b.weekno=2 then a.mh1 else 0 end
....
..


Query is

select a.projno
    ,e.Name
    ,e.dept
    ,a.empno
    ,b.dated
    ,b.bh
    ,sum(mh1) mh1
    ,sum(MH2) MH2
    ,sum(MH3) MH3
    ,sum(MH4) MH4
    ,sum(MH5) MH5
    ,sum(total) total
from (
    select empno
        ,projno
        ,mh1 = case when b.weekno = 1 then a.mh1 else 0 end
        ,MH2 = case when b.weekno = 2 then a.mh1 else 0 end
        ,MH3 = case when b.weekno = 3 then a.mh1 else 0 end
        ,MH4 = case when b.weekno = 4 then a.mh1 else 0 end
        ,mh5 = case when b.weekno = 5 then a.mh1 else 0 end
        ,total = a.mh1
    from mh1 a
    JOIN weeks b
        ON a.dated = b.dated
    ) a

JOIN bh b
        ON a.empno = b.empno
        AND a.projno = b.projno
JOIN Emp e
        ON a.empno = e.empno
where b.dated='11-26-2004'
group by
    a.projno
    ,e.Name
    ,e.dept
    ,a.empno
    ,b.dated
    ,b.bh
order by e.name

How can use above query in Access Report?
I want to run report by the query.
above query properly run in query analyzer.
I am using odbc connection. My tables in sql server 2000
with link odbc connection ie.

table in access like this.

dbo_emp
dbo_project
dbo_BH
dbo_MH1
dbo_dept
...

I also try to use dbo_ before table name in the query but it also give syntax error.

Please help that how can use above query in access ?
how can use CASE Clause in Access ?

Regards.

Mateen

It seems the basic problem is that:

sum(dbo_mh1) mh1

should be:

sum(dbo_mh1) AS mh1

According to the Data Manipulation language help, this is the syntax for a SELECT:

SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

I read that as, if you are going to give a field an alias, the "AS" must be used.

Why don't you try building your query using Access's Query Builder? Whenever I can't figure out the right syntax, I use that as my starting point.

I've never seen the "mh1 = case when b.weekno = 1 then a.mh1 else 0 end" syntax. It seems odd to me that you're repeating "a.mh1" for each case regardless of the value of "b.weekno". Also it seems that a.mh1 is not referring to a field. Isn't "a" the alias of table "mh1"? Or is "a" the alias of the table that is created by the sub-select?

Since I don't know what the "case when" is trying to accomplish, I can't be sure what a substitute could be. If I were to guess it's purpose (to set a value for the mh* aliases), that might be managed with something like "iif(b.weekno = 1,a.mh1,0) as mh1".

Everything else looks quite doable with Access's Query Builder. You might consider making the "Select empno, projno, ... from mh1 a JOIN weeks b ..." a query by itself. Then use that as your outer table "a".

You might try pasting your query into the SQL view of Query Builder and start cleaning up everywhere it presents errors. Since the syntax looks pretty complex, finding the problems using Query Builder might take a while.

Does Query Analyzer actually show you the results you expect to see? For that matter, does Query Analyzer show you the CORRECT results? That is, is the correct answer the same as the answer you expect to see?

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

I have something to say about my recent experience with recordsource and
qries. I have had a qry built from SQL strings which for reasons of easy
reading i've used the alias clause (btw the books say u can use alias without
the AS). Ok, i kept having errors messsages about the qry could not be built
as long as i have had the alias. As soon as i have had the alias removed the
qry worked as it should.
Rgds penta.

Thanks for your response and comments.

1. I try to built query in access builder first. but my result
not retrieve by access query builder (report).
1. Yes query analyzer actually show the results which I expect.
2. yes query analyzer show the correct result.

I use the above query in Access ie.

select
dbo_a.projno ,
dbo_e.Name ,
dbo_e.dept ,
dbo_a.empno ,
dbo_b.dated ,
dbo_b.bh ,
sum(dbo_mh1) as mh1,
sum(dbo_MH2) as mh2 ,sum(dbo_MH3) as mh3 ,sum(dbo_MH4) as mh4 ,sum(dbo_MH5) as mh5 ,sum(dbo_total) as total
from (select dbo_empno
,dbo_projno ,
dbo_mh1 = case when dbo_b.weekno = 1 then dbo_a.mh1 else 0 end ,
dbo_MH2 = case when dbo_b.weekno = 2 then dbo_a.mh1 else 0 end,
dbo_MH3 = case when dbo_b.weekno = 3 then dbo_a.mh1 else 0 end,
dbo_MH4 = case when dbo_b.weekno = 4 then dbo_a.mh1 else 0 end,

mh5 = case when b.weekno = 5 then a.mh1 else 0 end,
total = a.mh1

from dbo_mh1 a JOIN dbo_weeks b ON dbo_a.dated = dbo_b.dated ) a JOIN dbo_bh b ON dbo_a.empno = dbo_b.empno AND dbo_a.projno = dbo_b.projno JOIN dbo_Emp e ON dbo_a.empno = dbo_e.empno

where dbo_b.dated='11/26/2004' AND
dbo_E.dept='highway section'

group by dbo_a.projno ,
dbo_e.dept ,dbo_e.name
,dbo_a.empno ,
dbo_b.bh ,
dbo_b.dated
order by dbo_e.name


above query give error ie.
"syntax error in FROM clause"

and currsor stop/move on JOIN key word.

I have following tables.

BH (bugeted hour)
MH1 (weekly manhours)
EMP
PROJECT
DEPT
WEEKS
...

BH table data
empno..projno..BH....dated......userid
-----------------------------------------------------
34675..2010....200...4/26/2005..MartinK
73322..2016....170...4/26/2005..PeterJ
....
..


MH1 table (data).
empno..projno..HM1..dated......userid
------------------------------------------------
34675..2010...25....4/1/2005..MartinK
73322..2016...20....4/8/2005..PeterJ
34613..2015...15....4/19/2005..MarkK
13337..2210...10....4/26/2005..BonerJ
....
..

weeks table
-------------
weekno...dated
1.......4/1/2005
2.......4/8/2005
3.......4/19/2005
4.......4/26/2005

ie. employee Martin assign BH 200 and during the one month
Martin work first week 10 hours, second weeek 20 hour third week
30 hour fourth week 40 hour total 100 hour work, out of 200 hours.

I want to retreive data/report like this.

Project No. 2016....date:4/26/2005
Dept Highway
------------------------------------
empno..name..(BH)..mh1,mh2,mh3,mh4...Actual job-to-date....% Utilized
----------------------------------------------------------
06360 Bulent.200....20...15...10..20...65.
15164 John...200....20...10...20..10...60
....
.....
------------------------------------------------------------
.............400....40...25...30...30..125... 125/400*100=31.25%


ie. from single table MH1 data have to transpose to four separate columen how ?
how can generat above report in access query builder ?

advance thanks for your help.

regards.

Mateen