Query not showing certain records

  • Thread starter Thread starter Charlie
  • Start date Start date
C

Charlie

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
 
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?
 
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 said:
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 said:
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
Click to expand...


.
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
Snip
 
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 said:
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 said:
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 said:
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

:

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?
Click to expand...
Click to expand...
Snip
Click to expand...
 
Back
Top