Can I have an SQL view like in Access

  • Thread starter Thread starter M.Siler
  • Start date Start date
M

M.Siler

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.
 
Hi.
Can this be done in SQL 2005?
Click to expand...

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.
Click to expand...

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.
 
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?
 
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/...5D-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 said:
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 said:
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?
Click to expand...
Click to expand...
 
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.
 
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 said:
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/...5D-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 said:
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 said:
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?

in message 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.


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.
Click to expand...
Click to expand...
Click to expand...
 
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

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 said:
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/...5D-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 said:
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

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?

in message 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.


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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top