Unmatched Records

[Mario]

Have 2 queries, one is "CustomerNoSales" and the other "CustomerSales", both
queries have these 2 fields "CID" (customerID) and "PrdtID" (productID).

I need to find out which records from the "PrdtID" field in the
"CustomerSales" query that ARE NOT in the "CustomerNoSales" query.

 

[vanderghast]

Have you tried the Query Wizard?

Vanderghast, Access MVP

 

[Mario]

I did. Did not work.

Have you tried the Query Wizard?

Vanderghast, Access MVP



.

 

[vanderghast]

Have you some data sample, with records "IN" and records "NOT IN" ?

Vanderghast, Access MVP

 

[Mario]

I printed both queries and checked each
and there is 35 more records in the "CustomersSales" than in the
"CustomerNoSales".

 

[vanderghast]

You can have dup, so the two numbers of records won't be the same, even if
the value will all be "in" the other table.

Example:

Table1.Names = "Mary", "John", "Joe"

Table2.Names = "Mary", "John", "Mary", "John", "Joe"


Everyone in table2.Names *is* in Table1.Names, but there is 5 values in the
second sequence, and only 3 in the first sequence.

Again, to repeat myself, have you some 'data sample' which would explain
what you say by "it does not work" , and what it should be if it was "to
work" ?




Vanderghast, Access MVP

 

[Mario]

I want to thank you for your time, I was looking at my initial request
for help and I failed to mention that the records I am looking for are from
only one customer. Here is the 2 queries:

“CustomerNoSales†query

SELECT DISTINCT OrderDetails.ProductName2, OrderDetails.PrdtID,
ShipAddress.CID, Last(ShipAddress.ShippedDate) AS LastOfShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
HAVING (((ShipAddress.CID)=[Forms]![OrderForm]![CID]) AND
((Last(ShipAddress.ShippedDate))<=Date()-14))
ORDER BY OrderDetails.PrdtID;


“CustomerSales†query

SELECT DISTINCT OrderDetails.ProductName2, OrderDetails.PrdtID,
ShipAddress.CID, Last(ShipAddress.ShippedDate) AS LastOfShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
HAVING (((ShipAddress.CID)=[Forms]![OrderForm]![CID]))
ORDER BY OrderDetails.PrdtID;

The way the queries choose the customer is by an open form named
"OrderForm" and subform named "OrderDetails"

What I need is to see what products (PrdtID) this customer has not purchased
the last 14 days (CustomerNoSales) that he had purchase before
(CustomerSales)

 

[vanderghast]

I think I may see what can be the problem.

Starts with the query wizard about finding records in CustomerNoSales NOT
matching a record in CustomerSales, by asking a match about ProductName2.
Before running it, though, edit it in graphical view, and add an additional
join line over the common field ShipAddess.CID. Edit that additional
join-line so that it also points toward CustomerSales (as does the join-line
for ProductName2). That should do the trick about finding

(CustomerNoSales.ProductName2 AND CustomerNoSales.CID)
NOT IN
(CustomerSales.ProductName2 AND CustomerSales.CID)


The double outer join is required, in this case, since TWO fields are
implied, the product name and the address (client),while the query wizard
assumes only one field is required.



Vanderghast, Access MVP

I want to thank you for your time, I was looking at my initial request
for help and I failed to mention that the records I am looking for are
from
only one customer. Here is the 2 queries:

“CustomerNoSales†query

SELECT DISTINCT OrderDetails.ProductName2, OrderDetails.PrdtID,
ShipAddress.CID, Last(ShipAddress.ShippedDate) AS LastOfShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID =
OrderDetails.OID
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
HAVING (((ShipAddress.CID)=[Forms]![OrderForm]![CID]) AND
((Last(ShipAddress.ShippedDate))<=Date()-14))
ORDER BY OrderDetails.PrdtID;


“CustomerSales†query

SELECT DISTINCT OrderDetails.ProductName2, OrderDetails.PrdtID,
ShipAddress.CID, Last(ShipAddress.ShippedDate) AS LastOfShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID =
OrderDetails.OID
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
HAVING (((ShipAddress.CID)=[Forms]![OrderForm]![CID]))
ORDER BY OrderDetails.PrdtID;

The way the queries choose the customer is by an open form named
"OrderForm" and subform named "OrderDetails"

What I need is to see what products (PrdtID) this customer has not
purchased
the last 14 days (CustomerNoSales) that he had purchase before
(CustomerSales)

You can have dup, so the two numbers of records won't be the same, even
if
the value will all be "in" the other table.

Example:

Table1.Names = "Mary", "John", "Joe"

Table2.Names = "Mary", "John", "Mary", "John", "Joe"


Everyone in table2.Names *is* in Table1.Names, but there is 5 values in
the
second sequence, and only 3 in the first sequence.

Again, to repeat myself, have you some 'data sample' which would explain
what you say by "it does not work" , and what it should be if it was "to
work" ?




Vanderghast, Access MVP





.