statement has a syntax error

Could someone please help me figure out why I have a syntax error. Thanks.

(Syntax error (missing operator) in query expression DataSource3.publisher = DataSource1.publisher LEFT OUTER JOIN (SELECT publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT.)

SELECT DataSource1.publisher, IIf(IsNull([CountProjectMGMT]),"na", Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT)) AS AvgProjectMGMT,
IIf(IsNull([CountDesign]),"na", Val(DataSource1.SumDesign / DataSource3.CountDesign)) AS AvgDesign
FROM (SELECT publisher, SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumProjectMGMT, SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumDesign FROM  evaluations GROUP BY publisher) AS DataSource1 LEFT OUTER  JOIN
                          (SELECT publisher, COUNT(ps_design) AS CountDesign
                            FROM  evaluations AS Table_1_2
                            WHERE (ps_design <> "na")
                            GROUP BY publisher) AS DataSource3 ON DataSource3.publisher = DataSource1.publisher LEFT OUTER  JOIN
                          (SELECT  publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT  FROM evaluations AS Table_1_1
                            WHERE (ps_projectmgmt <> "na")  GROUP BY publisher) AS DataSource2 ON DataSource2.publisher = DataSource1.publisher;

Re: statement has a syntax error

Try:

SELECT          DataSource1.publisher,
                IIf(IsNull([CountProjectMGMT]),"na", Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT)) AS AvgProjectMGMT,
                IIf(IsNull([CountDesign]),"na", Val(DataSource1.SumDesign / DataSource3.CountDesign)) AS AvgDesign
FROM            ((SELECT   publisher,
                           SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumProjectMGMT,
                           SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]="na",0, Val([ps_projectmgmt]))) AS SumDesign
                  FROM     evaluations
                  GROUP BY publisher) AS DataSource1
LEFT OUTER JOIN (SELECT   publisher,
                           COUNT(ps_design) AS CountDesign
                  FROM     evaluations AS Table_1_2
                  WHERE    (ps_design <> "na")
                  GROUP BY publisher) AS DataSource3
ON               DataSource3.publisher = DataSource1.publisher)
LEFT OUTER JOIN (SELECT   publisher,
                          COUNT(ps_projectmgmt) AS CountProjectMGMT
                 FROM     evaluations AS Table_1_1
                 WHERE    (ps_projectmgmt <> "na")
                 GROUP BY publisher) AS DataSource2
ON              DataSource2.publisher = DataSource1.publisher;

Re: statement has a syntax error

Thank you, that removed the syntax error. I have one other question. I am trying to add the following to the SQL statement but it's not working it's giving me a wrong output.

WHERE dateSent>=#01/01/2011#

Any idea where I can add the piece above?

Thanks

Re: statement has a syntax error

lm111

I am trying to add the following to the SQL statement but it's not working it's giving me a wrong output.

WHERE dateSent>=#01/01/2011#

Any idea where I can add the piece above?

In which table(s) do you want to check the dateSent field?

Re: statement has a syntax error

The dateSent field is in my evaluations table. Could we check it somehow in the following block of code:

(SELECT   publisher,
                           COUNT(ps_design) AS CountDesign
                  FROM     evaluations AS Table_1_2
                  WHERE    (ps_design <> "na")
                  GROUP BY publisher) AS DataSource3
ON               DataSource3.publisher = DataSource1.publisher)
LEFT OUTER JOIN (SELECT   publisher,
                          COUNT(ps_projectmgmt) AS CountProjectMGMT
                 FROM     evaluations AS Table_1_1
                 WHERE    (ps_projectmgmt <> "na")
                 GROUP BY publisher

I tried something like this but the output is not right

WHERE (ps_design <> "na" AND dateSent>=#01/01/2011#)

Thanks

Re: statement has a syntax error

lm111

WHERE (ps_design <> "na" AND dateSent>=#01/01/2011#)

This is correct syntax

lm111

I tried something like this but the output is not right

The only way to solve this is that you try to explain in words which result you want. Show also some records and the result you want

Re: statement has a syntax error

Hello,

Here is the link to my other thread that explains it better.

http://forums.asp.net/t/1723766.aspx/1?Display+correct+data+in+GridView

The answer from 'mezzanine74' works fine I just need to figure out how to add the dateSent field.

Thank you so much.

Re: statement has a syntax error

I really don't understand what result you want. But to help you a little bit further:

When a field can contain either a numeric value, NULL of "na", You can use the following IIF function to change NULL and "na" in 0, so you can calculate with it:

IIF(IsNumeric([ps_projectmgmt]), [ps_projectmgmt], 0)

Re: statement has a syntax error

Thanks Hans, it's really difficult to explain the problem I am having. For some reason when I add the dateSent field some output for AvgProjectMGMT and AvgDesign are weird numbers like 170 and 19. But it should be the average like 5 or 4.5 or ...) When I remove the dateSent field from my query everything works great. I just don;t understand why the dateSent field would mess things up. Could you please take look again? Thanks.

SELECT DataSource1.publisher, IIf(IsNull([CountProjectMGMT]),'n/a', ROUND(Val(DataSource1.SumProjectMGMT/DataSource2.CountProjectMGMT),1)) AS AvgProjectMGMT, IIf(IsNull([CountDesign]),'n/a', ROUND(Val(DataSource1.SumDesign/DataSource3.CountDesign),1)) AS AvgDesign FROM ((SELECT publisher, SUM(IIf(IsNull([ps_projectmgmt]) OR [ps_projectmgmt]='na',0, Val([ps_projectmgmt]))) AS SumProjectMGMT, SUM(IIf(IsNull([ps_design]) OR [ps_design]='na',0, Val([ps_design]))) AS SumDesign FROM  evaluations  GROUP BY publisher) AS DataSource1 LEFT OUTER JOIN (SELECT publisher, COUNT(ps_design) AS CountDesign FROM  evaluations AS Table_1_2 WHERE ((ps_design <> 'na') AND (pub='1') AND (dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#)) GROUP BY publisher) AS DataSource3 ON DataSource3.publisher = DataSource1.publisher) LEFT OUTER JOIN (SELECT  publisher, COUNT(ps_projectmgmt) AS CountProjectMGMT  FROM evaluations AS Table_1_1 WHERE ((ps_projectmgmt <> 'na') AND (pub='1') AND (dateSent >= #" + calendarTextBox1.Text + "# AND dateSent <= #" + calendarTextBox2.Text + "#)) GROUP BY publisher) AS DataSource2 ON DataSource2.publisher = DataSource1.publisher";