Sql – How to make a select query for sql and access databases

ms-accesssql server

Using SQL server 2000 and Access 2003

Access Database Name - History.mdb 
Access Table Name - Events

SQL Database Name - Star.mdf
SQL Table Name - Person

I want to take the field from person table, and include in Events table by using inner join

Tried Query

Select * from Events inner join person where events.id = person.id

So How to make a query for access and sql databases.

I want to make a Select query in access only. Not an sql Database.

Need Query Help?

Best Answer

While you can (possible, should -- why?) use a linked table, there are as ever more than one way to skin a cat. Here's another approach: put the connection details into the query test e.g. something like

SELECT * 
  FROM [ODBC;Driver={SQL Server};SERVER=MyServer;DATABASE=Star;UID=MyUsername;Pwd=MyPassword;].Person AS P1 
       INNER JOIN 
       [MS Access;DATABASE=C:\History;].[Events] AS E1
          ON S1.seq = S2.seq
 WHERE E1.id = P1.id;

Related Solutions

Sql – How to perform an IF…THEN in an SQL SELECT

The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT CAST(
             CASE
                  WHEN Obsolete = 'N' or InStock = 'Y'
                     THEN 1
                  ELSE 0
             END AS bit) as Saleable, *
FROM Product

You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works:

SELECT CASE
            WHEN Obsolete = 'N' or InStock = 'Y'
               THEN 1
               ELSE 0
       END as Saleable, *
FROM Product

CASE statements can be embedded in other CASE statements and even included in aggregates.

SQL Server Denali (SQL Server 2012) adds the IIF statement which is also available in access (pointed out by Martin Smith):

SELECT IIF(Obsolete = 'N' or InStock = 'Y', 1, 0) as Saleable, * FROM Product

Sql-server – How to check if a column exists in a SQL Server table

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Best Answer

Related Solutions

Sql – How to perform an IF…THEN in an SQL SELECT

Sql-server – How to check if a column exists in a SQL Server table

Related Topic