R
Richard Horne
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;
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;