Convert Access Query to SQL Server View

[s_wadhwa]

SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
UCASE(Buildings.BuildingName) AS BuildingName,
Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
dCoveredUnenclosedGrossArea,
IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
Buildings.YearConstructed, Format$([NumberLevels],"00") AS
dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
dUnrelatedGrossArea, Buildings.YearLatestImprovement,
UCASE(Buildings.Address) AS Address, Buildings.CityCode,
CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
Format$([Mechanical],"0000000") AS dMechanical,
Format$([Custodial],"0000000") AS dCustodial
FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode
ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;
Please if anyone can help me in Converting the above given Access Query
to Sql Server. I don't know which function to use for format$, IIF. I
would really appreciate your suggestions.

Thanks,

[Hugo Kornelis]

On 17 May 2006 10:02:37 -0700, s_******@berkeley.edu wrote:

SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
UCASE(Buildings.BuildingName) AS BuildingName,
Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
dCoveredUnenclosedGrossArea,
IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
Buildings.YearConstructed, Format$([NumberLevels],"00") AS
dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
dUnrelatedGrossArea, Buildings.YearLatestImprovement,
UCASE(Buildings.Address) AS Address, Buildings.CityCode,
CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
Format$([Mechanical],"0000000") AS dMechanical,
Format$([Custodial],"0000000") AS dCustodial
FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode
ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;
Please if anyone can help me in Converting the above given Access Query
to Sql Server. I don't know which function to use for format$, IIF. I
would really appreciate your suggestions.

Thanks,

Hi s_wadhwa,

Change doouble quotes to single quotes.

Use + instead of & for string concatenation.

Replace UCASE() with UPPER()

Replace IIf with CASE (look it up in Books Online)

My knowledge of Format$() is very llimited, but if (for isntance) the
function Format$([Custodial],"0000000") is intended to format Custodial
as a 7-digit numeric string with leading zerooes (e.g. 0001234), then
you can replace it with:
RIGHT (REPLICATE('0', 7) + STR(Custodial), 7)

Finally, remove the constant from the ORDER BY list. It serves no
purpose.

--
Hugo Kornelis, SQL Server MVP

[s_wadhwa]

Hi Hugo,
Thanks a lot for your quick response. the suggestions were really
helpful. Is there any book to refer or some online material to refer
to. I have lots of other doubts regarding converting Access queries to
SQL.

Thanks,
Shalini

[s_wadhwa]

Hi,

If anyone has done the conversion of parameterized query in Access to
SQL Server. Please guide me how to solve this query and how to take
input in SQL query from Access forms.
PARAMETERS [[forms]!frmRooms![BuildingNumber]] Text ( 255 ),
[[forms]!frmRooms![ctlFloor]] Text ( 255 ),
[[forms]!frmRooms![DepartmentFilter]] Text ( 255 );
SELECT *
FROM Rooms
WHERE (((Rooms.BuildingNumber)=[forms]![frmRooms]![BuildingNumber]) AND
((Rooms.Floor) Like [forms]![frmRooms]![ctlFloor]) AND
((Rooms.DepartmentCode) Like
Mid$([forms]![frmRooms]![DepartmentFilter],1,4)) AND
((Rooms.RoomUseCode) Like [forms]![frmRooms]![RmCdFilter]))
ORDER BY Rooms.BuildingNumber, Rooms.RoomNumber;
thanks in advance for your help.
Shalini

[Hugo Kornelis]

On 18 May 2006 12:59:23 -0700, s_******@berkeley.edu wrote:

Hi,

If anyone has done the conversion of parameterized query in Access to
SQL Server. Please guide me how to solve this query and how to take
input in SQL query from Access forms.

Hi Shalini,

I see that your parameters refer to forms. Keep in mind that SQL Server
is not aware of the forms in yoour front-end - a big difference from
Access, which is DB and front-end lumped together in one application.

One possible way to convert this to SQL Server would be to create a
stored procedure:

CREATE PROC GoodNameGoesHere
(@BuildingNumber varchar(255),
@ctlFloor varchar(255),
@DepartmentFilter varchar(255))
AS
SELECT Col1, Col2, Col3, ... -- Never use SELECT * in production code
FROM Rooms
WHERE BuildingNumber = @BuildingNumber
AND "Floor" LIKE @ctlFloor
AND DepartmentCode LIKE SUBSTRING(@DepartmentFilter, 1, 4)
ORDER BY BuildingNumber, RoomNumber;

BTW, using a char datatype for a column named "Number" is highly
suspicious to me...

--
Hugo Kornelis, SQL Server MVP

[lakimaki]

Hi there,

I have an Access query that I've been trying to convert to SQL Server
view and although I've converted most of the syntax from access to sql
I still can't make it work. I'm getting errors within GROUP BY and
HAVING clauses. Anyone has an idea? Thanks...

SELECT DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()) AS Expr1,
GetDate()-O_DateOfAddress AS Expr3,
DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress) AS
Expr2,
qry_offender_master.O_VerificationQuarter,
qry_offender_master.O_Probation,
qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
qry_offender_master.O_CityEntry, qry_offender_master.O_State,
qry_offender_master.O_Zip, qry_offender_master.O_Sector,
tbl_state.State,
qry_offender_master.Ca_WarrantIssued,
qry_offender_master.O_DateofBirth,
qry_offender_master.O_Absconder
FROM qry_offender_master LEFT JOIN tbl_state ON
qry_offender_master.O_State = tbl_state.State
GROUP BY DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()),
GetDate()-O_DateOfAddress,
DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress),
qry_offender_master.O_VerificationQuarter,
qry_offender_master.O_Probation,
qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
qry_offender_master.O_CityEntry, qry_offender_master.O_State,
qry_offender_master.O_Zip, qry_offender_master.O_Sector,
tbl_state.State,
qry_offender_master.Ca_WarrantIssued,
qry_offender_master.O_DateofBirth,
qry_offender_master.O_Absconder
HAVING (((DateName(qq,O_DateofAddress) + '/' +
DateName(yy,O_DateofAddress)) <> DateName(qq,GetDate()) + '/' +
DateName(yy,GetDate())) AND
((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
AND ((qry_offender_master.O_Probation)=0) AND
((qry_offender_master.O_OutOfCounty)=0) AND
((qry_offender_master.O_deceased)=0) AND
((qry_offender_master.O_Sex_Probation)=0) AND
((qry_offender_master.O_Absconder)=0)) OR
(((DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress))<>
DateName(qq,GetDate()) + '/' + DateName(yy,GetDate())) AND
((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
AND ((qry_offender_master.O_Probation)=0) AND
((qry_offender_master.O_OutOfCounty)=0) AND
((qry_offender_master.O_deceased)=0) AND
((qry_offender_master.O_Sex_Probation)=0) AND
((qry_offender_master.O_DateofAddress) Is Null) AND
((qry_offender_master.O_Absconder)=0))
ORDER BY qry_offender_master.O_LastName
Hugo Kornelis wrote:

On 18 May 2006 12:59:23 -0700, s_******@berkeley.edu wrote:

Hi,

If anyone has done the conversion of parameterized query in Access to
SQL Server. Please guide me how to solve this query and how to take
input in SQL query from Access forms.

Hi Shalini,

I see that your parameters refer to forms. Keep in mind that SQL Server
is not aware of the forms in yoour front-end - a big difference from
Access, which is DB and front-end lumped together in one application.

One possible way to convert this to SQL Server would be to create a
stored procedure:

CREATE PROC GoodNameGoesHere
(@BuildingNumber varchar(255),
@ctlFloor varchar(255),
@DepartmentFilter varchar(255))
AS
SELECT Col1, Col2, Col3, ... -- Never use SELECT * in production code
FROM Rooms
WHERE BuildingNumber = @BuildingNumber
AND "Floor" LIKE @ctlFloor
AND DepartmentCode LIKE SUBSTRING(@DepartmentFilter, 1, 4)
ORDER BY BuildingNumber, RoomNumber;

BTW, using a char datatype for a column named "Number" is highly
suspicious to me...

--
Hugo Kornelis, SQL Server MVP

[lakimaki]

Ok, I've changed "GetDate()-O_DateOfAddress" to DateDiff(day,
O_DateOfAddress, GetDate()) and the only thing left has to do with
group by clause. So, is there any way I can use expressions (Expr1,
Expr2...) in GROUP By clause because obviously this is possible in
access but not in sql server...I tried both putting "Group by Expr1,
Expr2" and "Group by DateName(qq,GetDate()) + '/' +
DateName(yy,GetDate()) AS Expr1, GetDate()-O_DateOfAddress AS Expr3..."
but no success? Thanks in advance...

[s_wadhwa]

thanks for guidance.
i resolved that issue.

shalini

[Hugo Kornelis]

On 23 May 2006 05:51:47 -0700, lakimaki wrote:

Hi there,

I have an Access query that I've been trying to convert to SQL Server
view and although I've converted most of the syntax from access to sql
I still can't make it work. I'm getting errors within GROUP BY and
HAVING clauses. Anyone has an idea? Thanks... (snip)

Hi lakimaki,

There are several things in yoour query that I don't understand.

1. Why are you joining in the tblState table? Unless I am missing
something, you only use it to display the tblState.State column, which
is also the joining column. Why not display O_State and remove the join
to tblState?

2. Why do you need the GROUP BY clause? Since you include all columns in
the WHERE clause and since there are no aggregates used anywhere, this
is the same as just using DISTINCT to remove duplicates - and if your
table is well designed, there shouldn't be any duplicates. Just remove
the entire GROUP BY and change HAVING to WHERE.

3. The HAVING part looks strange too. It consists of two parts, combined
with OR. But the second part is an exact copy of the first part, with
only one extra requirement added. And that extra requirement has the
result that this second part can NEVER be true - since DateOfAddress can
never be both NULL and in the current quarter.

4. The test that DateOfAddress is in the current quarter is not done in
the most efficient way. Instead of using an expression to extract
quarter and year from this column, you shoould compare this column to
the start of the current and the next quarter - that way, an index on
DateOfAddress (if any exists) can be used to quickly narrow down the
amount of rows to process.

As far as I see, you could rewrite your query to:

SELECT DATENAME(qq, CURRENT_TIMESTAMP) + '/'
+ DATENAME(yy, CURRENT_TIMESTAMP) AS Expr1,
DATEDIFF(day, O_DateOfAddress, CURRENT_TIMESTAMP) AS Expr3,
DATENAME(qq, O_DateofAddress) + '/'
+ DATENAME(yy, O_DateofAddress) AS Expr2,
O_VerificationQuarter,
O_Probation,
O_active_case,
O_OutOfCounty,
O_deceased,
O_Sex_Probation,
O_DateofAddress,
O_Doc,
O_LastName,
O_FirstName,
O_MiddleName,
O_Address,
O_CityEntry,
O_State,
O_Zip,
O_Sector,
O_State,
Ca_WarrantIssued,
O_DateofBirth,
O_Absconder
FROM qry_offender_master
WHERE O_DateOfAddress >= DATEADD(qq,
DATEDIFF(qq, '20000101',
CURRENT_TIMESTAMP),
'20000101')
AND O_DateOfAddress < DATEADD(qq,
DATEDIFF(qq, '20000101',
CURRENT_TIMESTAMP),
'20000401')
AND O_VerificationQuarter = DATENAME(qq, CURRENT_TIMESTAMP)
AND O_Probation = 0
AND O_OutOfCounty = 0
AND O_deceased = 0
AND O_Sex_Probation = 0
AND O_Absconder = 0
ORDER BY O_LastName

(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)


SELECT DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()) AS Expr1,
GetDate()-O_DateOfAddress AS Expr3,
DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress) AS
Expr2,
qry_offender_master.O_VerificationQuarter,
qry_offender_master.O_Probation,
qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
qry_offender_master.O_CityEntry, qry_offender_master.O_State,
qry_offender_master.O_Zip, qry_offender_master.O_Sector,
tbl_state.State,
qry_offender_master.Ca_WarrantIssued,
qry_offender_master.O_DateofBirth,
qry_offender_master.O_Absconder
FROM qry_offender_master LEFT JOIN tbl_state ON
qry_offender_master.O_State = tbl_state.State
GROUP BY DateName(qq,GetDate()) + '/' + DateName(yy,GetDate()),
GetDate()-O_DateOfAddress,
DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress),
qry_offender_master.O_VerificationQuarter,
qry_offender_master.O_Probation,
qry_offender_master.O_active_case, qry_offender_master.O_OutOfCounty,
qry_offender_master.O_deceased, qry_offender_master.O_Sex_Probation,
qry_offender_master.O_DateofAddress, qry_offender_master.O_Doc,
qry_offender_master.O_LastName, qry_offender_master.O_FirstName,
qry_offender_master.O_MiddleName, qry_offender_master.O_Address,
qry_offender_master.O_CityEntry, qry_offender_master.O_State,
qry_offender_master.O_Zip, qry_offender_master.O_Sector,
tbl_state.State,
qry_offender_master.Ca_WarrantIssued,
qry_offender_master.O_DateofBirth,
qry_offender_master.O_Absconder
HAVING (((DateName(qq,O_DateofAddress) + '/' +
DateName(yy,O_DateofAddress)) <> DateName(qq,GetDate()) + '/' +
DateName(yy,GetDate())) AND
((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
AND ((qry_offender_master.O_Probation)=0) AND
((qry_offender_master.O_OutOfCounty)=0) AND
((qry_offender_master.O_deceased)=0) AND
((qry_offender_master.O_Sex_Probation)=0) AND
((qry_offender_master.O_Absconder)=0)) OR
(((DateName(qq,O_DateofAddress) + '/' + DateName(yy,O_DateofAddress))<>
DateName(qq,GetDate()) + '/' + DateName(yy,GetDate())) AND
((qry_offender_master.O_VerificationQuarter)= DateName(qq,GetDate()))
AND ((qry_offender_master.O_Probation)=0) AND
((qry_offender_master.O_OutOfCounty)=0) AND
((qry_offender_master.O_deceased)=0) AND
((qry_offender_master.O_Sex_Probation)=0) AND
((qry_offender_master.O_DateofAddress) Is Null) AND
((qry_offender_master.O_Absconder)=0))
ORDER BY qry_offender_master.O_LastName
Hugo Kornelis wrote:

On 18 May 2006 12:59:23 -0700, s_******@berkeley.edu wrote:

>Hi,
>
>If anyone has done the conversion of parameterized query in Access to
>SQL Server. Please guide me how to solve this query and how to take
>input in SQL query from Access forms.

Hi Shalini,

I see that your parameters refer to forms. Keep in mind that SQL Server
is not aware of the forms in yoour front-end - a big difference from
Access, which is DB and front-end lumped together in one application.

One possible way to convert this to SQL Server would be to create a
stored procedure:

CREATE PROC GoodNameGoesHere
(@BuildingNumber varchar(255),
@ctlFloor varchar(255),
@DepartmentFilter varchar(255))
AS
SELECT Col1, Col2, Col3, ... -- Never use SELECT * in production code
FROM Rooms
WHERE BuildingNumber = @BuildingNumber
AND "Floor" LIKE @ctlFloor
AND DepartmentCode LIKE SUBSTRING(@DepartmentFilter, 1, 4)
ORDER BY BuildingNumber, RoomNumber;

BTW, using a char datatype for a column named "Number" is highly
suspicious to me...

--
Hugo Kornelis, SQL Server MVP

--
Hugo Kornelis, SQL Server MVP