My query only shows results where all fields are have data entered

[Guest]

Hi guys,
I have a query - see below - which only shows the results for when all the
fields have data in them.

I say all, I mean in particular DeliveryAdress CompanyNameToDisplay and
Addresslines1 to 5.

For some reason before I added those 6 fields to the query it worked fine,
but now it will only show records where the above 6 fields have data in them.
I don't understand why. I want to see all recocrds regardless of whether
those fields are set.

Most of the records in the table orders don't have a delivery address set.


SELECT Orders.OrderNumber AS Orders_OrderNumber, Orders.[Customer Name],
Orders.ContactName, Orders.[Order Date], Orders.[Ship Name], Orders.[Ship
Address], Orders.[Ship Address 2], Orders.[Ship Address 3], Orders.[Ship
Address 4], Orders.[Ship Address 5], Orders.[Customer's Order Number],
Orders.[Quotation Number], Orders.Complete, Orders.[Complete Date],
Orders.[Extra Information], Orders.Contact, Orders.Delivery_Terms,
Orders.Order_Entry_Complete, Orders.HIE, Orders.Order_Cancelled,
Orders.Ship_To, Order_Details.Order_Items_ID, Order_Details.OrderNumber AS
Order_Details_OrderNumber, Order_Details.[Item Number], Order_Details.[Item
Type], Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Currency, Order_Details.Price,
Order_Details.Denomination, Order_Details.NSN, Order_Details.Pattern,
Order_Details.[Delivery Note], Order_Details.Delivery_WC,
Order_Details.InvoiceNumber, Order_Details.ItemComplete,
Order_Details.Department, Order_Details.OrderCancelled, Company_Details.ID AS
Company_Details_ID, Company_Details.[Company Name], Company_Details.[Contact
Name], Company_Details.[Address Line 1], Company_Details.[Address Line 2],
Company_Details.[Address Line 3], Company_Details.[Address Line 4],
Company_Details.[Address Line 5], Delivery_Addresses.ID AS
Delivery_Addresses_ID, Delivery_Addresses.CompanyNameToDisplay,
Delivery_Addresses.AddressLine1, Delivery_Addresses.AddressLine2,
Delivery_Addresses.AddressLine3, Delivery_Addresses.AddressLine4,
Delivery_Addresses.AddressLine5
FROM ((Delivery_Addresses INNER JOIN Orders ON Delivery_Addresses.ID =
Orders.Ship_To) INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber) INNER JOIN Company_Details ON Orders.[Customer
Name] = Company_Details.ID;

 

[John Nurick]

Hi Richard,

One of your INNER JOINS should be an outer (I can never remember whether
left or right) join, so you get records in the other join that don't
have counterparts in the DelivereryAddresses table.

Hi guys,
I have a query - see below - which only shows the results for when all the
fields have data in them.

I say all, I mean in particular DeliveryAdress CompanyNameToDisplay and
Addresslines1 to 5.

For some reason before I added those 6 fields to the query it worked fine,
but now it will only show records where the above 6 fields have data in them.
I don't understand why. I want to see all recocrds regardless of whether
those fields are set.

Most of the records in the table orders don't have a delivery address set.


SELECT Orders.OrderNumber AS Orders_OrderNumber, Orders.[Customer Name],
Orders.ContactName, Orders.[Order Date], Orders.[Ship Name], Orders.[Ship
Address], Orders.[Ship Address 2], Orders.[Ship Address 3], Orders.[Ship
Address 4], Orders.[Ship Address 5], Orders.[Customer's Order Number],
Orders.[Quotation Number], Orders.Complete, Orders.[Complete Date],
Orders.[Extra Information], Orders.Contact, Orders.Delivery_Terms,
Orders.Order_Entry_Complete, Orders.HIE, Orders.Order_Cancelled,
Orders.Ship_To, Order_Details.Order_Items_ID, Order_Details.OrderNumber AS
Order_Details_OrderNumber, Order_Details.[Item Number], Order_Details.[Item
Type], Order_Details.Description, Order_Details.Type_Colour_Size,
Order_Details.Quantity, Order_Details.Currency, Order_Details.Price,
Order_Details.Denomination, Order_Details.NSN, Order_Details.Pattern,
Order_Details.[Delivery Note], Order_Details.Delivery_WC,
Order_Details.InvoiceNumber, Order_Details.ItemComplete,
Order_Details.Department, Order_Details.OrderCancelled, Company_Details.ID AS
Company_Details_ID, Company_Details.[Company Name], Company_Details.[Contact
Name], Company_Details.[Address Line 1], Company_Details.[Address Line 2],
Company_Details.[Address Line 3], Company_Details.[Address Line 4],
Company_Details.[Address Line 5], Delivery_Addresses.ID AS
Delivery_Addresses_ID, Delivery_Addresses.CompanyNameToDisplay,
Delivery_Addresses.AddressLine1, Delivery_Addresses.AddressLine2,
Delivery_Addresses.AddressLine3, Delivery_Addresses.AddressLine4,
Delivery_Addresses.AddressLine5
FROM ((Delivery_Addresses INNER JOIN Orders ON Delivery_Addresses.ID =
Orders.Ship_To) INNER JOIN Order_Details ON Orders.OrderNumber =
Order_Details.OrderNumber) INNER JOIN Company_Details ON Orders.[Customer
Name] = Company_Details.ID;