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
– cha

2013-11-21 23:09:30 +00:00
Commented Nov 21, 2013 at 23:09 — cha
– cha, Commented Nov 21, 2013 at 23:09

Community · Accepted Answer · 2017-05-23 12:05:04Z

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.

Collectives™ on Stack Overflow

MS Access SQL - Fetch data from 3 tables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related