MS Access SQL - Fetch data from 3 tables

Question

In MS Access, I have 3 tables as below.

Table 1 tblOrders contains following fields.

fldOrdrID | fldCustID | fldProdID | fldOrdrDate
----------|-----------|-----------|------------

Table 2 tblCustomers contains following fields.

fldCustID | fldCustName | fldCustAddr
----------|-------------|------------

Table 3 tblProducts contains following fields.

fldProdID | fldProdName
----------|------------

How should I write a SELECT statement in MS Access to basically get the very four columns from table 1 tblOrders but replace fldCustID with fldCustName and replace fldProdID with fldProdName? Something like the below.

fldOrdrID | fldCustName | fldProdName | fldOrdrDate
----------|-------------|-------------|------------

Thanks

you could do this stuff quite simply with the Query Wizard. – cha Nov 21 '13 at 23:09 — cha, Nov 21 '13 at 23:09

score 3 · Accepted Answer · edited May 23 '17 at 12:05

You want to learn about JOINs.

Basically, a join will return all rows that match an expression. That expression generally is the equality between a primary key and a foreign key.

In this case:

SELECT tblOrders.fldOrdrID, tblCustomers.fldCustName, 
    tblProducts.fldProdName, tblOrders.fldOrdrDate
FROM (tblOrders INNER JOIN tblCustomers ON tblOrders.fldCustId = tblCustomers.fldCustId) INNER JOIN tblProducts ON fldProdID = tblOrders.fldProdId

The INNER JOIN specifies both the table to JOIN on and the condition on which to join. The 'INNER' qualifier specifies that the query should only return rows when the condition is satisfied. Sometimes you might wish to also return rows where one side of the equation does not have a corresponding row but the other does. Those would be LEFT or RIGHT joins.

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

In for a penny in for a pound.

You want to avoid simply enumerating the tables and then specifying the equality condition in a WHERE clause. It's more verbose and also less readable.

INNER JOIN ON vs WHERE clause

Also don't forget to cite me in your homework.

MS Access SQL - Fetch data from 3 tables

1 Answers1