Query not showing certain records
in [Queries]
|
From: Ken Snell on 15 Mar 2010 22:52 I cannot imagine how the query would have worked previously if the same data condition existed then as now. However, you can make the query operational if you use this SQL statement (using LEFT JOIN to the [Delivery Address] table): SELECT DISTINCTROW Customer.CustomerID, Customer.[Client Name], Customer.[Address 1], Customer.[Address 2], Customer.[Address 3], Customer.[Address 4], Customer.[Address 5], Order.OrderID, Order.CustomerID, Order.OrderNumber, Order.Salesman, Order.InputBy, Order.DateOrderTaken, Order.TimeOrderTaken, Order.OrderedBy, Order.CustomerContact, Order.Telephone, Order.Fax, Order.DeliveryDate, Order.JobDescription, Order.DeliveryTime, OrderDetail.OrderDetailID, OrderDetail.OrderID, OrderDetail.Product, OrderDetail.Qty, OrderDetail.UnitPrice, OrderDetail.Total, OrderDetail.VAT, OrderDetail.TotalIncl, Order.[Delivery Charge], Order.VATDel, Order.DelTotal, Order.CustomerPO, Order.Notes, [Delivery Address].DelAddress1, [Delivery Address].DelAddress2, [Delivery Address].DelAddress3, [Delivery Address].DelAddress4, [Delivery Address].DelAddress5, Order.DeliveryAddress, Order.[ConsignmentNo/POD], Customer_Contacts.Title, Customer_Contacts.First_Name, Customer_Contacts.Last_Name, Order.Rep, Order.EntryInputBy FROM Customer INNER JOIN (Customer_Contacts INNER JOIN (([Order] INNER JOIN OrderDetail ON Order.OrderNumber = OrderDetail.OrderID) LEFT JOIN [Delivery Address] ON Order.OrderID = [Delivery Address].OrderID) ON Customer_Contacts.ContactID = Order.ContactID) ON (Customer.CustomerID = Order.CustomerID) AND (Customer.CustomerID = Customer_Contacts.CustomerID) WHERE (((Order.OrderNumber)=[Forms]![Order]![OrderNumber])); -- Ken Snell http://www.accessmvp.com/KDSnell/ "Charlie" <Charlie(a)discussions.microsoft.com> wrote in message news:F7EC1687-6DDD-4BB2-99A8-316F5C3A2309(a)microsoft.com... > Hi Ken, > > I did a process of elimination and found that the delivery address was the > cause. My confusion is this. The database is a developed version of a > very > basic original. This particular query is part of the original programming > and the aspects that feed this particular table have not changed and > previously allowed the delivery details to be empty, although it would not > allow other aspects to be null. It is not so much of a problem, as there > should really be something in these fields. I am however curious as why > this > now occurs. > > Many thanks > > Charlotte > > "Ken Snell" wrote: > >> I'm not sure you answered my question? >> >> For your query to work, each record in Customer table must have at least >> one >> record in each of these tables: >> Customer_Contacts >> Order >> >> And each Customer_Contacts record must have at least one record in this >> table: >> Order >> >> And each Order record tied to a Customer record must have at least one >> record in each of these tables: >> OrderDetail >> [Delivery Address] >> >> >> Your forms may be working ok because of how you've set them up and >> because >> of which tables they're using. I can only draw conclusions / troubleshoot >> using the query that you posted. If you're missing any record in one of >> the >> children table, your query will not return the Customer record's data nor >> its children records' data. >> >> -- >> >> Ken Snell >> http://www.accessmvp.com/KDSnell/ >> >> >> >> >> "Charlie" <Charlie(a)discussions.microsoft.com> wrote in message >> news:19189023-BB0D-4125-A467-3E17CE5DDADA(a)microsoft.com... >> > Hi Ken, >> > >> > Thank you for coming back to me. All records have been appended in and >> > can >> > be viewed kn each of the relevant forms and can be back tracked through >> > the >> > tables. Some records are showing but not all. >> > >> > Charlotte >> > >> > "Ken Snell" wrote: >> > >> >> Your query will only show records when you children data in all the >> >> joined >> >> tables (this is because all tables are joined using INNER JOIN). If >> >> you >> >> do >> >> not have data in any of the children tables, then the query will not >> >> show >> >> that record. Likely, when you appended data, you didn't append data to >> >> all >> >> these tables? >> >> >> >> -- >> >> >> >> Ken Snell >> >> http://www.accessmvp.com/KDSnell/ >> >> >> >> > Snip |