Convert Access Query w/IIF to SQL Server View

I am brand new to SQL Server. I had to move my data tables from Access 2003
onto SQL Server 2000. I have just upsized my .mdb using the upsizing wizard
and, after some minor changes, have gotten everything to work.
Now, I'm trying to focus on speeding things up. I have several nested
queries, using many tables, with several IIF statements that are used just
for selecting data to be displayed on forms and reports. I attempted to
convert one into a view, but have discovered that views don't allow IIF
statements. Is there a better way to do this? Can you create a procedure tha
t
is linked to Access? How do you reference this in Access?(c) Access' IIF translates to the CASE expression in SQL Server. See
http://www.aspfaq.com/2214 for this and other resources that should prove
handy.
(b) create your view using Query Analyzer; if you use Enterprise Mangler's
view editor, you won't be able to use CASE (among other problems).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:DB695ADD-7A5D-44A4-8417-D7E6A3C40277@.microsoft.com...
>I am brand new to SQL Server. I had to move my data tables from Access 2003
> onto SQL Server 2000. I have just upsized my .mdb using the upsizing
> wizard
> and, after some minor changes, have gotten everything to work.
> Now, I'm trying to focus on speeding things up. I have several nested
> queries, using many tables, with several IIF statements that are used just
> for selecting data to be displayed on forms and reports. I attempted to
> convert one into a view, but have discovered that views don't allow IIF
> statements. Is there a better way to do this? Can you create a procedure
> that
> is linked to Access? How do you reference this in Access?|||(c) Access' IIF translates to the CASE expression in SQL Server. See
http://www.aspfaq.com/2214 for this and other resources that should prove
handy.
(b) create your view using Query Analyzer; if you use Enterprise Mangler's
view editor, you won't be able to use CASE (among other problems).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:DB695ADD-7A5D-44A4-8417-D7E6A3C40277@.microsoft.com...
>I am brand new to SQL Server. I had to move my data tables from Access 2003
> onto SQL Server 2000. I have just upsized my .mdb using the upsizing
> wizard
> and, after some minor changes, have gotten everything to work.
> Now, I'm trying to focus on speeding things up. I have several nested
> queries, using many tables, with several IIF statements that are used just
> for selecting data to be displayed on forms and reports. I attempted to
> convert one into a view, but have discovered that views don't allow IIF
> statements. Is there a better way to do this? Can you create a procedure
> that
> is linked to Access? How do you reference this in Access?|||Thanks for the speedy response. Another question: If I create my view using
query analyzer, how do I save it as a query to link to Access?|||If you have a view in SQL Server, like:
CREATE VIEW dbo.MyView
AS
SELECT 1;
Then from Access you can just treat it like a table, e.g. SELECT * FROM
dbo.MyView instead of SELECT * FROM dbo.MyTable.
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:D6BF7042-48B7-4FC0-9EE2-D66026A461D7@.microsoft.com...
> Thanks for the speedy response. Another question: If I create my view
> using
> query analyzer, how do I save it as a query to link to Access?|||Sorry, I'm not following you.
Ok, using SQL Query Analyzer, I created my Select statement using the case
statements instead of IIF's, and parsing completed successfully. It runs and
selects the right information. Now what?
Do I have to save this somewhere special? Then what.|||You do not save it somewhere else. View is a server object, meaning it has
to be created on the SQL Server database (and saved there after creating
it).
"Holly" <Holly@.discussions.microsoft.com> wrote in message
news:C8C7BE9D-70BD-4BB3-BCA5-7D2ABFD4C3D2@.microsoft.com...
> Sorry, I'm not following you.
> Ok, using SQL Query Analyzer, I created my Select statement using the case
> statements instead of IIF's, and parsing completed successfully. It runs
> and
> selects the right information. Now what?
> Do I have to save this somewhere special? Then what.|||Hello
You can use a CASE expression instead of the IIF, using one of these
syntaxes:
CASE
WHEN condition
THEN result_if_true
ELSE result_if_false
END
or:
CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
..
ELSE other_result
END
For more informations about CASE, see Books Online.
Razvan|||Use Query Analyzer(QA) to transfer ALL the old data from Access to SQL.
Copy it to tables - or better yet use DTS package to copy entire database.
If you are using a view to call the data for reports, use QA to test and
create a stored procedure with the test script. Call the stored procedure
from your reports.
If you want to use the QA script on occasion and not for any so-called
recurring reports, then save the script written in QA to any location (just
liek a text script) and open up the script when you want to run it and run i
t
at will.
"Holly" wrote:

> Thanks for the speedy response. Another question: If I create my view usin
g
> query analyzer, how do I save it as a query to link to Access?|||On Thu, 1 Jun 2006 11:26:01 -0700, Holly wrote:

>Sorry, I'm not following you.
>Ok, using SQL Query Analyzer, I created my Select statement using the case
>statements instead of IIF's, and parsing completed successfully. It runs an
d
>selects the right information. Now what?
>Do I have to save this somewhere special? Then what.
Hi Holly,
If you have a SELECT statement that returns the data you need, e.g.
SELECT au_fname FROM authors
than you can create a view by typing a CREATE VIEW statement before it
and executing the complete code:
CREATE VIEW Author_FirstNames
AS
SELECT au_fname FROM authors
Once this has executed successfullym you can use the Authro_FirstName
view just as you would use any regular table - and that includes
creating a linked table for it in Access.
Hugo Kornelis, SQL Server MVP