2 tables relationship_ suddenly records excluded in form

[jakobt]

I have 2 tables. Customers and Orders
In total I have 10 customers and 100 orders
Not all orders have a record under customerID
I setup a One to many relationships with customerID.
Now I have a form where I can drill through each order. I would still like to be able to drill through each 100 orders. However it has excluded all orders with a blank customerID?

 

[JonXL]

That will only happen if you have something excluding records based on that field being NULL. That something could be a WHERE clause in the query used as the form's record source, a filter on the form, having the form on a subform based on the Customers table, etc.

You'll likely need to provide more information about your setup if you want more specific direction on how to get the other records to show up.

The bigger issue, though, is how you can even have orders without customers to begin with...

 

[xenou]

Inner Join can also cause "exclusion" since any customerid involved in an inner join will not be in the result set if the customerid is null. Left joins are tricky but that may be what you are looking for here...

 

[jakobt]

Thank you.
Not sure exactly what is the difference between an inner join and a left join. Could you maybe add the 2 different SQL examples.

 

[xenou]

well an example would be:

SELECT c.CustomerId, Count(OrderId) As TotalOrders
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID

SELECT c.CustomerId, Count(OrderId) As TotalOrders
FROM Customers c
LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID

The former query would return only customers with orders. The second query would return all customers.

Off the top of my head I would have liked to come up with a better example (this one would give a null for order count for the group of customers without any orders, when we would probably like to show a zero in reality). But anyway that's the drift. You will probably find hundreds of better examples if you google INNER JOIN Vs. LEFT JOIN.

Also I would note that until you are an expert (and even after that) treat left joins with suspicion as it is easy to fall into traps with them and get incorrect results without realizing it. Test test test!

 

[jakobt]

Thanks does this work regardless if I created a table relationship. One to Many. (One customers table Many orders table).

Why at all do we need to create table relationships, when it seems SQL for the relationship is also added.

 

[JonXL]

Why at all do we need to create table relationships, when it seems SQL for the relationship is also added.

For data integrity.

What is Referential Integrity?

By creating relationships and turning on the referential integrity (RI) settings, you make it impossible to end up with orphaned records (records existing with a foreign key that doesn't exist as a primary key in the other table) - in short, you avoid the situation you're in now where you have orders without customers or orders with customers that are no longer in the DB. Note one of the examples from the link:

It could also result in strange results appearing in reports (such as products without an associated company).

Even if you are also enforcing RI through forms, queries, and VBA, you should still set it on the table relationships unless you absolutely cannot (for example, you have your tables in different DBs due to size, etc.).

Plus, when you set your relationships, Access will automatically create your query joins on account of those relationships (joining by the fields indicated in your relationships).

Here's the MS article on table relationships:

Guide to table relationships - Microsoft Support

Link tables in Access desktop databases by adding joins and creating relationships.

support.office.com