|
|
Posted by Dan Guzman on 08/20/06 13:39
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]
|