Re: [MS_AccessPros] Convert SQL View to Access SQL

 

Art:

CASE WHEN and CAST will not work in Access.

Can you link to the view and just leave it in MSSQL?

Otherwise, in Access, link to all the tables referenced,  in the TSQL, change "dbo." to " dbo_", paste it into an Access query and see what (else) breaks. I don't know of a tool to go that direction, mostly I see products for going from Access to MSSQL. 

-Steve

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, July 11, 2016 5:35 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Convert SQL View to Access SQL

 

 

I need to convert the following TSQL statement to Access SQL:

SELECT     a.UnitCode,a.[Unit#], a.Code, a.Size, d.NAME AS TenantName, g.ADDRESS1 AS Address, c.STRTDATE AS MoveInDate, a.Class, CASE WHEN c.STRTDATE IS NULL
                      THEN 'Vacant' ELSE [Status] END AS Status, CAST(cast(year(getdate()) as CHAR(4))+'-'+cast(datepart(mm,getdate()) as CHAR(2))+'-'+'01' as DATE) as ListingDate, a.Project, a.UnitType, a.NAHASDA, a.Community
FROM         dbo.IS_Tenant AS a  LEFT OUTER JOIN
                          (SELECT     LOCNCODE, MAX(DEX_ROW_ID) AS DEX
                            FROM          dbo.B4602200
                            WHERE      (BSSI_Tenant_Lease_Status = 2)
                            GROUP BY LOCNCODE) AS b ON a.UnitCode = b.LOCNCODE LEFT OUTER JOIN
                          (SELECT     LOCNCODE, MAX(STRTDATE) AS STRTDATE
                            FROM          dbo.B4610002
                            GROUP BY LOCNCODE) AS c ON b.LOCNCODE = c.LOCNCODE LEFT OUTER JOIN
                          (SELECT     NAME, MAX(DEX_ROW_ID) AS DEX
                            FROM          dbo.B4602200 AS B4602200_2
                            WHERE      (BSSI_Tenant_Lease_Status = 2)
                            GROUP BY NAME) AS d ON b.DEX = d.DEX LEFT OUTER JOIN
                          (SELECT     CUSTNMBR, DEX_ROW_ID AS DEX
                            FROM          dbo.B4602200 AS B4602200_1) AS e ON b.DEX = e.DEX LEFT OUTER JOIN
                          (SELECT     CUSTNMBR, MAX(DEX_ROW_ID) AS DEX
                            FROM          dbo.RM00102
                            GROUP BY CUSTNMBR) AS f ON e.CUSTNMBR = f.CUSTNMBR LEFT OUTER JOIN
                          (SELECT     ADDRESS1, DEX_ROW_ID AS DEX
                            FROM          dbo.RM00102 AS RM00102_1) AS g ON f.DEX = g.DEX
WHERE     (LEFT(a.Unit#, 1) <> '-')

Any ideas? Is there a tool that I could use maybe.

Thank you,

Art Lorenzini

Sioux Falls, SD

__._,_.___

---

Posted by: Steve Conklin <stephenmconklin@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (2)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___