2 tables relationship_ suddenly records excluded in form

jakobt

Active Member
Joined
May 31, 2010
Messages
322
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?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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... :unsure:
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,826
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
May 31, 2010
Messages
322
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,826
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
May 31, 2010
Messages
322
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

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Why at all do we need to create table relationships, when it seems SQL for the relationship is also added.

For data 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:

 

Watch MrExcel Video

Forum statistics

Threads
1,129,260
Messages
5,635,131
Members
416,842
Latest member
Ateen4ever

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top