Access to SQL Server Query Translation

Hi,

I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
application with SQL Server as Backend.

I'm having trouble converting Access Query into SQL Query. The Query is
given below:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SELECT DISTINCTROW Buildings.BuildingNumber,
First(Buildings.BuildingName) AS FirstOfBuildingName,
First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
First(CityCodes.CityName) AS FirstOfCityName,
First(CountyCodes.CountyName) AS FirstOfCountyName,
First(Buildings.Address) AS FirstOfAddress,
First(Buildings.YearConstructed) AS FirstOfYearConstructed,
First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
First(Buildings.NumberLevels) AS FirstOfNumberLevels,
First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
First(Buildings.SpecialArea) AS FirstOfSpecialArea,
First(Buildings.CoveredUnenclosedGrossArea) AS
FirstOfCoveredUnenclosedGrossArea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
Buildings.OwnershipCode) ON CountyCodes.CountyCode =
CityCodes.CountyCode
GROUP BY Buildings.BuildingNumber;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~

Please can any one tell me substitue for First Function in Acess to SQL
Function.

Any help is appreciated.
Thanks,
S(s_wadhwa@.berkeley.edu) writes:

Quote:

Originally Posted by

I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
application with SQL Server as Backend.
>
I'm having trouble converting Access Query into SQL Query. The Query is
given below:
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SELECT DISTINCTROW Buildings.BuildingNumber,
First(Buildings.BuildingName) AS FirstOfBuildingName,
First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
First(CityCodes.CityName) AS FirstOfCityName,
First(CountyCodes.CountyName) AS FirstOfCountyName,
First(Buildings.Address) AS FirstOfAddress,
First(Buildings.YearConstructed) AS FirstOfYearConstructed,
First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
First(Buildings.NumberLevels) AS FirstOfNumberLevels,
First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
First(Buildings.SpecialArea) AS FirstOfSpecialArea,
First(Buildings.CoveredUnenclosedGrossArea) AS
FirstOfCoveredUnenclosedGrossArea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
Buildings.OwnershipCode) ON CountyCodes.CountyCode =
CityCodes.CountyCode
GROUP BY Buildings.BuildingNumber;
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>
Please can any one tell me substitue for First Function in Acess to SQL
Function.

I don't know Access, but if I have understood it correctcly, First
returns the value for the "first" row in the group. What I don't know
if you are guaranteed that all these "first" will return data from the
same row from Buildings, or if they could be from different rows.

You see, in a relational database "first" is a not meaningful operation.
A table is a set of unordered tuples, and there is no first or last.

It could help if you posted the CREATE TABLE statements for the table,
including definitions of primary keys and foreign keys. It's also a good
idea to add a short description of what the query is supposed to achieve.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||To add on to Erland's response, FIRST is not relational so there is no
direct SQL Server equivalent. I have seen FIRST most often used in Access
queries to mask problems with data or query formulation rather than to
address a real requirement.

It looks to me like the purpose of this query is to calculate the total
assignable square feet by building and include additional information
related to the building. In that case, you might try something like the
example below, which assumes the primary key and foreign key relationships
are on the joined columns:

SELECT
Buildings.BuildingNumber,
Buildings.BuildingName,
First(OwnershipCodes.OwnershipCode,
OwnershipCodes.OwnershipDesc,
CityCodes.CityName,
CountyCodes.CountyName,
Buildings.Address,
Buildings.YearConstructed,
Buildings.DateOccupancy,
Buildings.NumberLevels,
Buildings.BasicGrossArea,
(SELECT SUM(Rooms.AssignableSquareFeet)
FROM Rooms
WHERE Buildings.BuildingNumber = Rooms.BuildingNumber
) AS SumOfAssignableSquareFeet,
Buildings.UnrelatedGrossArea,
Buildings.SpecialArea,
Buildings.CoveredUnenclosedGrossArea
FROM Buildings
INNER JOIN CountyCodes
ON CityCodes.CityCode = Buildings.CityCode
INNER JOIN OwnershipCodes
ON OwnershipCodes.OwnershipCode = Buildings.OwnershipCode
INNER JOIN ConditionCodes
ON ConditionCodes.ConditionCode = Buildings.ConditionCode
INNER JOIN CityCodes
ON CountyCodes.CountyCode = CityCodes.CountyCode;

--
Hope this helps.

Dan Guzman
SQL Server MVP

<s_wadhwa@.berkeley.eduwrote in message
news:1155837812.840480.6380@.m73g2000cwd.googlegrou ps.com...

Quote:

Originally Posted by

Hi,
>
I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
application with SQL Server as Backend.
>
I'm having trouble converting Access Query into SQL Query. The Query is
given below:
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
SELECT DISTINCTROW Buildings.BuildingNumber,
First(Buildings.BuildingName) AS FirstOfBuildingName,
First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
First(CityCodes.CityName) AS FirstOfCityName,
First(CountyCodes.CountyName) AS FirstOfCountyName,
First(Buildings.Address) AS FirstOfAddress,
First(Buildings.YearConstructed) AS FirstOfYearConstructed,
First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
First(Buildings.NumberLevels) AS FirstOfNumberLevels,
First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
First(Buildings.SpecialArea) AS FirstOfSpecialArea,
First(Buildings.CoveredUnenclosedGrossArea) AS
FirstOfCoveredUnenclosedGrossArea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
Buildings.OwnershipCode) ON CountyCodes.CountyCode =
CityCodes.CountyCode
GROUP BY Buildings.BuildingNumber;
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>
Please can any one tell me substitue for First Function in Acess to SQL
Function.
>
Any help is appreciated.
Thanks,
S
>

|||

Quote:

Originally Posted by

First(OwnershipCodes.OwnershipCode,

Oops, missed one. Should be:

OwnershipCodes.OwnershipCode,

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.netwrote in message
news:pCZFg.1509$yO7.177@.newssvr14.news.prodigy.com ...

Quote:

Originally Posted by

To add on to Erland's response, FIRST is not relational so there is no
direct SQL Server equivalent. I have seen FIRST most often used in Access
queries to mask problems with data or query formulation rather than to
address a real requirement.
>
It looks to me like the purpose of this query is to calculate the total
assignable square feet by building and include additional information
related to the building. In that case, you might try something like the
example below, which assumes the primary key and foreign key relationships
are on the joined columns:
>
SELECT
Buildings.BuildingNumber,
Buildings.BuildingName,
First(OwnershipCodes.OwnershipCode,
OwnershipCodes.OwnershipDesc,
CityCodes.CityName,
CountyCodes.CountyName,
Buildings.Address,
Buildings.YearConstructed,
Buildings.DateOccupancy,
Buildings.NumberLevels,
Buildings.BasicGrossArea,
(SELECT SUM(Rooms.AssignableSquareFeet)
FROM Rooms
WHERE Buildings.BuildingNumber = Rooms.BuildingNumber
) AS SumOfAssignableSquareFeet,
Buildings.UnrelatedGrossArea,
Buildings.SpecialArea,
Buildings.CoveredUnenclosedGrossArea
FROM Buildings
INNER JOIN CountyCodes
ON CityCodes.CityCode = Buildings.CityCode
INNER JOIN OwnershipCodes
ON OwnershipCodes.OwnershipCode = Buildings.OwnershipCode
INNER JOIN ConditionCodes
ON ConditionCodes.ConditionCode = Buildings.ConditionCode
INNER JOIN CityCodes
ON CountyCodes.CountyCode = CityCodes.CountyCode;
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<s_wadhwa@.berkeley.eduwrote in message
news:1155837812.840480.6380@.m73g2000cwd.googlegrou ps.com...

Quote:

Originally Posted by

>Hi,
>>
>I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp
>application with SQL Server as Backend.
>>
>I'm having trouble converting Access Query into SQL Query. The Query is
>given below:
>>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>SELECT DISTINCTROW Buildings.BuildingNumber,
>First(Buildings.BuildingName) AS FirstOfBuildingName,
>First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,
>First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,
>First(CityCodes.CityName) AS FirstOfCityName,
>First(CountyCodes.CountyName) AS FirstOfCountyName,
>First(Buildings.Address) AS FirstOfAddress,
>First(Buildings.YearConstructed) AS FirstOfYearConstructed,
>First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,
>First(Buildings.NumberLevels) AS FirstOfNumberLevels,
>First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,
>Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,
>First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,
>First(Buildings.SpecialArea) AS FirstOfSpecialArea,
>First(Buildings.CoveredUnenclosedGrossArea) AS
>FirstOfCoveredUnenclosedGrossArea
>FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
>INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
>Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =
>Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =
>Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =
>Buildings.OwnershipCode) ON CountyCodes.CountyCode =
>CityCodes.CountyCode
>GROUP BY Buildings.BuildingNumber;
>>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
>>
>Please can any one tell me substitue for First Function in Acess to SQL
>Function.
>>
>Any help is appreciated.
>Thanks,
>S
>>

>
>

Access to SQL

I have a coldfusion application that I have been converting from access to sql the database tables converted without a hitch but the query calls between access and sql are amazingly different can anyone reccomend a site that might give some details or conversion of the 2 different query types?Amazingly different? No...

Just different enough to be confusing? Definitely.

Don't know of any website, but feel free to post specific questions here.

Access to dataset fields in Code window

Hi,
Is it, or will it be possible to use dataset fields in expressions in the
code window. We are converting formulas which are too complex for an IIF
expression in the report, and would prefer not to create too many sprocs in
the database.
Cheers,
MattYou cannot access fields directly in the code window. You need to pass-in
the field values as arguments to your custom code function. The following
article shows how to do this:
http://odetocode.com/Articles/130.aspx
A more detailed discussion is provided in the following MSDN article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:3EFF6484-97F2-4744-BB6F-6DC35F084D7B@.microsoft.com...
> Hi,
> Is it, or will it be possible to use dataset fields in expressions in the
> code window. We are converting formulas which are too complex for an IIF
> expression in the report, and would prefer not to create too many sprocs
> in
> the database.
> Cheers,
> Matt

Access Export to SQL Server

Hi there,

Is there an equivalent in SQL Server to Access' exporting functions?

I am converting an Access database to SQL Server. One of the features of this Access DB is that it has a form through which you can export specific table data as a tab-delimited text file, csv or an excel format.

To make matters worse, the data that I want to export comes from several tables that I have merged into a view. ie. I want to export the values returned by a SELECT * on the view.

I am a newbie to Access and SQL Server so I don't know the ins and outs of things. It looks like DTS may be able to do what I want, but I am not sure. Any thoughts/ideas?Yes, dts and bcp.|||You can add a SQL Server table or view to Access as a linked table, and then use the export function of access to export data as CSV.|||Have you used the access upsizing wizard which converts your access database to sql server ?|||DTS is the way to go!

For all out there not aware, I used the Import/Export Data Service (DTS) .

From there, you can choose to export your data as a text file (or several other options).

The tricky part for me came in the next window. If you want to export a table, you can just use the "Copy table/view from source DB" option. But that option won't show the view (for some odd reason)!

Instead, I used a "query to specify data" and just selected * from the view (don't know why I didn't think of that before).

Two windows later you can check "Save DTS Package" and select "SQL Server." Give it a name, etc. and then you can run it over and over if you need to.

Thanks for all the super-fast reponses!!!