Max elements in query grid pane.

[Dave Patrick]

Does anyone know of some limit? I have a table with three columns
[datestamp], [timestamp], [tons_per_hour] There is a record added at 15
minute intervals to the table. I rotated the table on the [timestamp] column
which means I'll always have 96 columns (one for each of the 15 minute
intervals of the day). I then wanted the user to be able to select a date
range and return with the averages for each of the 96 time intervals so I
assembled the SQL for this and pasted it into the SQL pane of a new query.
This work fine and returns the results correctly but I'm unable to view the
design grid for the query. (msaccess encountered errors and now needs to
close) BTW the source tables are ODBC linked to SQL Server 2000 using Access
2003

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[MGFoster]

Does anyone know of some limit? I have a table with three columns
[datestamp], [timestamp], [tons_per_hour] There is a record added at 15
minute intervals to the table. I rotated the table on the [timestamp] column
which means I'll always have 96 columns (one for each of the 15 minute
intervals of the day). I then wanted the user to be able to select a date
range and return with the averages for each of the 96 time intervals so I
assembled the SQL for this and pasted it into the SQL pane of a new query.
This work fine and returns the results correctly but I'm unable to view the
design grid for the query. (msaccess encountered errors and now needs to
close) BTW the source tables are ODBC linked to SQL Server 2000 using Access
2003

I saw this recently in the MS KB:

http://support.microsoft.com/kb/821809/

Perhaps, this will solve your problem.

===

I've written some SQL that the grid could not show, but the query worked
anyway. I just have to remember never to switch to the grid view on
those queries.

 

[Dave Patrick]

I'll take a look when I get back to work. Yes like I said it does work just
fine. Same here; when you join using <> no more grid view but that shouldn't
be the case here. Just wondering if there was a limit on columns in grid
view.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| I saw this recently in the MS KB:
|
| http://support.microsoft.com/kb/821809/
|
| Perhaps, this will solve your problem.
|
| ===
|
| I've written some SQL that the grid could not show, but the query worked
| anyway. I just have to remember never to switch to the grid view on
| those queries.
| --
| MGFoster:::mgf00 <at> earthlink <decimal-point> net
| Oakland, CA (USA)

 

[John Spencer (MVP)]

As far as I know the limit is 255 columns. But exceeding that gives you an
error message - at least, normally it does.

 

[Dave Patrick]

OK, thanks. Must be something else.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| As far as I know the limit is 255 columns. But exceeding that gives you
an
| error message - at least, normally it does.

 

[Van T. Dinh]

I wonder whether the problem has anything to do with the fact that
"timestamp" is a reserved word in ODBC???

I guess [timestamp] is a Field of datetime data type you created and not of
the timestamp data type?

 

[Dave Patrick]

Sorry, I just made these names up for illustration but yes they're datetime
data types.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I wonder whether the problem has anything to do with the fact that
| "timestamp" is a reserved word in ODBC???
|
| I guess [timestamp] is a Field of datetime data type you created and not
of
| the timestamp data type?
|
| --
| HTH
| Van T. Dinh
| MVP (Access)

 

[Van T. Dinh]

Just making sure: you are using Access CrossTab Query and not Pass-Through
Query?

 

[Dave Patrick]

Yes, thanks for asking about that. It was deceiving.

I'm using an Access CrossTab query. I didn't even try pass-through. I
assumed SQL server would not understand 'Transpose' and 'Pivot' Wouldn't I
need to install OLAP server for this?

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Just making sure: you are using Access CrossTab Query and not
Pass-Through
| Query?
|
| --
| HTH
| Van T. Dinh
| MVP (Access)

 

[Van T. Dinh]

T-SQL doesn't have keywords TRANSFORM or PIVOT but you can simulate a
Cross-Tab Report unsing T-SQL SELECT.

See B.O.L. on Cross-Tab Reports.

Just as a test: try to create the Cross-Tab Query using the QBE. Once it
works, switch to SQL View and compare the SQL of this test Cross-Tab Query
with the SQL you constructed. Hopefully, some differences will show up
between the 2 SQL Strings.

 

[Dave Patrick]

OK, thanks for the ideas. I'll give that a go. If nothing else it should
speed the process up a bit.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| T-SQL doesn't have keywords TRANSFORM or PIVOT but you can simulate a
| Cross-Tab Report unsing T-SQL SELECT.
|
| See B.O.L. on Cross-Tab Reports.
|
| Just as a test: try to create the Cross-Tab Query using the QBE. Once it
| works, switch to SQL View and compare the SQL of this test Cross-Tab Query
| with the SQL you constructed. Hopefully, some differences will show up
| between the 2 SQL Strings.
|
| --
| HTH
| Van T. Dinh
| MVP (Access)