Posted by Dan Guzman on 08/20/06 13:47

> First(OwnershipCodes.OwnershipCode,

Oops, missed one. Should be:

OwnershipCodes.OwnershipCode,

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:pCZFg.1509$yO7.177@newssvr14.news.prodigy.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.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.edu> wrote in message
> news:1155837812.840480.6380@m73g2000cwd.googlegroups.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.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
>>
>
>

[Back to original message]