1

I am just converting my SQL based query to Access which runs successfully on SQL Server. I have converted everything into Access compatible format, except there is a problem in putting the number of brackets. I got the following error "SYNTAX ERROR IN FROM CLAUSE", Please help

SELECT CINT(MID(TC2.CHILDCOUNTER, 7, LEN(TC2.CHILDCOUNTER)) ) AS PKCHILDID
,tc2.VisitType
    ,MAX(IIf( tktResults.TaskCounter IN ( '001410' ,'001463' ,'001431' ),  tktResults.Result, NULL) ) AS KWA_QuitOffered
    ,MAX(IIf( tktResults.TaskCounter IN ( '001411' ,'001464' ,'001432' ),   tktResults.Result, NULL) ) AS KWA_QuitReferral
FROM ((tblConsultations tc2
INNER JOIN tblChild tc 
  ON tc2.ChildCounter = tc.ChildCounter)
LEFT  JOIN tblDelivery td 
  ON td.ChildCounter = tc.ChildCounter)
LEFT JOIN (
SELECT ttr.ResultCounter
    ,ttr.ChildCounter
    ,tkt.VisitType
    ,ttr.Result
    ,ttr.TaskCounter
FROM tblTaskResults ttr
LEFT JOIN tlkpKeyTasks tkt ON tkt.TaskCounter = ttr.TaskCounter
    AND tkt.TaskCounter IN (
        '001410'
        ,'001463'
        ,'001431'
        ,'001411'
        ,'001464'
        ,'001432'
        )
) AS tktResults 
    ON tc2.VisitType = tktResults.VisitType
AND tktResults.ChildCounter = tc2.ChildCounter
WHERE tc2.VisitType in (1, 2, 3, 4)
   AND tc2.ConsDate > '20130127' AND tc2.ConsDate < '20130228'
GROUP BY CINT(MID(TC2.CHILDCOUNTER, 7, LEN(TC2.CHILDCOUNTER)))
                                            ,TC2.VISITTYPE
                                            ,TC.CENTRECODE
                                            ,TC2.CENTRECODE
                                            ,TC2.COUNCILCODE
                                            ,TC2.CONSDATE
                                            ,TC.FEEDING3MONTHS
                                            ,TC.FEEDING6MONTHS
                                            ,TC.FEEDING12MONTHS
                                            ,TC2.WEIGHT
                                            ,TC2.HEADCIRC
                                    ORDER BY CINT(MID(TC2.CHILDCOUNTER, 7,   LEN(TC2.CHILDCOUNTER)) ), TC2.VISITTYPE
Improve this question
1
  • I believe Access requires a pair of () around each pair of tables joined in the FROM, as in FROM (t1 JOIN t2 ON...(JOIN t3 ON...(JOIN t4 ON...))) Commented Apr 18, 2013 at 2:08

3 Answers 3

Reset to default
2

MS Access requires parentheses around JOINs when you have multiple tables and you have unmatched parentheses which are causing the problems. The query should be:

SELECT ....
FROM (((tblConsultations tc2
INNER JOIN tblChild tc 
  ON tc2.ChildCounter = tc.ChildCounter)
LEFT JOIN tblDelivery td 
  ON td.ChildCounter = tc.ChildCounter)
LEFT JOIN 
(
  SELECT ttr.ResultCounter
    ,ttr.ChildCounter
    ,tkt.VisitType
    ,ttr.Result
    ,ttr.TaskCounter
  FROM tblTaskResults ttr
  LEFT JOIN tlkpKeyTasks tkt 
    ON tkt.TaskCounter = ttr.TaskCounter
    AND tkt.TaskCounter IN ('001410','001463'
                            ,'001431','001411'
                            ,'001464','001432')
) AS tktResults 
  ON tc2.VisitType = tktResults.VisitType
  AND tktResults.ChildCounter = tc2.ChildCounter)
WHERE .....

As I suggested in my last comment you should create and save a separate query for the subquery and then join on that. The subquery can be called whatever, for example call it myQuery:

  SELECT ttr.ResultCounter
    ,ttr.ChildCounter
    ,tkt.VisitType
    ,ttr.Result
    ,ttr.TaskCounter
  FROM tblTaskResults ttr
  LEFT JOIN tlkpKeyTasks tkt 
    ON tkt.TaskCounter = ttr.TaskCounter
    AND tkt.TaskCounter IN ('001410','001463'
                            ,'001431','001411'
                            ,'001464','001432')

And then you will include that in your JOIN:

SELECT ....
FROM (((tblConsultations tc2
INNER JOIN tblChild tc 
  ON tc2.ChildCounter = tc.ChildCounter)
LEFT JOIN tblDelivery td 
  ON td.ChildCounter = tc.ChildCounter)
LEFT JOIN myQuery AS tktResults  -- this is the name of the saved query your create
  ON tc2.VisitType = tktResults.VisitType
  AND tktResults.ChildCounter = tc2.ChildCounter)
WHERE .....
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

the error is still the same. I believe there should also be a parenthesis with the 3rd Left join, but the problem is perhaps due to another LEFT JOIN inside that left join
@AMS I added another layer of parentheses but the subquery LEFT JOIN should not matter because it is already wrapped inside of parentheses. One thing you can always do is use the query designer and the parentheses will be placed in the correct location.
I have edited the query according to your suggestion but the error is now "JOIN expression not supported"
OK, this is how to get the joins right. Remove all the joins in the SQL, then click on design view. This will show you all your tables without any joins at all. Then you can rejoin them using the graphical editor.
The query designer has a problem with mixing inner and left joins, also I think the subquery will have to be extracted to a separate query for the query designer to handle it correctly
|
0

Thanks guys, i am able to do it with little modifications. Actually i was missing CINT function in TaskCounter which is in string format in the original table. I also changed the between clause in date to > and <. My final query is as follows:

SELECT CINT(MID(TC2.CHILDCOUNTER, 7, LEN(TC2.CHILDCOUNTER)) ) AS PKCHILDID
,tc2.VisitType
    ,MAX(IIf( tktResults.TaskCounter IN ( '001410' ,'001463' ,'001431' ),  tktResults.Result, NULL) ) AS KWA_QuitOffered
    ,MAX(IIf( tktResults.TaskCounter IN ( '001411' ,'001464' ,'001432' ),   tktResults.Result, NULL) ) AS KWA_QuitReferral
FROM ((tblConsultations tc2
INNER JOIN tblChild tc 
  ON tc2.ChildCounter = tc.ChildCounter)
LEFT  JOIN tblDelivery td 
  ON td.ChildCounter = tc.ChildCounter)
LEFT JOIN (
SELECT ttr.ResultCounter
    ,ttr.ChildCounter
    ,tkt.VisitType
    ,ttr.Result
    ,ttr.TaskCounter
FROM tblTaskResults ttr
LEFT JOIN tlkpKeyTasks tkt ON CINT(tkt.TaskCounter) = CINT(ttr.TaskCounter)
WHERE tkt.TaskCounter IN (
        '001410'
        ,'001463'
        ,'001431'
        ,'001411'
        ,'001464'
        ,'001432'
        )
) AS tktResults 
    ON tc2.VisitType = tktResults.VisitType
AND tktResults.ChildCounter = tc2.ChildCounter
WHERE tc2.VisitType in (1, 2, 3, 4)
   AND tc2.ConsDate > '19980127' AND tc2.ConsDate < '20130228'
GROUP BY CINT(MID(TC2.CHILDCOUNTER, 7, LEN(TC2.CHILDCOUNTER)))
                                            ,TC2.VISITTYPE
                                            ,TC.CENTRECODE
                                            ,TC2.CENTRECODE
                                            ,TC2.COUNCILCODE
                                            ,TC2.CONSDATE
                                            ,TC.FEEDING3MONTHS
                                            ,TC.FEEDING6MONTHS
                                            ,TC.FEEDING12MONTHS
                                            ,TC2.WEIGHT
                                            ,TC2.HEADCIRC
                                    ORDER BY CINT(MID(TC2.CHILDCOUNTER, 7,  LEN(TC2.CHILDCOUNTER)) ), TC2.VISITTYPE
Improve this answer

Comments

0

Convert your subquery to a separate named query and then use that named query in place of the subquery. Also remove the inner join query by also saving it as a separate query:

So first take this part:

SELECT 
    CINT(MID(TC2.CHILDCOUNTER, 7, LEN(TC2.CHILDCOUNTER)) ) AS PKCHILDID
    ,tc2.VisitType
    ,MAX(IIf( tktResults.TaskCounter IN ( '001410' ,'001463' ,'001431' ),  tktResults.Result, NULL) ) AS KWA_QuitOffered
    ,MAX(IIf( tktResults.TaskCounter IN ( '001411' ,'001464' ,'001432' ),   tktResults.Result, NULL) ) AS KWA_QuitReferral
FROM tblConsultations tc2
   INNER JOIN tblChild tc 
     ON tc2.ChildCounter = tc.ChildCounter

And save it as a query, for example BaseQuery

Then take this:

SELECT 
    ttr.ResultCounter
    ,ttr.ChildCounter
    ,tkt.VisitType
    ,ttr.Result
    ,ttr.TaskCounter
FROM tblTaskResults ttr
    LEFT JOIN tlkpKeyTasks tkt 
        ON tkt.TaskCounter = ttr.TaskCounter 
           AND tkt.TaskCounter IN (
                '001410'
                ,'001463'
                ,'001431'
                ,'001411'
                ,'001464'
                ,'001432'
            )

And save it as another query, say SubQuery (use better names)

And then your query becomes:

SELECT 
   CINT(MID(TC.CHILDCOUNTER, 7, LEN(TC.CHILDCOUNTER)) ) AS PKCHILDID
   ,tc.VisitType
   ,MAX(IIf( tktResults.TaskCounter IN ( '001410' ,'001463' ,'001431' ),  tktResults.Result, NULL) ) AS KWA_QuitOffered
    ,MAX(IIf( tktResults.TaskCounter IN ( '001411' ,'001464' ,'001432' ),   tktResults.Result, NULL) ) AS KWA_QuitReferral    
FROM BaseQuery tc
    LEFT JOIN tblDelivery td 
      ON td.ChildCounter = tc.ChildCounter)
    LEFT JOIN SubQuery tktResults 
      ON tc.VisitType = tktResults.VisitType AND tktResults.ChildCounter = tc.ChildCounter)
WHERE tc.VisitType in (1, 2, 3, 4)
   AND tc.ConsDate > '20130127' AND tc.ConsDate < '20130228'
GROUP BY 
    CINT(MID(TC.CHILDCOUNTER, 7, LEN(TC.CHILDCOUNTER)))
    ,TC.VISITTYPE
    ,TC.CENTRECODE
    ,TC.CENTRECODE
    ,TC.COUNCILCODE
    ,TC.CONSDATE
    ,TC.FEEDING3MONTHS
    ,TC.FEEDING6MONTHS
    ,TC.FEEDING12MONTHS
    ,TC.WEIGHT
    ,TC.HEADCIRC
ORDER BY CINT(MID(TC.CHILDCOUNTER, 7,   LEN(TC.CHILDCOUNTER)) ), TC.VISITTYPE

Note the tc2.VisitType and tc2.ChildCounter becomes tc.VisitType and tc.ChildCounter because it is included in BaseQuery

Improve this answer

2 Comments

Could you please paste the whole query as i am bit confused on how to do that? Thanks in advance
Ok I fleshed out the query--obviously I can't test it so there may be a typo but hopefully you see what I'm getting at.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.