For the SQL query, you have put the Max() function inside the Case
statement
instead of putting it outside; hence your problem.

tried it but all the same

may be i should use pivot ?

No, you must remove the NewTable.NewDate from the Group By but you can keep
the Year([NewTable].[NewDate])and the Month([NewTable].[NewDate]) in the
Group By.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Arejan" <areejan2000 (AT) yahoo (DOT) com> wrote

removing the NewTable.NewDate from the Group
Column 'NewTable.NewDate' is invalid in the select list because it is
not contained in either an aggregate function or the GROUP BY clause.

what would be the ideal query for row to column in sql 2000

Yes, it's invalid to remove it if you keep your current Select's list. If
you take a look, you have hidden its value inside a call to Max() in the
Access query:

Max(IIf([NewTable].[expid]
=100,Format([NewTable].[NewDate],'mm/dd/yyyy'),Null))

but you didn't do the same thing in your SQL query. Maybe there are other
errors as well, I didn't look.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Arejan" <areejan2000 (AT) yahoo (DOT) com> wrote