One to many relationship

[Shadow]

Any kind of advice is much appreciated to help me overcome this problem.

I have a Customer and Orders table In an access application with a one to
many relationship.
I have also created a query by the following rst:
--------------------------------------------------------------
SELECT tblCustomer.*, tblOrders.* FROM tblCustomer INNER JOIN tblOrders ON
tblCustomer.ID = tblOrders.CustomerID;
--------------------------------------------------------------
Running this query, shows only those Customers who have some records in
Orders table. Is there any way to make this query to show all Customers even
if they haven't any records in Orders table?


many thanks for any kind of advice.
Access 2003

 

[tina]

try changing the INNER JOIN to LEFT JOIN

hth

 

[Dirk Goldgar]

Any kind of advice is much appreciated to help me overcome this
problem.

I have a Customer and Orders table In an access application with a
one to many relationship.
I have also created a query by the following rst:
--------------------------------------------------------------
SELECT tblCustomer.*, tblOrders.* FROM tblCustomer INNER JOIN
tblOrders ON tblCustomer.ID = tblOrders.CustomerID;
--------------------------------------------------------------
Running this query, shows only those Customers who have some records
in Orders table. Is there any way to make this query to show all
Customers even if they haven't any records in Orders table?


many thanks for any kind of advice.
Access 2003

Note: since this is plainly a question about a query, you should have
posted it to the .queries newsgroup alone. Certainly the .formscoding
and .modulescoding have no conceivable relevance.

To answer your question, you need a LEFT JOIN instead of an INNER JOIN:

SELECT tblCustomer.*, tblOrders.*
FROM tblCustomer LEFT JOIN tblOrders
ON tblCustomer.ID = tblOrders.CustomerID;

That will include all records from tblCustomer, and only the matching
records from tblOrders.

 

[Shadow]

thanks million for your sollution and sorry for using multiple newsgroups

 

[Shadow]

million thanks for your solution.

try changing the INNER JOIN to LEFT JOIN

hth

 

[tina]

you're very welcome

million thanks for your solution.

 

[adsl]

Any kind of advice is much appreciated to help me overcome this problem.

I have a Customer and Orders table In an access application with a one to
many relationship.
I have also created a query by the following rst:
--------------------------------------------------------------
SELECT tblCustomer.*, tblOrders.* FROM tblCustomer INNER JOIN tblOrders ON
tblCustomer.ID = tblOrders.CustomerID;
--------------------------------------------------------------
Running this query, shows only those Customers who have some records in
Orders table. Is there any way to make this query to show all Customers
even if they haven't any records in Orders table?


many thanks for any kind of advice.
Access 2003

 

[adsl]

try changing the INNER JOIN to LEFT JOIN

hth

 

[adsl]

million thanks for your solution.

 

[adsl]

you're very welcome

 

[adsl]

Note: since this is plainly a question about a query, you should have
posted it to the .queries newsgroup alone. Certainly the .formscoding
and .modulescoding have no conceivable relevance.

To answer your question, you need a LEFT JOIN instead of an INNER JOIN:

SELECT tblCustomer.*, tblOrders.*
FROM tblCustomer LEFT JOIN tblOrders
ON tblCustomer.ID = tblOrders.CustomerID;

That will include all records from tblCustomer, and only the matching
records from tblOrders.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[adsl]

thanks million for your sollution and sorry for using multiple newsgroups

 

[adsl]

 

[adsl]

 

[adsl]

 

[adsl]

 

[adsl]

 

[adsl]

 

[adsl]

thanks million for your sollution and sorry for using multiple newsgroups

 

[adsl]

Note: since this is plainly a question about a query, you should have
posted it to the .queries newsgroup alone. Certainly the .formscoding
and .modulescoding have no conceivable relevance.

To answer your question, you need a LEFT JOIN instead of an INNER JOIN:

SELECT tblCustomer.*, tblOrders.*
FROM tblCustomer LEFT JOIN tblOrders
ON tblCustomer.ID = tblOrders.CustomerID;

That will include all records from tblCustomer, and only the matching
records from tblOrders.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)