Query not showing certain records
in [Queries]
|
From: Charlie on 14 Mar 2010 12:17 HEEELLLLP I have a query which is used to feed to certain reports. It gathers information from 4 related tables and has a criteria under the order number of [Forms]![Order]![OrderNumber] this then produces the record for that specific number. However, I have appended some new data in (Database still being developed - keeping the data up to date) and certain records are just not showing up although all the necessary information is there. The SQL for the query is: 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) INNER 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])); Many thanks in advance Charlotte
From: Ken Snell on 14 Mar 2010 14:31 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/ "Charlie" <Charlie(a)discussions.microsoft.com> wrote in message news:57019CF9-A29A-4D65-835B-BD24A54C453B(a)microsoft.com... > HEEELLLLP > > I have a query which is used to feed to certain reports. It gathers > information from 4 related tables and has a criteria under the order > number > of [Forms]![Order]![OrderNumber] this then produces the record for that > specific number. However, I have appended some new data in (Database > still > being developed - keeping the data up to date) and certain records are > just > not showing up although all the necessary information is there. > > The SQL for the query is: > 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) INNER 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])); > > Many thanks in advance > > Charlotte >
From: Charlie on 14 Mar 2010 15:51 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/ > > > "Charlie" <Charlie(a)discussions.microsoft.com> wrote in message > news:57019CF9-A29A-4D65-835B-BD24A54C453B(a)microsoft.com... > > HEEELLLLP > > > > I have a query which is used to feed to certain reports. It gathers > > information from 4 related tables and has a criteria under the order > > number > > of [Forms]![Order]![OrderNumber] this then produces the record for that > > specific number. However, I have appended some new data in (Database > > still > > being developed - keeping the data up to date) and certain records are > > just > > not showing up although all the necessary information is there. > > > > The SQL for the query is: > > 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) INNER 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])); > > > > Many thanks in advance > > > > Charlotte > > > > > . >
From: Ken Snell on 14 Mar 2010 23:12 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/ >> >> >> "Charlie" <Charlie(a)discussions.microsoft.com> wrote in message >> news:57019CF9-A29A-4D65-835B-BD24A54C453B(a)microsoft.com... >> > HEEELLLLP >> > >> > I have a query which is used to feed to certain reports. It gathers >> > information from 4 related tables and has a criteria under the order >> > number >> > of [Forms]![Order]![OrderNumber] this then produces the record for that >> > specific number. However, I have appended some new data in (Database >> > still >> > being developed - keeping the data up to date) and certain records are >> > just >> > not showing up although all the necessary information is there. >> > >> > The SQL for the query is: >> > 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) INNER 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])); >> > >> > Many thanks in advance >> > >> > Charlotte >> > >> >> >> . >>
From: Charlie on 15 Mar 2010 03:18
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 |