Can I have an SQL view like in Access

If you open the Northwind.mdb in access and look at tables Orders &
Employees you'll see that both contain "Employee ID", but when you view the
Orders table you don't see the Employee ID you see the associated Employee's
name. Can this be done in SQL 2005? When I view the Orders table I see a
whole list of Employee ID which makes it hard to know which employee is
associated to the Employee ID. I have a real world application but I'm only
use Northwind as an example as everyone has this database.I don't use SQL 2005 but I am sure you can by creating a View combining
tables Orders and Employees with the EmployeeID as the linking Field.
--
HTH
Van T. Dinh
MVP (Access)
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||Hi.
> Can this be done in SQL 2005?
Not on the table itself. And you don't want to, either. It causes huge
problems. For more information, please see the article, "The Evils of
Lookup Fields in Tables," on the following Web page:
http://www.mvps.org/access/lookupfields.htm
> When I view the Orders table I see a whole list of Employee ID which makes
> it hard to know which employee is associated to the Employee ID.
You shouldn't be viewing the raw data in the tables. That's where the data
is stored, but to glean meaningful information from the data, you need to
use queries, forms or reports. In a query, you can join the two tables and
select the employee's first name and last name to display for each record,
not the EmployeeID. Create a similar view in SQL Server.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||I can see the "evils" of a lookup field in a table. I've never created a
view in SQL 2005 before... do you have a site that I could reference that
would give me a 101 on creating SQL 2005 views?
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
> Hi.
>> Can this be done in SQL 2005?
> Not on the table itself. And you don't want to, either. It causes huge
> problems. For more information, please see the article, "The Evils of
> Lookup Fields in Tables," on the following Web page:
> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
> You shouldn't be viewing the raw data in the tables. That's where the
> data is stored, but to glean meaningful information from the data, you
> need to use queries, forms or reports. In a query, you can join the two
> tables and select the employee's first name and last name to display for
> each record, not the EmployeeID. Create a similar view in SQL Server.
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
> info.
>
> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know which
>> employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has this
>> database.
>|||A view is very simple to create. The basic syntax is
CREATE VIEW <viewname>
AS <select statement>
See these Books Online topics for the 101 information you need.
http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>I can see the "evils" of a lookup field in a table. I've never created a
>view in SQL 2005 before... do you have a site that I could reference that
>would give me a 101 on creating SQL 2005 views?
> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes huge
>> problems. For more information, please see the article, "The Evils of
>> Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the two
>> tables and select the employee's first name and last name to display for
>> each record, not the EmployeeID. Create a similar view in SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know
>> which employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has
>> this database.
>>
>|||One may also download the latest edition of SQL Server 2005 Books Online
(BOL), which also replaces previous editions of the SQL Server 2005 Express
BOL. Please see the following Web page for the download:
http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>A view is very simple to create. The basic syntax is
> CREATE VIEW <viewname>
> AS <select statement>
> See these Books Online topics for the 101 information you need.
> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes huge
>> problems. For more information, please see the article, "The Evils of
>> Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the two
>> tables and select the employee's first name and last name to display for
>> each record, not the EmployeeID. Create a similar view in SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you view
>> the Orders table you don't see the Employee ID you see the associated
>> Employee's name. Can this be done in SQL 2005? When I view the Orders
>> table I see a whole list of Employee ID which makes it hard to know
>> which employee is associated to the Employee ID. I have a real world
>> application but I'm only use Northwind as an example as everyone has
>> this database.
>>
>>
>|||Since users shouldn't be viewing tables or even queries, you can simply base
your forms on queries that join the necessary tables. Forms should ALWAYS
be based on queries with selection criteria when the back end is not Jet.
"M.Siler" <John.Doe@.NoSpam.com> wrote in message
news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
> If you open the Northwind.mdb in access and look at tables Orders &
> Employees you'll see that both contain "Employee ID", but when you view
> the Orders table you don't see the Employee ID you see the associated
> Employee's name. Can this be done in SQL 2005? When I view the Orders
> table I see a whole list of Employee ID which makes it hard to know which
> employee is associated to the Employee ID. I have a real world application
> but I'm only use Northwind as an example as everyone has this database.
>|||sure, you can easily write a select statement to do this.
in the select statemetn are the columns you want to "see". in the
where section are the data rules that tell the engine how to get to
that data.
Absolutely, for sure, you do NOT have to have the "where" stuff in the
upper "select" stuff.|||I got the view working nicely, but I can't update any of the data from the
view. Is there a way around this or this is how it should be working?
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
message news:OGwfqlgbGHA.4892@.TK2MSFTNGP02.phx.gbl...
> One may also download the latest edition of SQL Server 2005 Books Online
> (BOL), which also replaces previous editions of the SQL Server 2005
> Express BOL. Please see the following Web page for the download:
> http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
> HTH.
> Gunny
> See http://www.QBuilt.com for all your database needs.
> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
> info.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>>A view is very simple to create. The basic syntax is
>> CREATE VIEW <viewname>
>> AS <select statement>
>> See these Books Online topics for the 101 information you need.
>> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes
>> huge problems. For more information, please see the article, "The
>> Evils of Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the
>> two tables and select the employee's first name and last name to
>> display for each record, not the EmployeeID. Create a similar view in
>> SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you
>> view the Orders table you don't see the Employee ID you see the
>> associated Employee's name. Can this be done in SQL 2005? When I view
>> the Orders table I see a whole list of Employee ID which makes it hard
>> to know which employee is associated to the Employee ID. I have a real
>> world application but I'm only use Northwind as an example as everyone
>> has this database.
>>
>>
>>
>|||It depends on if it's an updatable view or not. The
conditions on when it's updatable or not are listed in books
online in the Create View topic. There is a section titled
Updatable Views.
-Sue
On Fri, 12 May 2006 13:15:39 -0400, "M.Siler"
<John.Doe@.NoSpam.com> wrote:
>I got the view working nicely, but I can't update any of the data from the
>view. Is there a way around this or this is how it should be working?
>
>"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote in
>message news:OGwfqlgbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>> One may also download the latest edition of SQL Server 2005 Books Online
>> (BOL), which also replaces previous editions of the SQL Server 2005
>> Express BOL. Please see the following Web page for the download:
>> http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
>> news:ezDtd8fbGHA.4892@.TK2MSFTNGP02.phx.gbl...
>>A view is very simple to create. The basic syntax is
>> CREATE VIEW <viewname>
>> AS <select statement>
>> See these Books Online topics for the 101 information you need.
>> http://msdn2.microsoft.com/en-us/library/ms190174(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms188250(SQL.90).aspx
>> http://msdn2.microsoft.com/en-us/library/ms187956(SQL.90).aspx
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:%23tWe5tfbGHA.5104@.TK2MSFTNGP03.phx.gbl...
>>I can see the "evils" of a lookup field in a table. I've never created a
>>view in SQL 2005 before... do you have a site that I could reference that
>>would give me a 101 on creating SQL 2005 views?
>> "'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@.Spameater.orgZERO_SPAM> wrote
>> in message news:uC3ybuWbGHA.3956@.TK2MSFTNGP04.phx.gbl...
>> Hi.
>> Can this be done in SQL 2005?
>> Not on the table itself. And you don't want to, either. It causes
>> huge problems. For more information, please see the article, "The
>> Evils of Lookup Fields in Tables," on the following Web page:
>> http://www.mvps.org/access/lookupfields.htm
>> When I view the Orders table I see a whole list of Employee ID which
>> makes it hard to know which employee is associated to the Employee ID.
>> You shouldn't be viewing the raw data in the tables. That's where the
>> data is stored, but to glean meaningful information from the data, you
>> need to use queries, forms or reports. In a query, you can join the
>> two tables and select the employee's first name and last name to
>> display for each record, not the EmployeeID. Create a similar view in
>> SQL Server.
>> HTH.
>> Gunny
>> See http://www.QBuilt.com for all your database needs.
>> See http://www.Access.QBuilt.com for Microsoft Access tips and
>> tutorials.
>> http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
>> info.
>>
>> "M.Siler" <John.Doe@.NoSpam.com> wrote in message
>> news:eDQUjhWbGHA.3364@.TK2MSFTNGP05.phx.gbl...
>> If you open the Northwind.mdb in access and look at tables Orders &
>> Employees you'll see that both contain "Employee ID", but when you
>> view the Orders table you don't see the Employee ID you see the
>> associated Employee's name. Can this be done in SQL 2005? When I view
>> the Orders table I see a whole list of Employee ID which makes it hard
>> to know which employee is associated to the Employee ID. I have a real
>> world application but I'm only use Northwind as an example as everyone
>> has this database.
>>
>>
>>
>>
>