convert from access to sql server please help

Question

convert from access to sql server please help

draganl2000

SSC Rookie

Points: 37
More actions
March 22, 2015 at 9:40 pm

#298912

PLEASE HELP TO CONVERT SQL ACCESS TO SQL SERVER PLEASE
I HAVE THIS IN ACCESS
BELLOW I NEED TO CONVERT TO SQL SERVER
TRANSFORM First(Eval1to4.answer) AS FirstOfanswer SELECT Eval1to4.evalOid, Membershiptypemap.mappedvalue as membership, First(Eval1to4.answer) AS [Total Of answer] FROM (Members RIGHT JOIN (Eval1to4 LEFT JOIN Orders ON Eval1to4.evalOid = Orders.oid) ON Members.CID = Orders.cid) LEFT JOIN MembershipTypeMap ON (Members.MembershipStatus = MembershipTypeMap.membershipstatus) AND (Members.Membership = MembershipTypeMap.membershiptype) WHERE Orders.program = 20141128 AND Eval1to4.evalProgID=20141128 GROUP BY Eval1to4.evalOid, Membershiptypemap.mappedvalue ORDER BY Lastname, Firstname PIVOT Eval1to4.questionID

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63421 More actions · Answer 1

No need to shout... we can hear you fine.

You should read Jeff Moden's articles on crosstabs. Of course, if you're doing the crosstab in a report, you can just use a matrix and it does all the pivoting for you. Crosstabs and Pivots, Part 1[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

You might also add the occasional line break in your posted code so it can actually be read a little easier. 😉 A better idea would be to put your code in between one of the IFCode shortcuts that's available on the left side of the window that you type your post in. That's after you add the line breaks, of course.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

draganl2000 SSC Rookie Points: 37 More actions · Answer 3

that is my code

TRANSFORM First(Eval1to4.answer) AS FirstOfanswer SELECT Eval1to4.evalOid, Membershiptypemap.mappedvalue as membership, First(Eval1to4.answer) AS [Total Of answer] FROM (Members RIGHT JOIN (Eval1to4 LEFT JOIN Orders ON Eval1to4.evalOid = Orders.oid) ON Members.CID = Orders.cid) LEFT JOIN MembershipTypeMap ON (Members.MembershipStatus = MembershipTypeMap.membershipstatus) AND (Members.Membership = MembershipTypeMap.membershiptype) WHERE Orders.program = 20141128 AND Eval1to4.evalProgID=20141128 GROUP BY Eval1to4.evalOid, Membershiptypemap.mappedvalue PIVOT Eval1to4.questionID

please any help

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

draganl2000 (3/23/2015)
that is my code
TRANSFORM First(Eval1to4.answer) AS FirstOfanswer SELECT Eval1to4.evalOid, Membershiptypemap.mappedvalue as membership, First(Eval1to4.answer) AS [Total Of answer] FROM (Members RIGHT JOIN (Eval1to4 LEFT JOIN Orders ON Eval1to4.evalOid = Orders.oid) ON Members.CID = Orders.cid) LEFT JOIN MembershipTypeMap ON (Members.MembershipStatus = MembershipTypeMap.membershipstatus) AND (Members.Membership = MembershipTypeMap.membershiptype) WHERE Orders.program = 20141128 AND Eval1to4.evalProgID=20141128 GROUP BY Eval1to4.evalOid, Membershiptypemap.mappedvalue PIVOT Eval1to4.questionID
please any help

You forgot the most important part... line breaks.

The first step in any code analysis is being able to easily read the code. I was hoping you'd spend a little time on your problem before we did. Here's your code with some line breaks and appropriate indentation.

TRANSFORM First(Eval1to4.answer) AS FirstOfanswer

SELECT Eval1to4.evalOid

,Membershiptypemap.mappedvalue AS membership

,First(Eval1to4.answer) AS [Total Of answer]

FROM Members

RIGHT JOIN Eval1to4

LEFT JOIN Orders

ON Eval1to4.evalOid = Orders.oid

ON Members.CID = Orders.cid --This could possibly be converted to an AND

LEFT JOIN MembershipTypeMap

ON Members.MembershipStatus = MembershipTypeMap.membershipstatus

AND Members.Membership = MembershipTypeMap.membershiptype

WHERE Orders.program = 20141128

AND Eval1to4.evalProgID = 20141128

GROUP BY Eval1to4.evalOid, Membershiptypemap.mappedvalue

PIVOT Eval1to4.questionID

;

I notice that there's a PIVOT in that code. PIVOT in MS Access is much more sophisticated and easy to use than what's in SQL Server (MS really dropped the ball there). Of course, it's so sophisticated that I can't actually figure out what will be pivoted as a result. Can you do a screen shot of what the result of this query in Access is, please, and then attach it to a post?

Remembering that this is an SQL Server forum, I have no clue as to what TRANSFORM in Access actually does. Can you shed a bit of light on that, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

draganl2000 SSC Rookie Points: 37 More actions · Answer 5

HI

Thanks for reply that is code I delete order by give me error in access . In attachment is result . please help

TRANSFORM First(Eval1to4.answer) AS FirstOfanswer SELECT Eval1to4.evalOid, Membershiptypemap.mappedvalue as membership, First(Eval1to4.answer) AS [Total Of answer] FROM (Members RIGHT JOIN (Eval1to4 LEFT JOIN Orders ON Eval1to4.evalOid = Orders.oid) ON Members.CID = Orders.cid) LEFT JOIN MembershipTypeMap ON (Members.MembershipStatus = MembershipTypeMap.membershipstatus) AND (Members.Membership = MembershipTypeMap.membershiptype) WHERE Orders.program = 20141128 AND Eval1to4.evalProgID=20141128 GROUP BY Eval1to4.evalOid, Membershiptypemap.mappedvalue PIVOT Eval1to4.questionID

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

draganl2000 (3/23/2015)
HI
Thanks for reply that is code I delete order by give me error in access . In attachment is result . please help
TRANSFORM First(Eval1to4.answer) AS FirstOfanswer SELECT Eval1to4.evalOid, Membershiptypemap.mappedvalue as membership, First(Eval1to4.answer) AS [Total Of answer] FROM (Members RIGHT JOIN (Eval1to4 LEFT JOIN Orders ON Eval1to4.evalOid = Orders.oid) ON Members.CID = Orders.cid) LEFT JOIN MembershipTypeMap ON (Members.MembershipStatus = MembershipTypeMap.membershipstatus) AND (Members.Membership = MembershipTypeMap.membershiptype) WHERE Orders.program = 20141128 AND Eval1to4.evalProgID=20141128 GROUP BY Eval1to4.evalOid, Membershiptypemap.mappedvalue PIVOT Eval1to4.questionID

Heh... You must really not want help. I'm not going to reformat that mess again just to read it. Please add some line breaks and indenting. Also, on the output of the query in the attachment... it would be nice if you took 2 seconds to expand the column headings large enough to read them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

draganl2000 SSC Rookie Points: 37 More actions · Answer 7

Sorry . I really need help . I hope that is ok now

In attachment that is images when I run this query

regards

TRANSFORM First(Eval1to4.answer) AS FirstOfanswer

SELECT Eval1to4.evalOid, Membershiptypemap.mappedvalue as membership,

First(Eval1to4.answer) AS [Total Of answer]

FROM

(Members RIGHT JOIN (Eval1to4 LEFT JOIN Orders ON Eval1to4.evalOid = Orders.oid)

ON Members.CID = Orders.cid)

LEFT JOIN

MembershipTypeMap ON (Members.MembershipStatus = MembershipTypeMap.membershipstatus)

AND (Members.Membership = MembershipTypeMap.membershiptype)

WHERE Orders.program = 20141128

AND

Eval1to4.evalProgID=20141128

GROUP BY Eval1to4.evalOid, Membershiptypemap.mappedvalue

PIVOT Eval1to4.questionID

pietlinden SSC Guru Points: 63421 More actions · Answer 8

Are you going to show the results of this in Reporting Services? (Yes, I know Jeff hates SSRS...)

If so, you don't need to do the Pivot in SQL Server -- do it in SSRS.

Also, can you explain what the query is doing?

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

pietlinden (3/24/2015)
Are you going to show the results of this in Reporting Services? (Yes, I know Jeff hates SSRS...)
If so, you don't need to do the Pivot in SQL Server -- do it in SSRS.
Also, can you explain what the query is doing?

Heh... agreed... on both accounts. 😛 A "Matrix" will do the job just fine.

Anyone got any idea what TRANSFORM does in the Access code that's been posted?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63421 More actions · Answer 10

TRANSFORM in Access does a pivot, but I'm not clear on what the pivot is even supposed to do in this case.

Could you post some original data (well the data structure with some fake data if you want), and what it should look like after the transform?