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 sub-select question

NewBe Access Question

Hi folks,

I am having trouble building a query that retrieves what I need. The table I query is pre-filled with rows of records both empty and filled that I will continue to fill or empty from a form. Two columns in the table are completely pre-filled, the ID column and another, which has three different text categories. I need to sub-select an entire group of rows with one kind of text entry and then select the lowest ID value in that group whos columns are otherwise empty.

The table might look like:

ID CAT INFO2 INFO3 Etc.
1 aaaa
2 aaaa something
3 aaaa
4 bbbb
5 bbbb something
6 bbbb
7 cccc
8 cccc
9 cccc something

So, if Row ID 4 was the MIN ID of CAT bbbb whos columns were otherwise empty, I would like to query it and fill the other columns.

SELECT *
FROM Tbl
WHERE ID = (SELECT MIN(ID) FROM Tbl)

The above returns the lowest ID row in the entire table, of course. Not what I wanted.

SELECT *
FROM Tbl
WHERE ID = (SELECT MIN(ID) FROM Tbl)
AND Cat = 'bbbb'
AND Info2 IS NULL

This returns a No records returned message. Of course! I presume it does so because the lowest ID for an empty bbbb category is not the lowest ID in the table.

Any help would be greatly appreciated.

RosieI hope I understood this correctly.

To return the lowest ID for a certain category has a null value do the following.

SELECT *
FROM Tbl
WHERE ID = (SELECT MIN(ID) FROM Tbl WHERE Cat = 'bbbb'
AND Info2 IS NULL)

Note that there is another where statement in the sub-query

Machado|||Yes, you understood it perfectly!

Two WHERE's. That did it!

Thanks soooo much.

'Rosie'

Access SQL problems using MS Jet OLEDB with ADO+VB6

Hi there. I am having trouble with querying a record set by date range using the Between operator. Im using VB 6 with ADO.

Referenced: Microsoft ActiveX Data Objects 2.8 Library

Connection String: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\localdata\orders.mdb;Persist Security Info=False"

SQL Query used: "SELECT * FROM tbl_orders WHERE (InsertDate BETWEEN #12/07/2004# AND #14/07/2004#)"

Code:

QueryString is passed as "(InsertDate BETWEEN #12/07/2004# AND #14/07/2004#)"

Public Function GetOrders(ByRef OrderArray() As OrderDetails, QueryString As String) As Boolean

On Error GoTo errorHandler

'\\ Adds a new customer to the local database.

Dim dbConnection As ADODB.Connection
Dim dbRecordSet As ADODB.Recordset
Dim intLoopIndex As Integer

If Len(Application.LocalDBConnection) < 1 Then

MsgBox "No local database connection parameter provided. Please run the configuration utility. Unable to get orders.", 48, "Warning"

Exit Function

End If

Set dbConnection = New ADODB.Connection
Set dbRecordSet = New ADODB.Recordset

'Connect to the database
dbConnection.ConnectionString = Application.LocalDBConnection
dbConnection.Open

'Build SQL command
With dbRecordSet

.Open "SELECT * FROM tbl_orders WHERE " & QueryString, dbConnection, adOpenStatic, adLockReadOnly

If Not (.RecordCount < 1) Then

ReDim OrderArray(.RecordCount - 1)

intLoopIndex = 0

.MoveFirst

Do

OrderArray(intLoopIndex).Customer = .Fields("Customer").Value
OrderArray(intLoopIndex).CustOrdNo = .Fields("CustOrdNo").Value
OrderArray(intLoopIndex).Description = .Fields("Description").Value
OrderArray(intLoopIndex).User = .Fields("User").Value
OrderArray(intLoopIndex).Total = .Fields("Total").Value
OrderArray(intLoopIndex).InsertDate = .Fields("InsertDate").Value

.MoveNext

intLoopIndex = (intLoopIndex + 1)

Loop Until (.EOF)

GetOrders = True

End If

End With

'Close recordset and connection
If (dbRecordSet.State = adStateOpen) Then

dbRecordSet.Close

End If

If (dbConnection.State = adStateOpen) Then

dbConnection.Close

End If

'Dispose of DB objects
Set dbConnection = Nothing
Set dbRecordSet = Nothing

Exit Function

errorHandler:

'Close recordset and connection
If (dbRecordSet.State = adStateOpen) Then

dbRecordSet.Close

End If

If (dbConnection.State = adStateOpen) Then

dbConnection.Close

End If

'Dispose of DB objects
Set dbConnection = Nothing
Set dbRecordSet = Nothing

ShowError Err, "modFunctions.GetOrders()"

End Function


Problem:

The recordset returns a record that has the field 'InsertDate' equal to 15/07/2004. This is not within my date range specified.

The actual orders.mdb file is attached to this post.

Any help much appreciated!

Regards,
-Matthew Hall.Hi,

The following query will solve your requirement.

SELECT * FROM tbl_orders WHERE (InsertDate BETWEEN #7/12/2004# AND #7/14/2004#)

I believe Access evaluates date values as mm/dd/yyyy by default. If a supplied value is not valid in this format, Access will attempt to convert it. Therefore, the query you supplied would be equal to ... BETWEEN #12/07/2004# AND #7/14/2004#, which would indeed return 7/15/2004.|||Thank you for your response.

Is this not dependent on the systems regional settings?

Is there an option I can set in the actual Access file that will set the date to be in the correct format, or am I forced to use American format dates?

Access pass-through query Doesn't work

I am having trouble getting a pass-through query in Microsoft Access 2002 to
work on one of my computers. Here's the setup:
Computer 1: Windows XP Professional, MDAC 2.8, MS Access 2002
Computer 2: Windows 2000 Server, MDAC 2.6, MS Access 2002, Running SQL Serve
r 2000
My Pass Through Query invokes a stored procedure in SQL Server 2000 on Compu
ter 2 and returns the number of records found in the query. The pass through
query uses an ODBC Connection to get to SQL Server 2000.
When I use the Query in computer 2 (same one with the SQL Server) the correc
t answer is returned. When I use the same query in computer 1, the answer r
eturned is always 0 (zero), which is incorrect.
I can use the same query through the Enterprise Manager on SQL Server 2000 a
nd it also returns the correct answer.
I have upgraded the MDAC version from 2.5RTM to 2.8 on Computer 1, but it ma
de no difference. When I use the same ODBC Connection on Computer 1 that I
used with other linked tables on the same Microsoft Access database on compu
ter 1 (linked to SQL Server
on computer 2), I am able to successfully view, modify or delete the data on
the SQL server, so it shouldn't be a permission problem.
Does anyone have any suggestions as to how I might resolve this problem?
Thanks for any helpful suggestions.
dm at herald21.com (for real email address, remove spaces and replace at wit
h at symbol)Try running profiler when you execute the query from the PC
where it's not working - that may give you some more
information on what's going on.
Also, did you use SET NOCOUNT ON in the stored procedure? If
not, try adding that to the stored procedure.
If this is the only stored procedure or PT query that you
are having the problems with, you may want to post the ddl
for the stored procedure as it could help in understanding
what the issues might be.
-Sue
On Wed, 17 Mar 2004 10:01:10 -0800, "Dick Marcum"
<anonymous@.discussions.microsoft.com> wrote:

>I am having trouble getting a pass-through query in Microsoft Access 2002 t
o work on one of my computers. Here's the setup:
>Computer 1: Windows XP Professional, MDAC 2.8, MS Access 2002
>Computer 2: Windows 2000 Server, MDAC 2.6, MS Access 2002, Running SQL Serv
er 2000
>My Pass Through Query invokes a stored procedure in SQL Server 2000 on Comp
uter 2 and returns the number of records found in the query. The pass throug
h query uses an ODBC Connection to get to SQL Server 2000.
>When I use the Query in computer 2 (same one with the SQL Server) the corre
ct answer is returned. When I use the same query in computer 1, the answer
returned is always 0 (zero), which is incorrect.
>I can use the same query through the Enterprise Manager on SQL Server 2000
and it also returns the correct answer.
>I have upgraded the MDAC version from 2.5RTM to 2.8 on Computer 1, but it made no d
ifference. When I use the same ODBC Connection on Computer 1 that I used with other
linked tables on the same Microsoft Access database on computer 1 (linked to SQL Se
rve
r on computer 2), I am able to successfully view, modify or delete the data on the SQL serv
er, so it shouldn't be a permission problem.
>Does anyone have any suggestions as to how I might resolve this problem?
>Thanks for any helpful suggestions.
>dm at herald21.com (for real email address, remove spaces and replace at with at sy
mbol)|||Thanks Sue for steering me in the right direction. The Profiler did indeed
point out the error of my ways. It turned out the pass-through query was no
t passing through a required leading 0 on one of the varchar parameters. I
have corrected that and it
works correctly now. Thanks again
Dick