Re: [MS_AccessPros] Convert SQL View to Access SQL

 

Art-

There is not tool - you have to do it "by hand".  You could try this:

SELECT     a.UnitCode,a.[Unit#], a.Code, a.Size, d.NAME AS TenantName, g.ADDRESS1 AS Address, c.STRTDATE AS MoveInDate, a.Class, IIf(IsNull([c].[STRTDATE]),  'Vacant', [Status]) AS Status, Year(Date()) & "-" & Month(Date()) & "-" & '01' as ListingDate, a.Project, a.UnitType, a.NAHASDA, a.Community
FROM         (IS_Tenant AS a  LEFT OUTER JOIN
                          ((SELECT     LOCNCODE, MAX(DEX_ROW_ID) AS DEX
                            FROM          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          B4610002
                            GROUP BY LOCNCODE) AS c ON b.LOCNCODE = c.LOCNCODE) LEFT OUTER JOIN
                          (SELECT     NAME, MAX(DEX_ROW_ID) AS DEX
                            FROM          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          B4602200 AS B4602200_1) AS e ON b.DEX = e.DEX LEFT OUTER JOIN
                          (SELECT     CUSTNMBR, MAX(DEX_ROW_ID) AS DEX
                            FROM          RM00102
                            GROUP BY CUSTNMBR) AS f ON e.CUSTNMBR = f.CUSTNMBR LEFT OUTER JOIN
                          (SELECT     ADDRESS1, DEX_ROW_ID AS DEX
                            FROM          RM00102 AS RM00102_1) AS g ON f.DEX = g.DEX
WHERE     (LEFT(a.Unit#, 1) <> '-')

The FROM clause may need some more work - and I'm not sure that Access will accept all those chained LEFT JOINs.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Jul 12, 2016, at 7:35 PM, Steve Conklin StephenMConklin@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

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

.

__,_._,___