Access to SQL Server Query Translation

**Erland Sommarskog** · Aug 17 '06, 10:25 PM

Re: Access to SQL Server Query Translation

(s_wadhwa@berke ley.edu) writes:

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.Build ingNumber,
First(Buildings .BuildingName) AS FirstOfBuilding Name,
First(Ownership Codes.Ownership Code) AS FirstOfOwnershi pCode,
First(Ownership Codes.Ownership Desc) AS FirstOfOwnershi pDesc,
First(CityCodes .CityName) AS FirstOfCityName ,
First(CountyCod es.CountyName) AS FirstOfCountyNa me,
First(Buildings .Address) AS FirstOfAddress,
First(Buildings .YearConstructe d) AS FirstOfYearCons tructed,
First(Buildings .DateOccupancy) AS FirstOfDateOccu pancy,
First(Buildings .NumberLevels) AS FirstOfNumberLe vels,
First(Buildings .BasicGrossArea ) AS FirstOfBasicGro ssArea,
Sum(Rooms.Assig nableSquareFeet ) AS SumOfAssignable SquareFeet,
First(Buildings .UnrelatedGross Area) AS FirstOfUnrelate dGrossArea,
First(Buildings .SpecialArea) AS FirstOfSpecialA rea,
First(Buildings .CoveredUnenclo sedGrossArea) AS
FirstOfCoveredU nenclosedGrossA rea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityC ode =
Buildings.CityC ode) LEFT JOIN Rooms ON Buildings.Build ingNumber =
Rooms.BuildingN umber) ON ConditionCodes. ConditionCode =
Buildings.Condi tionCode) ON OwnershipCodes. OwnershipCode =
Buildings.Owner shipCode) ON CountyCodes.Cou ntyCode =
CityCodes.Count yCode
GROUP BY Buildings.Build ingNumber;
>
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~
>
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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Dan Guzman** · Aug 20 '06, 01:35 PM

Re: Access to SQL Server Query Translation

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.Build ingNumber,
Buildings.Build ingName,
First(Ownership Codes.Ownership Code,
OwnershipCodes. OwnershipDesc,
CityCodes.CityN ame,
CountyCodes.Cou ntyName,
Buildings.Addre ss,
Buildings.YearC onstructed,
Buildings.DateO ccupancy,
Buildings.Numbe rLevels,
Buildings.Basic GrossArea,
(SELECT SUM(Rooms.Assig nableSquareFeet )
FROM Rooms
WHERE Buildings.Build ingNumber = Rooms.BuildingN umber
) AS SumOfAssignable SquareFeet,
Buildings.Unrel atedGrossArea,
Buildings.Speci alArea,
Buildings.Cover edUnenclosedGro ssArea
FROM Buildings
INNER JOIN CountyCodes
ON CityCodes.CityC ode = Buildings.CityC ode
INNER JOIN OwnershipCodes
ON OwnershipCodes. OwnershipCode = Buildings.Owner shipCode
INNER JOIN ConditionCodes
ON ConditionCodes. ConditionCode = Buildings.Condi tionCode
INNER JOIN CityCodes
ON CountyCodes.Cou ntyCode = CityCodes.Count yCode;

--
Hope this helps.

Dan Guzman
SQL Server MVP

<s_wadhwa@berke ley.eduwrote in message
news:1155837812 .840480.6380@m7 3g2000cwd.googl egroups.com...

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.Build ingNumber,
First(Buildings .BuildingName) AS FirstOfBuilding Name,
First(Ownership Codes.Ownership Code) AS FirstOfOwnershi pCode,
First(Ownership Codes.Ownership Desc) AS FirstOfOwnershi pDesc,
First(CityCodes .CityName) AS FirstOfCityName ,
First(CountyCod es.CountyName) AS FirstOfCountyNa me,
First(Buildings .Address) AS FirstOfAddress,
First(Buildings .YearConstructe d) AS FirstOfYearCons tructed,
First(Buildings .DateOccupancy) AS FirstOfDateOccu pancy,
First(Buildings .NumberLevels) AS FirstOfNumberLe vels,
First(Buildings .BasicGrossArea ) AS FirstOfBasicGro ssArea,
Sum(Rooms.Assig nableSquareFeet ) AS SumOfAssignable SquareFeet,
First(Buildings .UnrelatedGross Area) AS FirstOfUnrelate dGrossArea,
First(Buildings .SpecialArea) AS FirstOfSpecialA rea,
First(Buildings .CoveredUnenclo sedGrossArea) AS
FirstOfCoveredU nenclosedGrossA rea
FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityC ode =
Buildings.CityC ode) LEFT JOIN Rooms ON Buildings.Build ingNumber =
Rooms.BuildingN umber) ON ConditionCodes. ConditionCode =
Buildings.Condi tionCode) ON OwnershipCodes. OwnershipCode =
Buildings.Owner shipCode) ON CountyCodes.Cou ntyCode =
CityCodes.Count yCode
GROUP BY Buildings.Build ingNumber;
>
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~
>
Please can any one tell me substitue for First Function in Acess to SQL
Function.
>
Any help is appreciated.
Thanks,
S
>

**Dan Guzman** · Aug 20 '06, 01:35 PM

Re: Access to SQL Server Query Translation

First(Ownership Codes.Ownership Code,

Oops, missed one. Should be:

OwnershipCodes. OwnershipCode,

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Guzman" <guzmanda@nospa m-online.sbcgloba l.netwrote in message
news:pCZFg.1509 $yO7.177@newssv r14.news.prodig y.com...

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.Build ingNumber,
Buildings.Build ingName,
First(Ownership Codes.Ownership Code,
OwnershipCodes. OwnershipDesc,
CityCodes.CityN ame,
CountyCodes.Cou ntyName,
Buildings.Addre ss,
Buildings.YearC onstructed,
Buildings.DateO ccupancy,
Buildings.Numbe rLevels,
Buildings.Basic GrossArea,
(SELECT SUM(Rooms.Assig nableSquareFeet )
FROM Rooms
WHERE Buildings.Build ingNumber = Rooms.BuildingN umber
) AS SumOfAssignable SquareFeet,
Buildings.Unrel atedGrossArea,
Buildings.Speci alArea,
Buildings.Cover edUnenclosedGro ssArea
FROM Buildings
INNER JOIN CountyCodes
ON CityCodes.CityC ode = Buildings.CityC ode
INNER JOIN OwnershipCodes
ON OwnershipCodes. OwnershipCode = Buildings.Owner shipCode
INNER JOIN ConditionCodes
ON ConditionCodes. ConditionCode = Buildings.Condi tionCode
INNER JOIN CityCodes
ON CountyCodes.Cou ntyCode = CityCodes.Count yCode;
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<s_wadhwa@berke ley.eduwrote in message
news:1155837812 .840480.6380@m7 3g2000cwd.googl egroups.com...

>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.Build ingNumber,
>First(Building s.BuildingName) AS FirstOfBuilding Name,
>First(Ownershi pCodes.Ownershi pCode) AS FirstOfOwnershi pCode,
>First(Ownershi pCodes.Ownershi pDesc) AS FirstOfOwnershi pDesc,
>First(CityCode s.CityName) AS FirstOfCityName ,
>First(CountyCo des.CountyName) AS FirstOfCountyNa me,
>First(Building s.Address) AS FirstOfAddress,
>First(Building s.YearConstruct ed) AS FirstOfYearCons tructed,
>First(Building s.DateOccupancy ) AS FirstOfDateOccu pancy,
>First(Building s.NumberLevels) AS FirstOfNumberLe vels,
>First(Building s.BasicGrossAre a) AS FirstOfBasicGro ssArea,
>Sum(Rooms.Assi gnableSquareFee t) AS SumOfAssignable SquareFeet,
>First(Building s.UnrelatedGros sArea) AS FirstOfUnrelate dGrossArea,
>First(Building s.SpecialArea) AS FirstOfSpecialA rea,
>First(Building s.CoveredUnencl osedGrossArea) AS
>FirstOfCovered UnenclosedGross Area
>FROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodes
>INNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityC ode =
>Buildings.City Code) LEFT JOIN Rooms ON Buildings.Build ingNumber =
>Rooms.Building Number) ON ConditionCodes. ConditionCode =
>Buildings.Cond itionCode) ON OwnershipCodes. OwnershipCode =
>Buildings.Owne rshipCode) ON CountyCodes.Cou ntyCode =
>CityCodes.Coun tyCode
>GROUP BY Buildings.Build ingNumber;
>>
>~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~
>>
>Please can any one tell me substitue for First Function in Acess to SQL
>Function.
>>
>Any help is appreciated.
>Thanks,
>S
>>

>
>

Access to SQL Server Query Translation

Access to SQL Server Query Translation

Comment

Comment

Comment