Migrating from Acess 2003 to Sql server - Function Val()

Hi folks

I do a lot of work with address data. I have been using Acess 2003 as my database but now need to upgrade to SQL Server.

My queries have been making extensive use of the Val function to order my queries on address columns. A typical address column might be:

12 The Avenue .......

by using Order By Val(AddressCol) I can sort these rows in ascending house number.

I can't find anything in SQL Server that will acomplish this. Does anybody have any ideas.

Thanks

Paul

why do you need to reference the val() function? is there something in your column that you want to exclude?

Does: "order by AddressCol" in TSQL not accomplish what you want?

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.
Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison
|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:

> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, though
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:
> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, though
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.

Migrating from Access to SQL Server

I recently migrated some user tables from Access to SQL Server while leaving
the forms, reports, and queries in Access with Linked tables to the SQL
instance. Some of the users queries/forms are not working correctly? It
seems that Access Queries return a single empty row when no matches are foun
d
while SQL Server returns an empty result set. In Access, the users use a
query to search for a customer number, if no matching number is found the
customer form is displayed with one record containing no values and the user
s
can then input new customer information. When the query/form is pointed to
SQL Server, when no matching customer is found a blank page is displayed. I
s
there a way to force SQL Server to return an empty row if none are found?Hi,
probably the attached script would help to you (I'm not sure how to
implement it within Access enviroment):
create table t (col1 int null, col2 varchar(10) null)
--without variables
select col1,col2 from t
union all
select null, null
--with variables
declare @.r as int
select * from t
set @.r=@.@.rowcount
if @.r>0
return
else
select null as col1, null as col2
drop table t
Denis
"DT" wrote:

> I recently migrated some user tables from Access to SQL Server while leavi
ng
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are fo
und
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the us
ers
> can then input new customer information. When the query/form is pointed t
o
> SQL Server, when no matching customer is found a blank page is displayed.
Is
> there a way to force SQL Server to return an empty row if none are found?
>|||Although your solution does cause the form to be displayed (progress!) I can
not enter data on the form? Am I missing something?
"Denis" wrote:
> Hi,
> probably the attached script would help to you (I'm not sure how to
> implement it within Access enviroment):
> create table t (col1 int null, col2 varchar(10) null)
> --without variables
> select col1,col2 from t
> union all
> select null, null
> --with variables
> declare @.r as int
> select * from t
> set @.r=@.@.rowcount
> if @.r>0
> return
> else
> select null as col1, null as col2
>
> drop table t
> Denis
> "DT" wrote:
>|||There is no such thing as an "empty" row in SQL Server. The functionality
you are wanting would probably be a property of the recordset or data grid.
"DT" <DT@.discussions.microsoft.com> wrote in message
news:0D425E66-49D7-4C46-97E2-7813DB70A6D0@.microsoft.com...
>I recently migrated some user tables from Access to SQL Server while
>leaving
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are
> found
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the
> users
> can then input new customer information. When the query/form is pointed
> to
> SQL Server, when no matching customer is found a blank page is displayed.
> Is
> there a way to force SQL Server to return an empty row if none are found?
>

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:

> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, thoug
h
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.sql

Migrating Access Query to SQL

I have complicated queries in access I want to migrate to SQL. They're
"complicated" only in the sense that I use the results of one calculated
field in an imbedded if (iif) of another calculated field, which in turn is
used in another imbedded if in another calculated field.
I replaced the iif with a CASE statement, and it worked for the first few
fields. I replace the field name with the (case statement) query since (as
far as I know) you can't use a calculated field as part of another field
like you can in access. This is okay until I get out a few fields and the
calculations become too large to be readable.
Here is an example query from access. (No problems until I get to variable
E, then it gets ridiculous)
A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
B: Len(A)
C: Len(myField2)
D: B + C - 10
E: IIF(B-C < 4, IIF(B<4,B,4), B-C)--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
E:
CASE WHEN B-C < 4
THEN CASE WHEN B < 4
THEN B
ELSE 4
END
ELSE B-C
END
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQf/y64echKqOuFEgEQIWuACfbHICFPTl5Pg8QaBDsD/NmtEVNmUAn0MD
gmxaj6LDDbQ5H8by4jsPyu+C
=7xKC
--END PGP SIGNATURE--
Jed Perlowin wrote:
> I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn i
s
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since (a
s
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to variabl
e
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
>|||Try,

> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
case when (b - c) < 4 then case when b < 4 then b else 4 end else b - c end
AMB
"Jed Perlowin" wrote:

> I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn i
s
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since (a
s
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to variabl
e
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
>|||Thank you for your reply. However, the logic isn't my problem -- but the
fact that you can use B, C, and D in the query (as you have below) in
Access, but (as far as I know -- and I'm hoping I'm wrong) you cannot use
them directly in SQL (and must expand each calculated field with it's
query).
"MGFoster" <me@.privacy.com> wrote in message
news:RpSLd.4078$cl1.1991@.newsread3.news.pas.earthlink.net...
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> E:
> CASE WHEN B-C < 4
> THEN CASE WHEN B < 4
> THEN B
> ELSE 4
> END
> ELSE B-C
> END
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/AwUBQf/y64echKqOuFEgEQIWuACfbHICFPTl5Pg8QaBDsD/NmtEVNmUAn0MD
> gmxaj6LDDbQ5H8by4jsPyu+C
> =7xKC
> --END PGP SIGNATURE--
>
> Jed Perlowin wrote:
is
few
(as
the
variable|||"Jed Perlowin" <jed@.perlowin.com> wrote in message
news:ObcPAMKCFHA.1424@.TK2MSFTNGP09.phx.gbl...
>I have complicated queries in access I want to migrate to SQL. They're
> "complicated" only in the sense that I use the results of one calculated
> field in an imbedded if (iif) of another calculated field, which in turn
> is
> used in another imbedded if in another calculated field.
> I replaced the iif with a CASE statement, and it worked for the first few
> fields. I replace the field name with the (case statement) query since
> (as
> far as I know) you can't use a calculated field as part of another field
> like you can in access. This is okay until I get out a few fields and the
> calculations become too large to be readable.
> Here is an example query from access. (No problems until I get to
> variable
> E, then it gets ridiculous)
> A: IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField)
> B: Len(A)
> C: Len(myField2)
> D: B + C - 10
> E: IIF(B-C < 4, IIF(B<4,B,4), B-C)
>
Use nested selects to refer to the results of computed columns by alias.
You must add a nested select for each level of recursion in your query.
SELECT A,B,C,B+C-10 D,
CASE WHEN B-C<2 THEN
CASE WHEN B<4 THEN B ELSE 4 END
ELSE B-C END E
FROM
(
SELECT A, Len(A) B, Len(MyField2) C
FROM
(
SELECT
IIF(Len(myField) > 8, Left(myField, 4) & Right(myField, 4), myField) A,
MyField2
FROM T
) DT
)DT2
David|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Yes, you're correct: you can't use aliases in calculations. IOW,
Access SQL:
(col1 + col2)/ col3 As A, A + 25 As B
SQL'r syntax:
(col1 + col2) / col3 As A,
((col1 + col2) / col3) + 25 As B
David Browne has a interesting solution that, unfortunately, doesn't
reduce the complexity of the query, but does allow you to use aliases in
calculations. In essence, he used tabular subqueries to alias the
calculations.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQf/ 4R4echKqOuFEgEQJOXQCgx5V7nCjfwSmy2uVFzQG
lMlXuxm8AoLfz
OWxitX3IfYip6FasKyI2I88+
=mIEC
--END PGP SIGNATURE--
Jed Perlowin wrote:
> Thank you for your reply. However, the logic isn't my problem -- but the
> fact that you can use B, C, and D in the query (as you have below) in
> Access, but (as far as I know -- and I'm hoping I'm wrong) you cannot use
> them directly in SQL (and must expand each calculated field with it's
> query).
>

Migrate from Access to SQl Server 2005

I am trying to convert an Access database to SQL Server 2005. I have gotten the tables imported into SS2005 but how can I get all my queries imported in? Or is it not possible? Thanks.

John

You coudl try this CTP of a new tool

http://www.microsoft.com/downloads/details.aspx?familyid=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en

Migrate from Access queries to T-SQL

Could anyone help me translate an access statement to T-SQL?

1. what is the Equivalent of First() in T-SQL?

need to convert:

SELECT NAMAST.SSN_9, Sum(MASTER_DSC.DSC_CUR_BAL_21) AS SumOfDSC_CUR_BAL_21, First(MASTER_DSC.ACCTNO) AS FirstOfACCTNO
FROM MASTER

Thanks in advance for your help or advice...

FIRST or LAST based on what criteria?.

Guessing by the statement posted, you could try using aggregate function MIN.

Example:

Code Snippet

create table dbo.t1 (

c1 int not null,

c2 int not null,

c3 money not null

)

go

insert into dbo.t1 values(1, 1, 5)

insert into dbo.t1 values(1, 2, 5)

insert into dbo.t1 values(2, 10, 5)

insert into dbo.t1 values(2, 20, 5)

insert into dbo.t1 values(2, 30, 3)

go

select

c1,

min(c2) as min_c2,

sum(c3) as sum_c3

from

dbo.t1

group by

c1

go

drop table dbo.t1

go

AMB

|||

Would the full statement help anyone help me?

SELECT NAMAST.SSN_9
, Sum(MASTER_DSC.DSC_CUR_BAL_21) AS SumOfDSC_CUR_BAL_21
, First(MASTER_DSC.ACCTNO) AS FirstOfACCTNO
FROM MASTER_DSC
INNER JOIN NAMAST ON (MASTER_DSC.APP=NAMAST.APP) AND (MASTER_DSC.ACCTNO=NAMAST.ACCTNO)
WHERE (((NAMAST.SEQ)=0) AND ((MASTER_DSC.DSC_STATUS) Not In (9,10,11,90)))
GROUP BY NAMAST.SSN_9
HAVING (((NAMAST.SSN_9)>"0") AND ((Sum(MASTER_DSC.DSC_CUR_BAL_21))>=[Total Customer Deposits >= What $ Amount]))
ORDER BY NAMAST.SSN_9;

|||

I think "First" is a "group operator" in Access, causing it to grab the value from the first record it encounters, without any regard to order. So it is potentially rather vague in what it means, since the data may not always be "grabbed" in the same order.

You are probably just as well off to use something like MIN or MAX.

Dan

|||Thanks hunchback and DanR1. You've been a great help!

Microsoft Technical support

Hi
Can anyone tell me how can I contact the microsoft technical support for a few of my queries?
Thanks
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Check http://support.microsoft.com/default.aspx?pr=cntactms&style=home.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:B96293D6-9FD0-48E7-A3B4-5DCCD0871046@.microsoft.com...
> Post your queries here. The MSFT RS team answers most of the questions.
> "SqlJunkies User" wrote:
> > Hi
> >
> > Can anyone tell me how can I contact the microsoft technical support for
a few of my queries?
> >
> > Thanks
> >
> >
> > --
> > Posted using Wimdows.net NntpNews Component -
> >
> > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
> >|||It was suggested previously that this newsgroup may become a "Managed Newsgroup" - any progress on that front?
Regards
Chris McGuigan
"Ravi Mumulla (Microsoft)" wrote:
> Check http://support.microsoft.com/default.aspx?pr=cntactms&style=home.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:B96293D6-9FD0-48E7-A3B4-5DCCD0871046@.microsoft.com...
> > Post your queries here. The MSFT RS team answers most of the questions.
> >
> > "SqlJunkies User" wrote:
> >
> > > Hi
> > >
> > > Can anyone tell me how can I contact the microsoft technical support for
> a few of my queries?
> > >
> > > Thanks
> > >
> > >
> > > --
> > > Posted using Wimdows.net NntpNews Component -
> > >
> > > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
> > >
>
>|||Last I heard, they were going to give it another 30 days to get more data.
That would be around the beginning of July. Once they have all the data they
need, I have no idea how long it will take them to make the decision and get
the contracts in place. I'm hoping it'll be fairly soon but I don't have
direct visibility into that process.
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> wrote in message
news:1DE126C2-959F-4D53-88B8-4D8AE43A19EB@.microsoft.com...
> It was suggested previously that this newsgroup may become a "Managed
Newsgroup" - any progress on that front?
> Regards
> Chris McGuigan
> "Ravi Mumulla (Microsoft)" wrote:
> > Check http://support.microsoft.com/default.aspx?pr=cntactms&style=home.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> > news:B96293D6-9FD0-48E7-A3B4-5DCCD0871046@.microsoft.com...
> > > Post your queries here. The MSFT RS team answers most of the
questions.
> > >
> > > "SqlJunkies User" wrote:
> > >
> > > > Hi
> > > >
> > > > Can anyone tell me how can I contact the microsoft technical support
for
> > a few of my queries?
> > > >
> > > > Thanks
> > > >
> > > >
> > > > --
> > > > Posted using Wimdows.net NntpNews Component -
> > > >
> > > > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup
engine
> > supports Post Alerts, Ratings, and Searching.
> > > >
> >
> >
> >|||Thanks for the update Stephen.
Regards
Chris McGuigan
"Stephen Dybing [MSFT]" wrote:
> Last I heard, they were going to give it another 30 days to get more data.
> That would be around the beginning of July. Once they have all the data they
> need, I have no idea how long it will take them to make the decision and get
> the contracts in place. I'm hoping it'll be fairly soon but I don't have
> direct visibility into that process.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> wrote in message
> news:1DE126C2-959F-4D53-88B8-4D8AE43A19EB@.microsoft.com...
> > It was suggested previously that this newsgroup may become a "Managed
> Newsgroup" - any progress on that front?
> >
> > Regards
> > Chris McGuigan
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > Check http://support.microsoft.com/default.aspx?pr=cntactms&style=home.
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> > > news:B96293D6-9FD0-48E7-A3B4-5DCCD0871046@.microsoft.com...
> > > > Post your queries here. The MSFT RS team answers most of the
> questions.
> > > >
> > > > "SqlJunkies User" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > Can anyone tell me how can I contact the microsoft technical support
> for
> > > a few of my queries?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > > --
> > > > > Posted using Wimdows.net NntpNews Component -
> > > > >
> > > > > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup
> engine
> > > supports Post Alerts, Ratings, and Searching.
> > > > >
> > >
> > >
> > >
>
>