473,218 Members | 1,483 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

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

Aug 17 '06 #1
3 4396
(s_******@berkeley.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.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 17 '06 #2
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_******@berkeley.eduwrote in message
news:11********************@m73g2000cwd.googlegrou ps.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

Aug 20 '06 #3
First(OwnershipCodes.OwnershipCode,
Oops, missed one. Should be:

OwnershipCodes.OwnershipCode,

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Guzman" <gu******@nospam-online.sbcglobal.netwrote in message
news:pC****************@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_******@berkeley.eduwrote in message
news:11********************@m73g2000cwd.googlegrou ps.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


Aug 20 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Frnak McKenney | last post by:
Back when computer dinosaurs roamed the earth and the precursors to today's Internet were tiny flocks of TDMs living symbiotically with the silicon giants, tracking access to data processing...
6
by: Jegger | last post by:
Hello! We have following situation; network with 100 users, aplication developed in Access, user DB deployed on SQL Server. Is it better to create query inside aplication (with code) and then...
4
by: Bon | last post by:
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to...
3
by: Jon Ole Hedne | last post by:
My Access 2002-application need to work with tables from both Oracle and Access. To solve this, I want to run some querys on three views in Oracle and import the results into temporary...
29
by: Mark B | last post by:
We have an Access app (quite big) at www.orbisoft.com/download. We have had requests by potential users to have it converted to an SQL version for them since there corporate policy excludes them...
3
by: Rahul Agarwal | last post by:
Hi In our web page we use a combination of HTML and server side controls and some of them have a custom attribute based on which we need to find and replace the values once the HTML is ready. ...
11
by: DraguVaso | last post by:
Hi, I want to make a small application in VB.NET that relinks all the query's and tables in an Access database that are linked via ODBC to an SQL Server. It must be able to relink all the tables...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
13
by: magickarle | last post by:
Hi, I got a pass-through query (that takes about 15 mins to process) I would like to integrate variables to it. IE: something simple: Select EmplID from empl_Lst where empl_lst.timestamp between...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.