If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query not showing certain records
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 |
#2
|
|||
|
|||
Query not showing certain records
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" wrote in message ... 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 |
#3
|
|||
|
|||
Query not showing certain records
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" wrote in message ... 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 . |
#4
|
|||
|
|||
Query not showing certain records
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" wrote in message ... 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" wrote in message ... 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 . |
#5
|
|||
|
|||
Query not showing certain records
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" wrote in message ... 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 |
#6
|
|||
|
|||
Query not showing certain records
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" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|