Consolidation Query: 2012

Thursday, March 29, 2012

Convert an Access database to SQL

I've seen software that claims it can convert an Access database into SQL one. Does anyone know how this works? Does Microsoft have a solution for this
I've only used Access databases and need to develop one with more power. For developing all I need is the developers edition right? My hosting server does have SQL
Thanks for helping a SQL newbi
JoyceAccess has an Upsizing Wizard to migrate an Access database
to SQL Server - you can find information on this in the
Access help files. You can also use DTS (Data Transformation
Services) in SQL Server to migrate your Access data to SQL
Server.
For the situation you describe and just using SQL Server for
development, the developers edition would be fine.
-Sue
On Tue, 11 May 2004 18:51:02 -0700, jje
<jevans1@.verizon.net> wrote:
>I've seen software that claims it can convert an Access database into SQL one. Does anyone know how this works? Does Microsoft have a solution for this?
>I've only used Access databases and need to develop one with more power. For developing all I need is the developers edition right? My hosting server does have SQL.
>Thanks for helping a SQL newbie
>Joyce|||Thank you so much for the quick answer.|||Some other resources here, as well:
http://www.aspfaq.com/2182
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"jje" <jevans1@.verizon.net> wrote in message
news:78ADFC44-20D8-42B8-AEDF-7496B10BDCC2@.microsoft.com...
> I've seen software that claims it can convert an Access database into SQL
> one. Does anyone know how this works? Does Microsoft have a solution for
> this?
> I've only used Access databases and need to develop one with more power.
> For developing all I need is the developers edition right? My hosting
> server does have SQL.
> Thanks for helping a SQL newbie
> Joyce|||On Tue, 11 May 2004 18:51:02 -0700, jje <jevans1@.verizon.net> wrote:
>Thanks for helping a SQL newbie
Something to note just in case...Upsizing will only transfer your data
to SQL Server. You'll essentially have a front-end/back-end where you
use Access' Forms and Reports using SQL Server tables, stored
procedures and views.
Lerchsql sql

Convert an Access database to SQL

I've seen software that claims it can convert an Access database into SQL on
e. Does anyone know how this works? Does Microsoft have a solution for this?
I've only used Access databases and need to develop one with more power. For
developing all I need is the developers edition right? My hosting server do
es have SQL.
Thanks for helping a SQL newbie
JoyceAccess has an Upsizing Wizard to migrate an Access database
to SQL Server - you can find information on this in the
Access help files. You can also use DTS (Data Transformation
Services) in SQL Server to migrate your Access data to SQL
Server.
For the situation you describe and just using SQL Server for
development, the developers edition would be fine.
-Sue
On Tue, 11 May 2004 18:51:02 -0700, jje
<jevans1@.verizon.net> wrote:

>I've seen software that claims it can convert an Access database into SQL o
ne. Does anyone know how this works? Does Microsoft have a solution for this
?
>I've only used Access databases and need to develop one with more power. Fo
r developing all I need is the developers edition right? My hosting server d
oes have SQL.
>Thanks for helping a SQL newbie
>Joyce|||Thank you so much for the quick answer.|||Some other resources here, as well:
http://www.aspfaq.com/2182
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"jje" <jevans1@.verizon.net> wrote in message
news:78ADFC44-20D8-42B8-AEDF-7496B10BDCC2@.microsoft.com...
> I've seen software that claims it can convert an Access database into SQL
> one. Does anyone know how this works? Does Microsoft have a solution for
> this?
> I've only used Access databases and need to develop one with more power.
> For developing all I need is the developers edition right? My hosting
> server does have SQL.
> Thanks for helping a SQL newbie
> Joyce|||On Tue, 11 May 2004 18:51:02 -0700, jje <jevans1@.verizon.net> wrote:

>Thanks for helping a SQL newbie
Something to note just in case...Upsizing will only transfer your data
to SQL Server. You'll essentially have a front-end/back-end where you
use Access' Forms and Reports using SQL Server tables, stored
procedures and views.
Lerch

Convert AlphaNumeric to Numeric

Hello,

I have to convert a alpha numeric value to a numeric value using query.
Is there a way to do it.

The column data type is Varchar and I am storing alpha numeric values to it.
I have to sort the column now. when I say order by [column name] it is not comming properly.

Need some solution to do it.

Regards,
GowriShankar.

Quote:

Originally Posted by gowrishankar

Hello,

I have to convert a alpha numeric value to a numeric value using query.
Is there a way to do it.

The column data type is Varchar and I am storing alpha numeric values to it.
I have to sort the column now. when I say order by [column name] it is not comming properly.

Need some solution to do it.

Regards,
GowriShankar.

What do you mean by "alphanumeric value"? Do you have non-numeric chars in the column? If yes, how do you expect it to be converted to numeric? If no, then use "convert(int, ColumnName)" or "cast(ColumnName as int)" statements.
Please post examples|||

Quote:

Originally Posted by almaz

try like this
SELECT Description
FROM ModuleSetup
ORDER BY CAST(Description AS varchar)

convert all caps names to proper names

Is there a way in the report designer to format a field that contains names to display the name as a proper name rather than in all caps as stored in the db?

Thanks.

By proper name do you mean a name with just the first letter capitalized? You could write an expression for it. It would look something like this:

=iif(Fields!yourfield.Value.ToString.ToUpper = Fields!yourfield.Value.ToString, Fields!yourfield.Value.ToString.Chars(0).ToString & Fields!yourfield.Value.ToString.SubString(1, Fields!yourfield.Value.ToString.Length, Fields!yourfield.Value)

The expression assumes that the field will never be null and it will always be at least 2 characters long. If these aren't true it gets a little more complicated. Hopefully this puts you on the right track.

|||Due to the checks necessary, encapsulate this code into a function in the custom code section of the Report.

In Report Designer, add the following Function to the Code section of the Report properties dialog.

Public Function ToFirstUpper(str As String) As String
If (str = Nothing Or str.Length = 0)
Return String.Empty

If str.Length = 1 Then
Return str.ToUpper()
Else
Return str.Substring(0, 1).ToUpper() & str.Substring(1).ToLower()
End Function

This method is then called using the following expression.

=Code.ToFirstUpper(Fields!yourField.Value)

You can also do this directly in the SQL query. This example uses the SQL Server 2005 AdventureWorks database.

Select
UPPER(SUBSTRING(LastName, 1, 1)) + LOWER(SUBSTRING(LastName,2, LEN(LastName) - 1)) AS LastName
FROM Person.Contact

For more information on custom code:
Using Custom Code References in Expressions (Reporting Services)
How to: Add Code to a Report (Report Designer)|||If you want to use this function also outside reporting services take a look at:
http://vyaskn.tripod.com/code.htm#propercase

I haven't tried this sp yet, because I use Oracle as Datasource which has the function INITCAP..

so just rewrite your sql-statement:
select initcap(name), customerid from customers

or if you user MSSQL-Server as Source create the function in the link

Convert ADP to MDB

My company uses Microsoft ADP to manage and display data. I am the
Business Analyst for the division, my manager has asked me to import
the ADP and convert it to MDB, so that we can modify queries to
generate more reports.
To give a little background on the issue, we have a "Form" that
performs a search in the "Tables" using the queries, and outputs that
results into and calendar format in excel. All this has been hard-coded
into the VB code. Basically, the "Form" runs the code, which in turn
runs a "Module".
Some of the queries were encrypted so I had to go in and make sense
out of it, and build similar queries in MDB. I have imported all the
necessary tables, queries, modules, macros, and forms. Since it was
custom built, I am having difficulty debugging the errors... Is there
any basic stuff I should look at before I start troubleshooting'
Please help!!You'd probably want to post this to an Access forum.
-oj
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||I am bit confused.
ADP is merely a front-end app of SQL Server/MSDE and does not contain
tables, queries. It only has forms, reports, macros/vba code modules. If you
want to modify/create queries in ADP (they are actually Views, SPs, UDF in a
SQL Server database), you are actually directly working in a SQL Server
database.
If your goal is to switch the front end app from ADP to an mdb with linked
table (to the SQL Server back end), because of not cleared future of ADP,
then converting ADP to MDB may make some sense, otherwise, I do not see why
to do it.
Also, the way ADP connects to SQL Server (bound controls, especially) is a
bit different from the way mbd linked table. And it may also be quite
different how the server side queries (views, sps...) are used. IMO, one
must be good at both Access programming and SQL Server programming in order
to fulfill the converting, if it has to be converted.
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||Thanks for your reponse. I am not an expert on VB and SQL programming.
So I'm taking a different approach. To keep things simple, I have three
tables (in MDB), so I created a query that draws required information
from the tables and puts the data in a calendar format in 'Access
Report' (I made a calendar for July, with Text Boxes for each day of
the month).
Problem: The calendar displays only one record per page, it was
supposed to display all the records corresponding to the days of the
month in one page. Also, is there a way to display more than one record
in the 'Text box'?, as of now it only displays one record in each 'text
box' in each page.
Please help!!!!!!!!!!|||jyesbrown@.gmail.com wrote:
> Thanks for your reponse. I am not an expert on VB and SQL programming.
> So I'm taking a different approach. To keep things simple, I have three
> tables (in MDB), so I created a query that draws required information
> from the tables and puts the data in a calendar format in 'Access
> Report' (I made a calendar for July, with Text Boxes for each day of
> the month).
> Problem: The calendar displays only one record per page, it was
> supposed to display all the records corresponding to the days of the
> month in one page. Also, is there a way to display more than one record
> in the 'Text box'?, as of now it only displays one record in each 'text
> box' in each page.
> Please help!!!!!!!!!!
>
I think you need to seek help in one of the Access groups, these sound
like questions specific to Access, not SQL Server. It also sounds like
you're in over your head, why did you agree to do something like this
without having a clue how?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Convert ADP to MDB

convert adDBtimeStamp to datetime

Hello,
I use a SQL Server with ODBC driver and >NET C#.
I want to convert a date field with adDBtimeStamp format to a DATETIME
format, but no solution could be found till now.
My query is as follows:
SELECT received_at as EXPR1
FROM TTable
WHERE datepart(w

(datetime(received_at)))>10
Has anybody any solution for conversion?
*** Sent via Developersdex http://www.examnotes.net ***What is the SQL datatype of "received_at" and what are you attepting to
do with it? adDBtimeStamp maps to a DATETIME type in SQL Server so no
conversion should be necessary. However your example code isn't valid
in SQL Server - there is no WEEK or DATETIME function and your syntax
for DATEPART is wrong.
Maybe the following was what you intended, assuming you are in fact
dealing with a DATETIME column:
SELECT received_at AS expr1
FROM TTable
WHERE DATEPART(WEEK,received_at)>10 ;
David Portas
SQL Server MVP
--|||
It seems that no conversion is made and no explicit conversion could be
applied.
for the sequence:
SELECT received_at AS expr1
FROM TTable
WHERE DATEPART(WEEK,received_at)>10
the outcome is:
Driver]Expected lexical element not found: )
*** Sent via Developersdex http://www.examnotes.net ***|||Lucian,
If the adDBtimeStamp values are seen as strings of the
form 'yyyymmddhhmmss', try this:
declare @.t table (
adDBtimeStamp char(14)
)
insert into @.t values ('20051012171534')
select
convert(datetime,
substring(adDBtimeStamp,1,8) + space(1) +
substring(adDBtimeStamp,9,2) + ':' +
substring(adDBtimeStamp,11,2) + ':' +
substring(adDBtimeStamp,12,2),
112) as SQLdt
from @.t
Steve Kass
Drew University
Lucian Baltes wrote:

>Hello,
>I use a SQL Server with ODBC driver and >NET C#.
>I want to convert a date field with adDBtimeStamp format to a DATETIME
>format, but no solution could be found till now.
>My query is as follows:
>SELECT received_at as EXPR1
>FROM TTable
>WHERE datepart(w

(datetime(received_at)))>10
>Has anybody any solution for conversion?
>
>
>*** Sent via Developersdex http://www.examnotes.net ***
>

Convert Access to SQL 2000 Desktop

I have used the Upsizing Wizard in Access 2003 to convert a database to SQL. When I open it using SQL 2000 Desktop edition, I am finding a problem with the views and stored procedures.
When I open a query in design view, the tables are displayed in the upper pane without the details of the individual fields. All I get is a tick box next to *(All Columns)
If I create a new table, the same thing happens. If I create a new database, then make a table within it, any queries behave normally, with all the fields displayed.
I would be grateful for any ideasIf you are using the Access query designer, then checking the boxes next to the field names in the diagram pane will make the fields appear in the query grid.|||My problem is that there are no field names in the diagram pane - just a single line at the top of the table box which says *(All Columns). There is a single check box to the left of it, so I can have all columns or no columns. I can't pick the ones I want.
This "All Columns" would normally be at the top of the list of field names.
(How do I insert an image into this reply?)|||Post the SQL from the SQL pane for the view in question.

Convert Access to SQL

The following is my code for Access... can someone help me convert it to sql:

My Connectionstring is"server=(local);database=Database;trusted_connection=true"

@.PageLanguage="VB" %>

@.ImportNamespace="System.Data" %>

@.ImportNamespace="System.Data.OleDb" %>

scriptlanguage="VB"runat="server">Sub btnLogin_OnClick(SrcAsObject, EAs EventArgs)Dim myConnectionAs OleDbConnectionDim myCommandAs OleDbCommandDim intUserCountAsIntegerDim strSQLAsString

strSQL =

"SELECT COUNT(*) FROM tblLoginInfo " _

"WHERE username='" & Replace(txtUsername.Text,"'","''") &"' " _

"AND password='" & Replace(txtPassword.Text,"'","''") &"';"

myConnection =

New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _

"Data Source=" & Server.MapPath("login.mdb") &";")

myCommand =

New OleDbCommand(strSQL, myConnection)

myConnection.Open()

intUserCount = myCommand.ExecuteScalar()

myConnection.Close()

If intUserCount > 0Then

lblInvalid.Text =

FormsAuthentication.SetAuthCookie(txtUsername.Text,

True)

Response.Redirect(

"login_db-protected.aspx")Else

lblInvalid.Text =

"Sorry... try again..."EndIfEndSub

script>

Reformatted code:

<%@. Page Language="VB" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">

Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim intUserCount As Integer
Dim strSQL As String

strSQL = "SELECT COUNT(*) FROM tblLoginInfo " _
& "WHERE username='" & Replace(txtUsername.Text, "'", "''") & "' " _
& "AND password='" & Replace(txtPassword.Text, "'", "''") & "';"

myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & Server.MapPath("login.mdb") & ";")

myCommand = New OleDbCommand(strSQL, myConnection)

myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()

If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub

</script>

|||

The query language used by SQL(Transact-SQL) is very similar to which used by Access. There is no need to modify the sql command in your case. Just modify the connection string to point to your sql server:

<%@. Import Namespace="System.Data.SqlClient" %>

...

Dim myConnection As SqlConnection
Dim myCommand As SqlCommand

...
myConnection = New SqlConnection("Data Source=myServerName\SqlInstanceName; Integrated Security=SSPI; Database=mydb;")

You can visithttp://www.connectionstrings.com, or refer to MSDN:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

|||

Don't have to, but should:

<%@. Page Language="VB" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">

Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim intUserCount As Integer
Dim strSQL As String

strSQL = "SELECT COUNT(*) FROM tblLoginInfo WHEREusername=@.UserName AND password=@.Password"

myConnection = New SqlConnection({Your Sql Connection string})

myCommand = New SqlCommand(strSQL, myConnection)

myCommand.Parameters.add("@.UserName",sqldbtype.varchar).value=txtUserName.text

myCommand.Parameters.add("@.Password",sqldbtype.varchar).value=txtPassword.text

myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()

</script>

Convert Access SQL to ANSII compliant SQL

I don't want to sit here and re-engineer Access SQL to SQL Server ANSII all day. Is there a cool that will convert a sql statement if you past it in to ANSII? Or what other techniques can make my life easier in the process if you have any up your sleeve?

Also, is there anything in .NET or even SQL Server 2005 to help convert SQL statemetns from Access to SQL Server?

AFAIK, there are no tools to do this. If you however write your query using the ODBC canonical syntax then the ODBC/OLEDB driver can convert the syntax to the native data source automatically. Of course, this only applies to applications that you code. For example, Visual FoxPro allows you to create queries like this on the client-side that can be run against different data sources.sql sql

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

Convert Access Query to Transact SQL 2000 IIF to CASE

I need to convert a MS Access 2000 query into SQL Server 2000. Since
the IIF statements are not allowed in Transact-SQL, I 'm trying to
use the CASE Statement
I have two tables, Results and Sections. I need to import the section
records into the Results table. I tried to do a CASE statement, but so
far I cant' get the second IIF right.
In a nutshell, this is what this update query does:
Update the TotalScore in the tblResults table. If the tblResults.type
field is A, B, or C, then make it null. Otherwise, check if the
tblSections.MainScore1 field is null. If it is, then use the
tblResults.TotalScore value. If is not null, then use the
tblSections.MainScore1 value.
Here is the actual Access query syntax :
UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
[tblSections].TestID
SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
[Type]="C",Null,
IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
I have several other fields to update in this query, but they're very
similar to this one.
Any help would be appreciated it.
Thanks.let's see..
update tblResults
set TotalScore = case when r.type in ('A', 'B', 'C') then null else
coalesce(s.MainScore1, r.TotalScore) end
from tblResults r, tblSections s
where r.testID = s.testID
dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145560609.392893.303600@.e56g2000cwe.googlegroups.com...
>I need to convert a MS Access 2000 query into SQL Server 2000. Since
> the IIF statements are not allowed in Transact-SQL, I 'm trying to
> use the CASE Statement
> I have two tables, Results and Sections. I need to import the section
> records into the Results table. I tried to do a CASE statement, but so
> far I cant' get the second IIF right.
> In a nutshell, this is what this update query does:
> Update the TotalScore in the tblResults table. If the tblResults.type
> field is A, B, or C, then make it null. Otherwise, check if the
> tblSections.MainScore1 field is null. If it is, then use the
> tblResults.TotalScore value. If is not null, then use the
> tblSections.MainScore1 value.
> Here is the actual Access query syntax :
> UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
> [tblSections].TestID
> SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
> [Type]="C",Null,
> IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
>
> I have several other fields to update in this query, but they're very
> similar to this one.
> Any help would be appreciated it.
> Thanks.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can't use Access SQL syntax in SQL'r. Use Standard SQL syntax.:
UPDATE tblResults
SET TotalScore =
CASE WHEN [Type] IN ('A','B','C') THEN NULL
ELSE CASE WHEN MainScore1 IS NULL THEN TotalScore
ELSE (SELECT MainScore1 FROM tblSections
WHERE TestID = tblResults.TestID)
END
END
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBREfx1oechKqOuFEgEQLAkgCfZlAFT1+TbFfW
cP+zDhv5c5hCnqgAnAji
grqbw9lVWW3Mm+3fC54YuPJj
=Rv7/
--END PGP SIGNATURE--
ILCSP@.NETZERO.NET wrote:
> I need to convert a MS Access 2000 query into SQL Server 2000. Since
> the IIF statements are not allowed in Transact-SQL, I 'm trying to
> use the CASE Statement
> I have two tables, Results and Sections. I need to import the section
> records into the Results table. I tried to do a CASE statement, but so
> far I cant' get the second IIF right.
> In a nutshell, this is what this update query does:
> Update the TotalScore in the tblResults table. If the tblResults.type
> field is A, B, or C, then make it null. Otherwise, check if the
> tblSections.MainScore1 field is null. If it is, then use the
> tblResults.TotalScore value. If is not null, then use the
> tblSections.MainScore1 value.
> Here is the actual Access query syntax :
> UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
> [tblSections].TestID
> SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
> [Type]="C",Null,
> IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
>
> I have several other fields to update in this query, but they're very
> similar to this one.|||Hello Guys, thanks for replying. I've been trying both suggestions and
both give me the same results. I'm able to get some of the total
scores, but not all of them.
I can get some of the total scores for not all of them. My guess is
that the error happens when the query is evaluating for null or perhaps
the declared join for the 2 tables is wrong.
the Table tblResults has 1 instance of TestID. The Table tblSections
has several instances of a TestID and they can have something in the
MainScore1 or be Null.
Here's an example of the tblSections table. In this example, I'm able
to get the 43 TotalScore for the 214371148 ID, but not for the other 2.
When running the old Access 2000 update query, I do get the 3
TotalScores.
Type TestID MainScore1
M 214209195 58
L 214371148 43
U 214217823 74
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823|||Hi,
Could you please post the DDL for the tables in question, INSERTs for the
sample data, and the expected results?
Thanks
Dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145640771.510008.142980@.v46g2000cwv.googlegroups.com...
> Hello Guys, thanks for replying. I've been trying both suggestions and
> both give me the same results. I'm able to get some of the total
> scores, but not all of them.
> I can get some of the total scores for not all of them. My guess is
> that the error happens when the query is evaluating for null or perhaps
> the declared join for the 2 tables is wrong.
> the Table tblResults has 1 instance of TestID. The Table tblSections
> has several instances of a TestID and they can have something in the
> MainScore1 or be Null.
> Here's an example of the tblSections table. In this example, I'm able
> to get the 43 TotalScore for the 214371148 ID, but not for the other 2.
> When running the old Access 2000 update query, I do get the 3
> TotalScores.
> Type TestID MainScore1
> M 214209195 58
> L 214371148 43
> U 214217823 74
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
>|||Here's the DDLs
CREATE TABLE [dbo].[tblResults] (
[InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestDate] [datetime] NULL ,
[TotalScore] [float] NULL ,
[MainScore1] [float] NULL ,
[MainStatus1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainScore2] [float] NULL ,
[MainStatus2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainScore3] [float] NULL ,
[MainStatus3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Sec1c] [float] NULL ,
[Sec1i] [float] NULL ,
[Sec2c] [float] NULL ,
[Sec2i] [float] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblSections] (
[InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NULL ,
[MainScore1] [float] NULL ,
[MainScore2] [float] NULL ,
[MainScore3] [float] NULL ,
[MainStatus1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainStatus2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainStatus3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Sec1c] [float] NULL ,
[Sec1i] [float] NULL ,
[Sec2c] [float] NULL ,
[Sec2i] [float] NULL
) ON [PRIMARY]
GO
Since I already posted a short example of the tblSections table, here's
the one for the tblResults table
TestID SSN ExamType TotalScore
214209195 123456789 M
214371148 987654321 L
214398090 234567890 B
214398102 098765432 A
214217823 590380445 U
214430778 567890123 C
Based on the update query, I should get the totalscore for the records
with the TestID: 214209195, 214371148, and the 214217823. However, I
only get the results for the 214371148. The other 2 total scores are
still null.
Hope this helps.|||Hi,
Thanks for the DDL. And if you've posted the actual INSERT script, would it
look something like this?
insert tblResults (TestID, SSN, Type)
select
214209195, '123456789', 'M'
union all
select
214371148, '987654321', 'L'
union all
select
214398090, '234567890', 'B'
union all
select
214398102, '098765432', 'A'
union all
select
214217823, '590380445', 'U'
union all
select
214430778, '567890123', 'C'
insert tblSections (TestID, MainScore1)
select
214209195, 58
union all
select
214371148, 43
union all
select
214217823, 74
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
If so, the update I posted yesterday should give the expected result. Once
again:
update tblResults
set TotalScore = case when r.type in ('A', 'B', 'C') then null else
coalesce(s.MainScore1, r.TotalScore) end
from tblResults r, tblSections s
where r.testID = s.testID
select TestId, TotalScore from tblResults
TestId TotalScore
-- ----
214209195 58.0
214371148 43.0
214398090 NULL
214398102 NULL
214217823 74.0
214430778 NULL
(6 row(s) affected)
Dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145644726.162771.207080@.g10g2000cwb.googlegroups.com...
> Here's the DDLs
> CREATE TABLE [dbo].[tblResults] (
> [InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
> [TestID] [int] NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TestDate] [datetime] NULL ,
> [TotalScore] [float] NULL ,
> [MainScore1] [float] NULL ,
> [MainStatus1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainScore2] [float] NULL ,
> [MainStatus2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainScore3] [float] NULL ,
> [MainStatus3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Sec1c] [float] NULL ,
> [Sec1i] [float] NULL ,
> [Sec2c] [float] NULL ,
> [Sec2i] [float] NULL
> ) ON [PRIMARY]
>
>
> CREATE TABLE [dbo].[tblSections] (
> [InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
> [TestID] [int] NULL ,
> [MainScore1] [float] NULL ,
> [MainScore2] [float] NULL ,
> [MainScore3] [float] NULL ,
> [MainStatus1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainStatus2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainStatus3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Sec1c] [float] NULL ,
> [Sec1i] [float] NULL ,
> [Sec2c] [float] NULL ,
> [Sec2i] [float] NULL
> ) ON [PRIMARY]
> GO
>
> Since I already posted a short example of the tblSections table, here's
> the one for the tblResults table
> TestID SSN ExamType TotalScore
> 214209195 123456789 M
> 214371148 987654321 L
> 214398090 234567890 B
> 214398102 098765432 A
> 214217823 590380445 U
> 214430778 567890123 C
>
> Based on the update query, I should get the totalscore for the records
> with the TestID: 214209195, 214371148, and the 214217823. However, I
> only get the results for the 214371148. The other 2 total scores are
> still null.
> Hope this helps.
>

Convert Access query to SQL View

Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
SenthilkumarCheck out CONVERT() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Senthilkumar" <kesk32@.yahoo.co.in> wrote in message
news:%234%232fJmJGHA.1388@.TK2MSFTNGP11.phx.gbl...
Hi,
I have used an access table to create a chart report to show how many calls
were handled in a month. How do i conver the Access sql to MS sql server
view. Here is the access sql:
SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
FROM [Complaints]
GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
(Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
In the MS Sql, the word FORMAT is not recognised. I have tried several ways
like this w/o any luck. Can anyone tell me the correct syntax for the above
sql querry.
Thanks
Senthilkumar|||To add to Tom's reply, you might want to consider leaving off the
formatting in the view and just return the raw data. Let the client
format the result set. Any time you format the data on the server, the
formatting functions you use have to operate on every single row, one
at a time.
--Mary
On Tue, 31 Jan 2006 18:09:30 +0530, "Senthilkumar"
<kesk32@.yahoo.co.in> wrote:

>Hi,
>I have used an access table to create a chart report to show how many calls
>were handled in a month. How do i conver the Access sql to MS sql server
>view. Here is the access sql:
>SELECT (Format([ComplaintDate],"mmm"" '""yy")) AS Expr1, Count(*) AS [Count]
>FROM [Complaints]
>GROUP BY (Format([ComplaintDate],"mmm"" '""yy")),
> (Year([ComplaintDate])*12+Month([Complai
ntDate])-1)
>ORDER BY (Year([ComplaintDate])*12+Month([Complai
ntDate])-1);
>In the MS Sql, the word FORMAT is not recognised. I have tried several ways
>like this w/o any luck. Can anyone tell me the correct syntax for the above
>sql querry.
>Thanks
>Senthilkumar
>

Convert Access Query to SQL Server View

SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
UCASE(Buildings.BuildingName) AS BuildingName,
Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
dCoveredUnenclosedGrossArea,
IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
Buildings.YearConstructed, Format$([NumberLevels],"00") AS
dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
dUnrelatedGrossArea, Buildings.YearLatestImprovement,
UCASE(Buildings.Address) AS Address, Buildings.CityCode,
CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
Format$([Mechanical],"0000000") AS dMechanical,
Format$([Custodial],"0000000") AS dCustodial
FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode
ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;

Please if anyone can help me in Converting the above given Access Query
to Sql Server. I don't know which function to use for format$, IIF. I
would really appreciate your suggestions.

Thanks,On 17 May 2006 10:02:37 -0700, s_wadhwa@.berkeley.edu wrote:

>SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
>UCASE(Buildings.BuildingName) AS BuildingName,
>Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
>Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
>Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
>dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
>dCoveredUnenclosedGrossArea,
>IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
>Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
>Buildings.YearConstructed, Format$([NumberLevels],"00") AS
>dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
>dUnrelatedGrossArea, Buildings.YearLatestImprovement,
>UCASE(Buildings.Address) AS Address, Buildings.CityCode,
>CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
>Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
>Format$([Mechanical],"0000000") AS dMechanical,
>Format$([Custodial],"0000000") AS dCustodial
>FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
>Buildings.CityCode
>ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;
>
>Please if anyone can help me in Converting the above given Access Query
>to Sql Server. I don't know which function to use for format$, IIF. I
>would really appreciate your suggestions.
>Thanks,

Hi s_wadhwa,

Change doouble quotes to single quotes.

Use + instead of & for string concatenation.

Replace UCASE() with UPPER()

Replace IIf with CASE (look it up in Books Online)

My knowledge of Format$() is very llimited, but if (for isntance) the
function Format$([Custodial],"0000000") is intended to format Custodial
as a 7-digit numeric string with leading zerooes (e.g. 0001234), then
you can replace it with:
RIGHT (REPLICATE('0', 7) + STR(Custodial), 7)

Finally, remove the constant from the ORDER BY list. It serves no
purpose.

--
Hugo Kornelis, SQL Server MVP|||Hi Hugo,
Thanks a lot for your quick response. the suggestions were really
helpful. Is there any book to refer or some online material to refer
to. I have lots of other doubts regarding converting Access queries to
SQL.

Thanks,
Shalini|||Hi,

If anyone has done the conversion of parameterized query in Access to
SQL Server. Please guide me how to solve this query and how to take
input in SQL query from Access forms.

PARAMETERS [[forms]!frmRooms![BuildingNumber]] Text ( 255 ),
[[forms]!frmRooms![ctlFloor]] Text ( 255 ),
[[forms]!frmRooms![DepartmentFilter]] Text ( 255 );
SELECT *
FROM Rooms
WHERE (((Rooms.BuildingNumber)=[forms]![frmRooms]![BuildingNumber]) AND
((Rooms.Floor) Like [forms]![frmRooms]![ctlFloor]) AND
((Rooms.DepartmentCode) Like
Mid$([forms]![frmRooms]![DepartmentFilter],1,4)) AND
((Rooms.RoomUseCode) Like [forms]![frmRooms]![RmCdFilter]))
ORDER BY Rooms.BuildingNumber, Rooms.RoomNumber;

thanks in advance for your help.
Shalini|||On 18 May 2006 12:59:23 -0700, s_wadhwa@.berkeley.edu wrote:

>Hi,
>If anyone has done the conversion of parameterized query in Access to
>SQL Server. Please guide me how to solve this query and how to take
>input in SQL query from Access forms.

Hi Shalini,

I see that your parameters refer to forms. Keep in mind that SQL Server
is not aware of the forms in yoour front-end - a big difference from
Access, which is DB and front-end lumped together in one application.

One possible way to convert this to SQL Server would be to create a
stored procedure:

CREATE PROC GoodNameGoesHere
(@.BuildingNumber varchar(255),
@.ctlFloor varchar(255),
@.DepartmentFilter varchar(255))
AS
SELECT Col1, Col2, Col3, ...-- Never use SELECT * in production code
FROM Rooms
WHERE BuildingNumber = @.BuildingNumber
AND "Floor" LIKE @.ctlFloor
AND DepartmentCode LIKE SUBSTRING(@.DepartmentFilter, 1, 4)
ORDER BY BuildingNumber, RoomNumber;

BTW, using a char datatype for a column named "Number" is highly
suspicious to me...

--
Hugo Kornelis, SQL Server MVP|||Hi there,

I have an Access query that I've been trying to convert to SQL Server
view and although I've converted most of the syntax from access to sql
I still can't make it work. I'm getting errors within GROUP BY and
HAVING clauses. Anyone has an idea? Thanks...

SELECT DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()) AS Expr1,
GetDate()-O_DateOfAddress AS Expr3,
DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress) AS
Expr2,
qry_offender_master.O_VerificationQuarter,
qry_offender_master.O_Probation,
qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
qry_offender_master.O_CityEntry, qry_offender_master.O_State,
qry_offender_master.O_Zip, qry_offender_master.O_Sector,
tbl_state.State,
qry_offender_master.Ca_WarrantIssued,
qry_offender_master.O_DateofBirth,
qry_offender_master.O_Absconder
FROM qry_offender_master LEFT JOIN tbl_state ON
qry_offender_master.O_State = tbl_state.State
GROUP BY DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()),
GetDate()-O_DateOfAddress,
DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress),
qry_offender_master.O_VerificationQuarter,
qry_offender_master.O_Probation,
qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
qry_offender_master.O_CityEntry, qry_offender_master.O_State,
qry_offender_master.O_Zip, qry_offender_master.O_Sector,
tbl_state.State,
qry_offender_master.Ca_WarrantIssued,
qry_offender_master.O_DateofBirth,
qry_offender_master.O_Absconder
HAVING (((DateName(qq,O_DateofAddress) + '/' +
DateName(yy,O_DateofAddress)) <> DateName(qq,GetDate()) + '/' +
DateName(yy,GetDate())) AND
((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
AND ((qry_offender_master.O_Probation)=0) AND
((qry_offender_master.O_OutOfCounty)=0) AND
((qry_offender_master.O_deceased)=0) AND
((qry_offender_master.O_Sex_Probation)=0) AND
((qry_offender_master.O_Absconder)=0)) OR
(((DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress))<>
DateName(qq,GetDate()) + '/' + DateName(yy,GetDate())) AND
((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
AND ((qry_offender_master.O_Probation)=0) AND
((qry_offender_master.O_OutOfCounty)=0) AND
((qry_offender_master.O_deceased)=0) AND
((qry_offender_master.O_Sex_Probation)=0) AND
((qry_offender_master.O_DateofAddress) Is Null) AND
((qry_offender_master.O_Absconder)=0))
ORDER BY qry_offender_master.O_LastName

Hugo Kornelis wrote:
> On 18 May 2006 12:59:23 -0700, s_wadhwa@.berkeley.edu wrote:
> >Hi,
> >If anyone has done the conversion of parameterized query in Access to
> >SQL Server. Please guide me how to solve this query and how to take
> >input in SQL query from Access forms.
> Hi Shalini,
> I see that your parameters refer to forms. Keep in mind that SQL Server
> is not aware of the forms in yoour front-end - a big difference from
> Access, which is DB and front-end lumped together in one application.
> One possible way to convert this to SQL Server would be to create a
> stored procedure:
> CREATE PROC GoodNameGoesHere
> (@.BuildingNumber varchar(255),
> @.ctlFloor varchar(255),
> @.DepartmentFilter varchar(255))
> AS
> SELECT Col1, Col2, Col3, ...-- Never use SELECT * in production code
> FROM Rooms
> WHERE BuildingNumber = @.BuildingNumber
> AND "Floor" LIKE @.ctlFloor
> AND DepartmentCode LIKE SUBSTRING(@.DepartmentFilter, 1, 4)
> ORDER BY BuildingNumber, RoomNumber;
> BTW, using a char datatype for a column named "Number" is highly
> suspicious to me...
> --
> Hugo Kornelis, SQL Server MVP|||Ok, I've changed "GetDate()-O_DateOfAddress" to DateDiff(day,
O_DateOfAddress, GetDate()) and the only thing left has to do with
group by clause. So, is there any way I can use expressions (Expr1,
Expr2...) in GROUP By clause because obviously this is possible in
access but not in sql server...I tried both putting "Group by Expr1,
Expr2" and "Group by DateName(qq,GetDate()) + '/' +
DateName(yy,GetDate()) AS Expr1, GetDate()-O_DateOfAddress AS Expr3..."
but no success? Thanks in advance...|||thanks for guidance.
i resolved that issue.

shalini|||On 23 May 2006 05:51:47 -0700, lakimaki wrote:

>Hi there,
>I have an Access query that I've been trying to convert to SQL Server
>view and although I've converted most of the syntax from access to sql
>I still can't make it work. I'm getting errors within GROUP BY and
>HAVING clauses. Anyone has an idea? Thanks...
(snip)

Hi lakimaki,

There are several things in yoour query that I don't understand.

1. Why are you joining in the tblState table? Unless I am missing
something, you only use it to display the tblState.State column, which
is also the joining column. Why not display O_State and remove the join
to tblState?

2. Why do you need the GROUP BY clause? Since you include all columns in
the WHERE clause and since there are no aggregates used anywhere, this
is the same as just using DISTINCT to remove duplicates - and if your
table is well designed, there shouldn't be any duplicates. Just remove
the entire GROUP BY and change HAVING to WHERE.

3. The HAVING part looks strange too. It consists of two parts, combined
with OR. But the second part is an exact copy of the first part, with
only one extra requirement added. And that extra requirement has the
result that this second part can NEVER be true - since DateOfAddress can
never be both NULL and in the current quarter.

4. The test that DateOfAddress is in the current quarter is not done in
the most efficient way. Instead of using an expression to extract
quarter and year from this column, you shoould compare this column to
the start of the current and the next quarter - that way, an index on
DateOfAddress (if any exists) can be used to quickly narrow down the
amount of rows to process.

As far as I see, you could rewrite your query to:

SELECT DATENAME(qq, CURRENT_TIMESTAMP) + '/'
+ DATENAME(yy, CURRENT_TIMESTAMP) AS Expr1,
DATEDIFF(day, O_DateOfAddress, CURRENT_TIMESTAMP) AS Expr3,
DATENAME(qq, O_DateofAddress) + '/'
+ DATENAME(yy, O_DateofAddress) AS Expr2,
O_VerificationQuarter,
O_Probation,
O_active_case,
O_OutOfCounty,
O_deceased,
O_Sex_Probation,
O_DateofAddress,
O_Doc,
O_LastName,
O_FirstName,
O_MiddleName,
O_Address,
O_CityEntry,
O_State,
O_Zip,
O_Sector,
O_State,
Ca_WarrantIssued,
O_DateofBirth,
O_Absconder
FROM qry_offender_master
WHERE O_DateOfAddress >= DATEADD(qq,
DATEDIFF(qq, '20000101',
CURRENT_TIMESTAMP),
'20000101')
AND O_DateOfAddress < DATEADD(qq,
DATEDIFF(qq, '20000101',
CURRENT_TIMESTAMP),
'20000401')
AND O_VerificationQuarter = DATENAME(qq, CURRENT_TIMESTAMP)
AND O_Probation = 0
AND O_OutOfCounty = 0
AND O_deceased = 0
AND O_Sex_Probation = 0
AND O_Absconder = 0
ORDER BY O_LastName

(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)

>SELECT DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()) AS Expr1,
>GetDate()-O_DateOfAddress AS Expr3,
>DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress) AS
>Expr2,
>qry_offender_master.O_VerificationQuarter,
>qry_offender_master.O_Probation,
>qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
>qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
>qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
>qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
>qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
>qry_offender_master.O_CityEntry, qry_offender_master.O_State,
>qry_offender_master.O_Zip, qry_offender_master.O_Sector,
>tbl_state.State,
>qry_offender_master.Ca_WarrantIssued,
>qry_offender_master.O_DateofBirth,
>qry_offender_master.O_Absconder
>FROM qry_offender_master LEFT JOIN tbl_state ON
>qry_offender_master.O_State = tbl_state.State
>GROUP BY DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()),
>GetDate()-O_DateOfAddress,
>DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress),
>qry_offender_master.O_VerificationQuarter,
>qry_offender_master.O_Probation,
>qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
>qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
>qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
>qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
>qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
>qry_offender_master.O_CityEntry, qry_offender_master.O_State,
>qry_offender_master.O_Zip, qry_offender_master.O_Sector,
>tbl_state.State,
>qry_offender_master.Ca_WarrantIssued,
>qry_offender_master.O_DateofBirth,
>qry_offender_master.O_Absconder
>HAVING (((DateName(qq,O_DateofAddress) + '/' +
>DateName(yy,O_DateofAddress)) <> DateName(qq,GetDate()) + '/' +
>DateName(yy,GetDate())) AND
>((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
>AND ((qry_offender_master.O_Probation)=0) AND
>((qry_offender_master.O_OutOfCounty)=0) AND
>((qry_offender_master.O_deceased)=0) AND
>((qry_offender_master.O_Sex_Probation)=0) AND
>((qry_offender_master.O_Absconder)=0)) OR
>(((DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress))<>
>DateName(qq,GetDate()) + '/' + DateName(yy,GetDate())) AND
>((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
>AND ((qry_offender_master.O_Probation)=0) AND
>((qry_offender_master.O_OutOfCounty)=0) AND
>((qry_offender_master.O_deceased)=0) AND
>((qry_offender_master.O_Sex_Probation)=0) AND
>((qry_offender_master.O_DateofAddress) Is Null) AND
>((qry_offender_master.O_Absconder)=0))
>ORDER BY qry_offender_master.O_LastName
>
>Hugo Kornelis wrote:
>> On 18 May 2006 12:59:23 -0700, s_wadhwa@.berkeley.edu wrote:
>>
>> >Hi,
>>> >If anyone has done the conversion of parameterized query in Access to
>> >SQL Server. Please guide me how to solve this query and how to take
>> >input in SQL query from Access forms.
>>
>> Hi Shalini,
>>
>> I see that your parameters refer to forms. Keep in mind that SQL Server
>> is not aware of the forms in yoour front-end - a big difference from
>> Access, which is DB and front-end lumped together in one application.
>>
>> One possible way to convert this to SQL Server would be to create a
>> stored procedure:
>>
>> CREATE PROC GoodNameGoesHere
>> (@.BuildingNumber varchar(255),
>> @.ctlFloor varchar(255),
>> @.DepartmentFilter varchar(255))
>> AS
>> SELECT Col1, Col2, Col3, ...-- Never use SELECT * in production code
>> FROM Rooms
>> WHERE BuildingNumber = @.BuildingNumber
>> AND "Floor" LIKE @.ctlFloor
>> AND DepartmentCode LIKE SUBSTRING(@.DepartmentFilter, 1, 4)
>> ORDER BY BuildingNumber, RoomNumber;
>>
>> BTW, using a char datatype for a column named "Number" is highly
>> suspicious to me...
>>
>> --
>> Hugo Kornelis, SQL Server MVP

--
Hugo Kornelis, SQL Server MVP

Convert Access query to sql

how to convert this access query to sql query

IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2

SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1

IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name

please......

case

when [Total Bunches] > 0 then Production * 1000 / [Total Bunches]

else 0

end

as Name2,

SUM(

case

when BlockInYield = -1 then [SIZE]

else 0

end

)

as Name1,

case when BlockInYield = 1 then (case when TC_M > 0 then TC_M else TC_DENS *[SIZE] end)

else 0

end

as Name

Thanks

Naras

|||There is no IFF operator in TSQL; as Naras is indicating the way to approach converting an IFF statement is to use an abstraction using CASE statements.sql sql

Convert Access into SQL server express

Sorry if this has been answered before. Could not find any answers.

OS Vista Business, SQL Server 2005 Express.

I have a CSV file which I imported to Access only 100 records with 8 fields. Then ran Tools >> Database Utilities >> Upsizing Wisard. The result was I exported the table field headers but not the data. Is there any method on how I can get the field headers and data into SQL Server Express?

Use the Upsize Structure AND Data option. Personally, I found this migration product to be a bit better than the one built in to Access:http://www.microsoft.com/downloads/details.aspx?FamilyId=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en

|||

Mike, you cannot believe the amount of hours I spent googling looking for an answer and you have supplied the perfect soultion. Thank you greatly. I don't know why I missed finding this option. It does exactly what I have been looking for and so easy to use.

regards

convert access IIF to sql in order by statement

ive been having a lot of trouble converting this last line from my access database to sql server. i was hoping someone could help
order by Name, IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
any suggestions?I;m not sure I got the IIF true/False right...I went with IIF(expr, true, false)...if it's backwards just flip it in the code...

ORDER BY Name
-- , IIF(Risk.Calc like H*, 1, IIF (Risk.Calc like M*,2,3)), Risk.Chance*risk.cons DESC, risk.title
, CASE WHEN Risk.Calc LIKE 'H%' THEN 1
WHEN Riks.Calc LIKE 'M%' THEN 2
ELSE 3
END
, Risk.Tile|||i didnt think you could use IIF in sql|||I think brett was just reiterating your iif and submitting a case example as the replacement.|||yea thats what i thought after i looked at it, thanks for the help brett|||The "--" indicate that it's a comment line...good luck..

did it work?

Convert Access Function to SQL

I'm going crazy trying to convert an Access Function to SQL.
From what I've read, it has to be done as a stored procedure.
I'm trying to take a field that is "minutes.seconds" and convert it to minutes.

This is what I have in Access:

Function ConvertToTime (myAnswer As Variant)
Dim myMinutes
myMinutes-(((((myAnswer * 100)Mod 100/100/0.6)+(CInt(myAnswer-0.4))))
ConvertToTime =(myMinutes)
End Function

When I tried to modify it in SQL:

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTime
Function ConvertToTime(myAnswer As Variant)
Dim myMinutes
myMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))
ConvertToTime=(myMinutes)
End

I get an error after ConverToTime.Transact-SQL is not VB!

If you are using SQL2000 you can create a user-defined function:

CREATE FUNCTION dbo.ConvertToMinutes (@.minsec DECIMAL(5,2))
RETURNS DECIMAL(5,2)
BEGIN
RETURN ROUND(@.minsec,0,1)+(@.minsec-ROUND(@.minsec,0,1))*10/6
END

SELECT dbo.ConvertToMinutes(100.30)

Result:

---
-100.50

(1 row(s) affected)

--
David Portas
----
Please reply only to the newsgroup
--|||Mich wrote:
> CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS ConvertToTime
> Function ConvertToTime(myAnswer As Variant)
> Dim myMinutes
> myMinutes = (((((myAnswer * 100)Mod 100)/100/0.6)+9CInt(myAnswer-0.4))))
> ConvertToTime=(myMinutes)
> End
T-SQL uses "Return <value>" for functions, like C or Java, not
"<Function Name> = <value>", like VB (including Access) or Pascal.

Bill

convert Access DB to MS SQL with dtswiz(command line)

Hi!

I' m sitting here now for days and I still get no conclusion on my
problem. I have an MS ACCESS .mdb File and I would like to export it
with dtswiz.exe (from MS SQL Utility Tools) via command line. (If you
know other tools which are able to do that via command line I would
appreciate any suggestion)

I tried to find another Provider Parameter (like MSDASQL) for Access
but somehow it seems to me that Microsoft only got one. I' m also able
to address a Server which appears in the GUI but I don t need one for
my ACCess DB (I only tried, if the command parameters have any effect
on the converter).

I would be glad if someone could help me on this.

Thank you
Klaus Baumgarten"Klaus Baumgarten" <klaus.baumgarten@.gmx.net> wrote in message
news:c562e80d.0405110819.6eb845bc@.posting.google.c om...
> Hi!
> I' m sitting here now for days and I still get no conclusion on my
> problem. I have an MS ACCESS .mdb File and I would like to export it
> with dtswiz.exe (from MS SQL Utility Tools) via command line. (If you
> know other tools which are able to do that via command line I would
> appreciate any suggestion)
> I tried to find another Provider Parameter (like MSDASQL) for Access
> but somehow it seems to me that Microsoft only got one. I' m also able
> to address a Server which appears in the GUI but I don t need one for
> my ACCess DB (I only tried, if the command parameters have any effect
> on the converter).
> I would be glad if someone could help me on this.
> Thank you
> Klaus Baumgarten

I'm not totally sure what your issue is - are you asking how to execute a
DTS package from the command line? If so, you can save your package to a
..dts file and execute it with dtsrun.exe; you can use dtsrunui.exe to build
a working command line first.

If your problem is moving data from an Access database to an MSSQL one, then
this may help:

http://support.microsoft.com/default.aspx?kbid=285829

Simon

Convert Access Database to SQL Server?

is it possible to convert an Access database to SQL Server 2005 or 2000?http://www.microsoft.com/sql/solutions/migration/access/default.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0561.mspx?mfr=true|||Thanks for the links. perfecgt.|||all i did was google it...sql sql

Convert Access CROSSTAB query to SQL Table or View

I have a Crosstab query that I need to convert to SQL to complete upsize of a large DB.
I have a table (here referred to as Data) with the fields: Resource, Date and Count. I need to transform it to a table (or view) with a fields called Date, and one field for each Resource that exists in the Data table.

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

SELECT sDate,
AVG(CASE WHEN res ='res1' THEN sCount END) as res1,
AVG(CASE WHEN res ='res2' THEN sCount END) as res2,
AVG(CASE WHEN res ='res3' THEN sCount END) as res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
WHERE res IN ('res1', 'res2', 'res3')
GROUP BY sDate
ORDER By Convert(DATETIME,'01'+sDate,13)

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Convert Access CROSSTAB query to SQL Table or View

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Convert Access CROSSTAB query to SQL Table or View

TRANSFORM IIf(Sum(IIf([blockinyield]=True,[SIZE],0))>0,Sum([Y_TOTAL_ton])/Sum(IIf([blockinyield]=True,[SIZE],0)),0) AS Yield_THA
SELECT OILPALM.NAME, OILPALM.YEAR, formatyear([year]) AS yearDisplay, Count(OILPALM.BLOCK) AS CountOfBLOCK
FROM OILPALM
GROUP BY OILPALM.NAME, OILPALM.YEAR
PIVOT Year([D_PLANTED]);

how to convert the access query above to sql server 2000

In SQL Server 2000 you have't have predefined operator to get the PIVOT table..

Here you have to manually write the query to get the pivot result...

(Example)

Code Snippet

Create Table #BikeSales
(
Year int,
Product Varchar(100),
Sales Int
)

Insert Into #BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into #BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into #BikeSales Values ('2007', 'HONDA F1', 7000)

Insert Into #BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into #BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into #BikeSales Values ('2007', 'HONDA IRL', 1000)

Insert Into #BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into #BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into #BikeSales Values ('2007', 'HONDA MotoGP', 132)

Insert Into #BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into #BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into #BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
[Main].Product
,Sum([2005].Sales) as [2005]
,Sum([2006].Sales) as [2006]
,Sum([2007].Sales) as [2007]
From (Select Distinct Product From #BikeSales) as [Main]
Left Outer Join (Select * From #BikeSales Where Year=2005) as [2005] On [2005].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2006) as [2006] On [2006].Product=[Main].Product
Left Outer Join (Select * From #BikeSales Where Year=2007) as [2007] On [2007].Product=[Main].Product
Group By [Main].Product

You can generate the above query dynamically using the following script..

Code Snippet

Declare @.JoinQuery as Varchar(1000);
Declare @.SelectQuery as Varchar(1000);
Declare @.PreparedJoinQuery as Varchar(1000);
Declare @.PreparedSelectQuery as Varchar(1000);
Select @.JoinQuery = '', @.SelectQuery = ''
Select @.PreparedJoinQuery = 'Left Outer Join (Select * From #BikeSales Where Year=?) as [?] On [?].Product=[Main].Product '
Select @.PreparedSelectQuery =',Sum([?].Sales) as [?]'
Select
@.JoinQuery = @.JoinQuery + Replace(@.PreparedJoinQuery,'?',Cast(year as Varchar))
,@.SelectQuery = @.SelectQuery + Replace(@.PreparedSelectQuery,'?',Cast(year as Varchar)) From #BikeSales Group By Year

Exec ('Select [Main].Product' + @.SelectQuery + ' From (Select Distinct Product From #BikeSales) as [Main]' + @.JoinQuery + ' Group By [Main].Product')

|||

Using Manivannan's data, this method of creating a 'pivot' table in SQL 2005 is quite a bit more efficient. (Single Pass, No JOINS, NO Sub-Queries, No Dynamic SQL.)

Code Snippet

DECLARE @.BikeSales table
( [Year] int,
Product varchar(25),
Sales int
)

Insert Into @.BikeSales Values ('2005', 'HONDA F1', 10000)
Insert Into @.BikeSales Values ('2006', 'HONDA F1', 6000)
Insert Into @.BikeSales Values ('2007', 'HONDA F1', 7000)
Insert Into @.BikeSales Values ('2005', 'HONDA IRL', 100)
Insert Into @.BikeSales Values ('2006', 'HONDA IRL', 99)
Insert Into @.BikeSales Values ('2007', 'HONDA IRL', 1000)
Insert Into @.BikeSales Values ('2005', 'HONDA MotoGP', 124)
Insert Into @.BikeSales Values ('2006', 'HONDA MotoGP', 344)
Insert Into @.BikeSales Values ('2007', 'HONDA MotoGP', 132)
Insert Into @.BikeSales Values ('2005', 'HONDA Super GT', 234)
Insert Into @.BikeSales Values ('2006', 'HONDA Super GT', 32344)
Insert Into @.BikeSales Values ('2007', 'HONDA Super GT', 123232)

Select
Product,
[2005] = sum( CASE [Year] WHEN 2005 THEN Sales END ),
[2006] = sum( CASE [Year] WHEN 2006 THEN Sales END ),
[2007] = sum( CASE [Year] WHEN 2007 THEN Sales END )
FROM @.BikeSales
GROUP BY Product
ORDER BY Product

Convert Access CROSSTAB query to SQL Table or View

The Data table looks like this:
RES DATE COUNT
res1 Jan06 5
res2 Jan06 4
res3 Jan 06 2
res1 Feb06 9
res2 Feb06 5
res3 Feb06 7

etc

The Access crosstab query sql is:
=====================
TRANSFORM Sum(Data.Count) AS SumOfCount
SELECT Data.Date
FROM Data
GROUP BY Data.Date
ORDER BY Data.Date
PIVOT Data.Resource;

which gives the resultant data set for charting:
Date res1 res2 res3
Jan06 5 4 2
Feb06 9 5 7

TRANSFORM is not T-SQL. I assume I need a usp to create the required table. Any ideas please?

George Cooper.

In SQL Server, you can use either CASE statement to pivot the table or PIVOT function in SQL Server 2005.

Here is CASE solution:

PIVOT solution:(SQL Server 2005)

SELECT sDate, res1, res2, res3
FROM (SELECT sDate, res, sCount FROM myDATA) p
PIVOT (AVG(sCount) FOR res IN ([res1], [res2], [res3])) AS pvt
ORDER By Convert(DATETIME,'01'+sDate,13)

You need to pay attention to your so-called Date column. I convert the text (nvarchar) field to datetime for sorting purpose.

|||

Thanks,

Pivot soultion works well.

Regards\

George Cooper

|||There is an interesting article on this issue at:
http://tinyurl.com/mgrwo

|||

But if the number of destination columns (res1, res2, res3,...,resN) is unknown?

Using SqlServer 2000.

Later i found these great article:
http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

Convert ACCESS code to SQL Server

Hi,
I m working on the project which was using MS Access but now i want to use SQL Server 2005.
But there are some queries which are not understood by me, so please help me in converting those.
Following is the query:

SHAPE {select stlcode , SampleTypeCode,InNo,NoOfSamples,RecdDistrict,RecdT al,DtRecd , RecdFrom, RecdNameDesg, PrimaryInNo, MicroInNo, WaterInNo from SInward order by SampleTypeCode,InNo } AS ParentCMD APPEND
({select SampleType, InNo, SrNo, StlCode, AStlCode, SampleTakenDt, FarmerNm, FarAddVil, FarAddPost, FarAddDist, FarAddTal , FarAddPin, SurveyGrNo, ReprArea, LastSeason, LastSeasonCrop, NextSeason, NextSeasonCrop, NextSeasonCrop22, AgeOfTree, LandProfile, DepthFrom, DepthTo, WaterSource, CollectdBy, SampleAccepted, LabSampleNo, HCNumber, MicroLabSampleNo, WaterLabSampleNo from SDetails order by SrNo } AS ChildCMD RELATE SampleTypeCode TO SampleType, InNo to InNo, stlcode to stlcode) AS ChildCMD

This query was previously written in MS Access and now I want it to be in SQL Server 2005, So please help me in solving this.
Thanx

Quote:

Originally Posted by sachinkale123

This query was previously written in MS Access and now I want it to be in SQL Server 2005, So please help me in solving this.
Thanx

That is not simple thing, most of the query you have to change.

1.Access Date in where clause will be # change to
2.Access IIF condition, change to CaseBlab blab.|||

Quote:

Originally Posted by hariharanmca

That is not simple thing, most of the query you have to change.

1.Access Date in where clause will be # change to
2.Access IIF condition, change to CaseBlab blab.

Thanxs for u r reply...
now i can think that way...sql sql

Convert Access 2003 Function

I am converting an Access DB to SQL2000 and opne of the queries uses the
Format$ function. How should the syntax be for SQL?
Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
Use the CONVERT function with an optional date style argument. See the
following for syntax, samples and date arguments.
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>I am converting an Access DB to SQL2000 and opne of the queries uses the
> Format$ function. How should the syntax be for SQL?
> Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth
|||Thanks Jerry, I already looked at Cast and Convert but cannot figure out the
correct syntax. It's a bit confusing for me.
"Jerry Spivey" wrote:

> Use the CONVERT function with an optional date style argument. See the
> following for syntax, samples and date arguments.
> http://msdn.microsoft.com/library/de...ca-co_2f3o.asp
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>
>
|||Try this:
SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...[vbcol=seagreen]
> Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> the
> correct syntax. It's a bit confusing for me.
> "Jerry Spivey" wrote:
|||Thats was it. I changed the GetDate with my DB field and removed the Select
and it worked with no problems. Thanks much Jerry. I also understand the
function better now that I have the correct syntax.
"Jerry Spivey" wrote:

> Try this:
> SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),6)
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
>
>

Convert Access 2003 Function

I am converting an Access DB to SQL2000 and opne of the queries uses the
Format$ function. How should the syntax be for SQL?
Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonthUse the CONVERT function with an optional date style argument. See the
following for syntax, samples and date arguments.
http://msdn.microsoft.com/library/d...br />
2f3o.asp
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>I am converting an Access DB to SQL2000 and opne of the queries uses the
> Format$ function. How should the syntax be for SQL?
> Access: Format$(TimeTrackerEntry.EntryDate,'yyyymm') AS EntryMonth|||Thanks Jerry, I already looked at Cast and Convert but cannot figure out the
correct syntax. It's a bit confusing for me.
"Jerry Spivey" wrote:

> Use the CONVERT function with an optional date style argument. See the
> following for syntax, samples and date arguments.
> http://msdn.microsoft.com/library/d... />
o_2f3o.asp
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:073E808D-100D-4F1F-9842-4BEB4DF3F112@.microsoft.com...
>
>|||Try this:
SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),
6)
HTH
Jerry
"dj5md" <dj5md@.discussions.microsoft.com> wrote in message
news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...[vbcol=seagreen]
> Thanks Jerry, I already looked at Cast and Convert but cannot figure out
> the
> correct syntax. It's a bit confusing for me.
> "Jerry Spivey" wrote:
>|||Thats was it. I changed the GetDate with my DB field and removed the Select
and it worked with no problems. Thanks much Jerry. I also understand the
function better now that I have the correct syntax.
"Jerry Spivey" wrote:

> Try this:
> SELECT LEFT(CONVERT(VARCHAR(25),GETDATE(),112),
6)
> HTH
> Jerry
> "dj5md" <dj5md@.discussions.microsoft.com> wrote in message
> news:8212D4FC-A530-47DC-9E07-934516904C6D@.microsoft.com...
>
>

Thursday, March 29, 2012

Convert an Access database to SQL

Convert an Access database to SQL

Convert AlphaNumeric to Numeric

convert all caps names to proper names

Convert ADP to MDB

Convert ADP to MDB

convert adDBtimeStamp to datetime

Convert Access to SQL 2000 Desktop

Convert Access to SQL

Convert Access SQL to ANSII compliant SQL

Convert Access Query w/IIF to SQL Server View

Convert Access Query to Transact SQL 2000 IIF to CASE

Convert Access query to SQL View

Convert Access Query to SQL Server View

Convert Access query to sql

Convert Access into SQL server express

convert access IIF to sql in order by statement

Convert Access Function to SQL

convert Access DB to MS SQL with dtswiz(command line)

Convert Access Database to SQL Server?

Convert Access CROSSTAB query to SQL Table or View

Convert Access CROSSTAB query to SQL Table or View

Convert Access CROSSTAB query to SQL Table or View

Convert Access CROSSTAB query to SQL Table or View

Convert ACCESS code to SQL Server

Convert Access 2003 Function

Convert Access 2003 Function

Consolidation Query

Blog Archive

About Me