Query Types in Query Designer

I am just beginning to use SQL Server(MSDE), Access 2003 adp projects in a
web application I am developing. In using the query designer I am given the
option of developing a View or a Function in addition to a Stored Procedure.
I know about Stored Procedures but I have no information on when and how to
use Views or Functions. Can someone give me some insight on what
circumstances would cause you to select one query type over another and/or
point me to some documentation that will?
Through trial and error I have found that you can’t have a computed column
in a Function but you can in a View and you can sort in a Function but you
can’t in a View. I am investing quite a bit of effort into this project and
I don’t want to get down the road too far and find out that I have made some
basic mistake that will require substantial rework.
Any help you can give will be greatly appreciated.
Ray Cannon
EIS, Inc.
Access is generally not the best choice for web applications. It is
also not the best choice of development tools for a SQL Server
database. It is a better idea to purchase the Developer edition of SQL
Server ($49) that has the client tools and use it to create your SQL
Server objects. Use MSDE only when it comes time to deploy your
application. The license agreement for the Developer edition prohibits
its use in a production environment, but it has everything you need to
create, test and secure SQL Server databases. SQL Books Online is a
good source of information on creating SQL Server objects, as are
third-party books. You can download the latest version at
http://www.microsoft.com/sql/techinf...2000/books.asp
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Wed, 4 Aug 2004 12:15:04 -0700, "RayC"
<RayC@.discussions.microsoft.com> wrote:

> I am just beginning to use SQL Server(MSDE), Access 2003 adp projects in a
>web application I am developing. In using the query designer I am given the
>option of developing a View or a Function in addition to a Stored Procedure.
>I know about Stored Procedures but I have no information on when and how to
>use Views or Functions. Can someone give me some insight on what
>circumstances would cause you to select one query type over another and/or
>point me to some documentation that will?
>Through trial and error I have found that you cant have a computed column
>in a Function but you can in a View and you can sort in a Function but you
>cant in a View. I am investing quite a bit of effort into this project and
>I dont want to get down the road too far and find out that I have made some
>basic mistake that will require substantial rework.
>Any help you can give will be greatly appreciated.

query to parse out values from one column into different columns

I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.

For (hypothetical) example:

There is an existing table with following info in three columns:
userid record recordtag
1 joe 1
1 j 2
1 jr 3
2 bob 1
2 a 2
2 sr 3
where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)

I need to query these records for a report so it the output is:

userID firstname middleinitial suffix
1 joe j jr
2 bob a sr

What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.Look up CROSSTAB queries in Books Online.select userid,
max(case recordtag when 1 then record end) as firstname,
max(case recordtag when 2 then record end) as middleinitial,
max(case recordtag when 3 then record end) as suffix
from [YourTable]
group by userid|||Thanks for the info. I'll let you know how I do.|||I incorporated the crosstab query into my code and the performance is stellar. Thanks for your help. !!

query to list field data types

Can anyone tell me if there's a query command that will
return information about a table's properties, such as
field names, field data types, field lengths, index names,
etc?
For example, I'm looking for output that would show the
following, if we queried the Customers table in the MS
Northwind SQL DB:
CustomerID, nchar, 5, nulls=no
CompanyName, nvarchar, 40, nulls=no
ContactName, nvarchar, 30, nulls=yes
etc...
Thanks!!!
David
On Mon, 19 Apr 2004 10:04:25 -0700, David Hepburn III wrote:

>Can anyone tell me if there's a query command that will
>return information about a table's properties, such as
>field names, field data types, field lengths, index names,
>etc?
>For example, I'm looking for output that would show the
>following, if we queried the Customers table in the MS
>Northwind SQL DB:
>CustomerID, nchar, 5, nulls=no
>CompanyName, nvarchar, 40, nulls=no
>ContactName, nvarchar, 30, nulls=yes
>etc...
>Thanks!!!
>David
Hi David,
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers'
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi david,
you can make use of any of the following system stored procedures.
sp_columns <table_name> --to find column definition
sp_helpindex <table_name> --to find indexes
sp_help <table_name> complete table definition including indexes,pks/fks
etc.
Vishal
vgparkar@.yahoo.co.in

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John Jasper
John
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper
|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper
|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>
|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John JasperJohn
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:

> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
>
>

Query to display fiels names and types ?

Can anyone help me with a query which will display each filed name from a
table along with the type of field and length ? I have tried to google the
question with no luck
Thanks
John JasperJohn
select ordinal_position 'Seq',
cast(column_name as varchar(40)) 'Column',
isnull(character_maximum_length, numeric_precision) 'Size',
cast(data_type as varchar(12)) 'Type'
from information_schema.columns
where table_name = 'w_works'
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||Try this
DECLARE @.ID INT
SELECT @.ID = [id]
FROM [sysobjects]
WHERE [name] = 'Test'
SELECT A.[name], B.[name], A.[Length]
FROM [syscolumns] A
JOIN [systypes] B
ON A.[xtype ] = B.[xusertype]
WHERE A.[id] = @.ID
ORDER BY [colid]
--
Nik Marshall-Blank MCSD/MCDBA
"John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> Can anyone help me with a query which will display each filed name from a
> table along with the type of field and length ? I have tried to google
> the
> question with no luck
> Thanks
> John Jasper|||That did not work - did not get anything ((0 row(s) affected)
"Nik Marshall-Blank (delete fcom for my e" wrote:
> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> > Can anyone help me with a query which will display each filed name from a
> > table along with the type of field and length ? I have tried to google
> > the
> > question with no luck
> >
> > Thanks
> >
> > John Jasper
>
>|||Actually - that did work - I had originally used the name of the database
instead of a table - Thank-You
"Nik Marshall-Blank (delete fcom for my e" wrote:
> Try this
> DECLARE @.ID INT
> SELECT @.ID = [id]
> FROM [sysobjects]
> WHERE [name] = 'Test'
> SELECT A.[name], B.[name], A.[Length]
> FROM [syscolumns] A
> JOIN [systypes] B
> ON A.[xtype ] = B.[xusertype]
> WHERE A.[id] = @.ID
> ORDER BY [colid]
> --
> Nik Marshall-Blank MCSD/MCDBA
> "John Jasper" <JohnJasper@.discussions.microsoft.com> wrote in message
> news:FA9148E1-E210-47F9-BE92-FF16B6FC260B@.microsoft.com...
> > Can anyone help me with a query which will display each filed name from a
> > table along with the type of field and length ? I have tried to google
> > the
> > question with no luck
> >
> > Thanks
> >
> > John Jasper
>
>