MS Access query to SQL Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

MS Access query to SQL Procedure

Author

Topic

Amini
Starting Member

6 Posts

Posted - 2009-04-01 : 07:03:18

in MS Access used this query to make payroll. I want to shift my data from MS Access to SQL Server. I want to rewrite this query in stored procedrue. Please how calculate the Salary and reuse salary amount in next calculation.

SELECT tblRecords.RegNo,tblRecords.Active, Round(IIf([Active],[Basic],0),0) AS Salary, IIf([Per],Round([Salary]*0.06,2),0) AS Atya FROM tblRecords

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-01 : 07:15:56

I don't work on access.. But shouldn't there be a condition check in IIF.
As in, shouldn't it be like,,,

Round(IIf([Active]>0,[Basic],0),0) AS Salary

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-01 : 07:24:18

You can use CASE condition for IIF statements in SQL Server.You can see BOL for more info on how to use CASE statements

Amini
Starting Member

6 Posts

Posted - 2009-04-01 : 07:41:16

I have used CASE and successfuly Get the salary but how to use salary in my next calculation.

I need this result

RegNo, Active, Basic, Salary, Atiya
202, True, 4276, 4276, 256.56

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-01 : 07:50:45

Please post some sample data & output and what do you mean by next calculation?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-01 : 07:51:36

You need to provide more sample data and expected output. You reference a Per column but don't show its value anywhere. It would also help to show the full structure of the table(s) involved.

edit: What he said.

Amini
Starting Member

6 Posts

Posted - 2009-04-01 : 08:58:52

I am uploading the part of my original project which is in mdb format. In this file 1 table 1 query 1 report. Now i want to deal all this with ms access project and back end databse is in Server 2005. In short i want get same result in sql server. link is below


http://cid-dac7bb37eb0d10ab.skydrive.live.com/self.aspx/Just%20Amini/amla.mdb

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-01 : 10:02:58

You will have to create UDF for getting salary etc.I did it for Salary

Create function dbo.getsalary(@sno int)
returns int
as

Begin
Declare @salary int
Select @salary=Round(Case WHEN Active>0 THEN Case when [CutDay]=0 THEN [Basic]+[Alwnc]
ELSE [Basic]+[Alwnc]/[DefaultDay]*[DefaultDay]-[CutDay]END ELSE 0 END,0)FROM tblRecords
WHERE (((tblRecords.Active)=1))and tblRecords.sno=@sno
return @salary
End

And then use a query like this

SELECT tblRecords.inoffice, tblRecords.SNo, tblRecords.RegNo, tblRecords.MoosiRate, tblRecords.Name,
tblRecords.Active, tblRecords.Per, tblRecords.Moosi, tblRecords.DefaultDay, tblRecords.CutDay,
[DefaultDay]-[CutDay] AS WrkDay, tblRecords.Basic,
tblRecords.Alwnc, tblRecords.ExAlwnc, tblRecords.ExAlwnc AS [All] ,
dbo.getsalary(tblRecords.SNo)AS Salary,
Case when [Per]>0 Then Round(dbo.getsalary(tblRecords.SNo)*0.06,2) End AS Atya
FROM tblRecords
WHERE (((tblRecords.Active)=1))
order by Sno

Amini
Starting Member

6 Posts

Posted - 2009-04-01 : 11:12:19

Thank you, now i get to know the method to calculate. I will fix my other problems.
Thank you again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources