ACCESS FEED: RE: [MS_AccessPros] Re: r SQL Server query to Access

Senin, 13 April 2015

RE: [MS_AccessPros] Re: r SQL Server query to Access

Oh duh. That makes sense. I use them all the time for field names, but a table name alias is actually a really good idea.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, April 13, 2015 11:59 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: r SQL Server query to Access

Liz

That's what is known as an alias. It is usually a "nickname" for something. Note how I can refer to the table by just typing "a."

SELECT a.CustID

FROM Customers as a

WHERE a.Active<>0

-is the same as-

SELECT Customers.CustID

FROM Customers

WHERE Customers.Active<>0

It comes in handy when a table has a long name. You can also see Access do that for you if you use the same table more than one time in a query, But Access just adds a number to the table name like Customers and Customers_1. It's not shorter but it gives Access a way to tell which field belongs to which table.

-Bill

---In MS_Access_Professionals@yahoogroups.com, <liz_ravenwood@beaerospace.com> wrote :

The "as a" before inner join doesn't look right either. I think Access sql doesn't have those words.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Monday, April 13, 2015 10:54 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: r SQL Server query to Access

Art

First save each of those 2 nested queries as queries. Then use them as tables in your main query. If you don't want to do that then try putting brackets around Class. It's probably a reserved word.

custclas AS [Class]

---In MS_Access_Professionals@yahoogroups.com, <dbalorenzini@yahoo.com> wrote :

Is there a app or can someone help me to convert the following SQL Server Query to Access query:

SELECT a.CustID, a.UnitID, *
FROM (select custnmbr AS CustID, custclas AS Class, ADRSCODE AS UnitID,Hold,
CUSTNAME AS TenantName,
address1, address2, city, state, zip from RM00101
where INACTIVE = 0 and city <> ' ' AND CUSTCLAS='Rental' and Hold = 0

) AS a INNER JOIN (Select CUSTNMBR, CUSTNAME, LOCNCODE, LOC! NDSCR, BSSI_PortfolioID, BSSI_Description1,
BSSI_Tenant_Lease_Status, BSSI_Lease_Execution_Dat, BSSI_Lease_Termination_D
From B4602200 where
BSSI_Tenant_Lease_Status = 2) AS b ON a.CustID = b.CUSTNMBR;

WHen I try to run this in ACCESS I get ODBC call error.

Thank you

Art Lorenzini

South Dakota.

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.