Unmatched records

M

Mario

Have 2 queries the names are "CustomersNoSales" and the other
"CustomerSales", both have a field named "CID"(CustomerID) and another field
named "PrdtID"(ProductID).

I want to find out which "PrdtID" records are in the "CustomerSales" query
that ARE NOT in the "CustomerNoSales" query.

Thank You.
 
B

Bob Barrows

Mario said:
Have 2 queries the names are "CustomersNoSales" and the other
"CustomerSales", both have a field named "CID"(CustomerID) and
another field named "PrdtID"(ProductID).
Click to expand...

I guess it seemed like a good idea when you designed it, but this really is
not a good database design. Personally, I would simply have a customer
master table that listed all the customers in your database, another to list
all the products, and a CustomerSales table. The CustomersNoSales table is
completely unnecessary and will likely lead to a very hard-to-diagnose bug
in your application someday when you forget to remove a record from it when
you're supposed to .
I want to find out which "PrdtID" records are in the "CustomerSales"
query that ARE NOT in the "CustomerNoSales" query.
Click to expand...
Use the "find unmatched query wizard" available as one of the choices when
you clik the New button on the Queries tab.
 
J

John W. Vinson

Have 2 queries the names are "CustomersNoSales" and the other
"CustomerSales", both have a field named "CID"(CustomerID) and another field
named "PrdtID"(ProductID).

I want to find out which "PrdtID" records are in the "CustomerSales" query
that ARE NOT in the "CustomerNoSales" query.

Thank You.
Click to expand...

Create a new Query.

Use the "Unmatched Query" wizard.

Or, copy and paste this SQL into the SQL window of a new query:

SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
WHERE CustomerNoSales.PrdtID IS NULL;
 
M

Mario

Did not worked. I shows no records, I printed both queries and checked each
and there is 35 more records in the "CustomersSales" than in the
"CustomerNoSales".
 
J

John Spencer

You need to join on both CID and PrdtID if you are trying to get records from
CustomerSales where the combination of Customer and product does not exist.

SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.CID = CustomerNoSales.CID
WHERE CustomerNoSales.PrdtID IS NULL;
--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
M

Mario

Did not worked. It shows no records, the "CID" (CustomerID) would be on both
queries, the records I am looking for are the "PrdtID" (ProductID) that
shows in the "CustomerSales" query and that do not show in the the
"CustomerNoSales" query.
 
J

John Spencer

Perhaps I am misunderstanding what you are attempting to do.

If there are 35 more records in CustomerSales than in CustomerNoSales that
does not mean that there are no matches. One record in CustomerNoSales could
match anywhere from zero to thousands of records in CustomerSales.

What I've posted would show any records in CustomerSales that had no matching
prdtID and CID in customerNoSales.

If you run the following query:

SELECT CustomerSales.*, CustomerNoSales.PrdtID, CustomerNoSales.CID
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.CID = CustomerNoSales.CID

Do you see any records that where CustomerNoSales.PrdtID is blank?
--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
M

Mario

John 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)
 
J

John Spencer

OK.

Rewrite of your query to handle a problem.
== DISTINCT and GROUP BY have the same effect on returned records, so you
don't need DISTINCT in this query.
== Last returns the value of the field in the LAST record accessed by the
query for the group. You want MAX to get the latest date.
== It is more efficient to use a WHERE clause to limit the records returned
before they are aggregated. Use HAVING clause when you want to filter against
the aggregated result.

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

SELECT OrderDetails.ProductName2
, OrderDetails.PrdtID
, ShipAddress.CID
, Max(ShipAddress.ShippedDate) AS LastShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
WHERE (((ShipAddress.CID)=[Forms]![OrderForm]![CID]))
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
ORDER BY OrderDetails.PrdtID;

Since you are restricting both queries to the same CID you don't need that at
in the join, but I think you do need the LastShipDate as part of the join.

SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John 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:
Click to expand...
S N I P
 
M

Mario

I did what you suggested and when I run the last query is asking for a
"Parameter Value" for "CustomerNoSales.PrdtID'


John Spencer said:
OK.

Rewrite of your query to handle a problem.
== DISTINCT and GROUP BY have the same effect on returned records, so you
don't need DISTINCT in this query.
== Last returns the value of the field in the LAST record accessed by the
query for the group. You want MAX to get the latest date.
== It is more efficient to use a WHERE clause to limit the records returned
before they are aggregated. Use HAVING clause when you want to filter against
the aggregated result.

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

SELECT OrderDetails.ProductName2
, OrderDetails.PrdtID
, ShipAddress.CID
, Max(ShipAddress.ShippedDate) AS LastShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
WHERE (((ShipAddress.CID)=[Forms]![OrderForm]![CID]))
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
ORDER BY OrderDetails.PrdtID;

Since you are restricting both queries to the same CID you don't need that at
in the join, but I think you do need the LastShipDate as part of the join.

SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John 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:
Click to expand...
S N I P
.
Click to expand...
 
J

John Spencer

If that is the case, then there must be a typing error in one of the queries.
Did you copy and paste from my post? If so, the newsgroup software may have
introduced an extraneous (invisible) character.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I did what you suggested and when I run the last query is asking for a
"Parameter Value" for "CustomerNoSales.PrdtID'


John Spencer said:
OK.

Rewrite of your query to handle a problem.
== DISTINCT and GROUP BY have the same effect on returned records, so you
don't need DISTINCT in this query.
== Last returns the value of the field in the LAST record accessed by the
query for the group. You want MAX to get the latest date.
== It is more efficient to use a WHERE clause to limit the records returned
before they are aggregated. Use HAVING clause when you want to filter against
the aggregated result.

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

SELECT OrderDetails.ProductName2
, OrderDetails.PrdtID
, ShipAddress.CID
, Max(ShipAddress.ShippedDate) AS LastShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
WHERE (((ShipAddress.CID)=[Forms]![OrderForm]![CID]))
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
ORDER BY OrderDetails.PrdtID;

Since you are restricting both queries to the same CID you don't need that at
in the join, but I think you do need the LastShipDate as part of the join.

SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John 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:
Click to expand...
S N I P
.
Click to expand...
Click to expand...
 
M

Mario

Hello John,

Sorry it took so long to write this but I was out sick.

There was an extra "s" in theCustomerNoSales.PrdtID:
SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

when I run this query it shows no records. If I run each of the queries the
"CustomerSales" shows 133 records and the "CustomerNoSales"query shows 96
records.

Please advise.

Thank you.
John Spencer said:
If that is the case, then there must be a typing error in one of the queries.
Did you copy and paste from my post? If so, the newsgroup software may have
introduced an extraneous (invisible) character.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I did what you suggested and when I run the last query is asking for a
"Parameter Value" for "CustomerNoSales.PrdtID'


John Spencer said:
OK.

Rewrite of your query to handle a problem.
== DISTINCT and GROUP BY have the same effect on returned records, so you
don't need DISTINCT in this query.
== Last returns the value of the field in the LAST record accessed by the
query for the group. You want MAX to get the latest date.
== It is more efficient to use a WHERE clause to limit the records returned
before they are aggregated. Use HAVING clause when you want to filter against
the aggregated result.

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

SELECT OrderDetails.ProductName2
, OrderDetails.PrdtID
, ShipAddress.CID
, Max(ShipAddress.ShippedDate) AS LastShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
WHERE (((ShipAddress.CID)=[Forms]![OrderForm]![CID]))
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
ORDER BY OrderDetails.PrdtID;

Since you are restricting both queries to the same CID you don't need that at
in the join, but I think you do need the LastShipDate as part of the join.

SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Mario wrote:
John 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:
S N I P
.
Click to expand...
Click to expand...
.
Click to expand...
 
M

Mario

Hello John,

I was wondering if you are going to be able to help me with this problem

Thank you.


Mario said:
Hello John,

Sorry it took so long to write this but I was out sick.

There was an extra "s" in theCustomerNoSales.PrdtID:
SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

when I run this query it shows no records. If I run each of the queries the
"CustomerSales" shows 133 records and the "CustomerNoSales"query shows 96
records.

Please advise.

Thank you.
John Spencer said:
If that is the case, then there must be a typing error in one of the queries.
Did you copy and paste from my post? If so, the newsgroup software may have
introduced an extraneous (invisible) character.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I did what you suggested and when I run the last query is asking for a
"Parameter Value" for "CustomerNoSales.PrdtID'


:

OK.

Rewrite of your query to handle a problem.
== DISTINCT and GROUP BY have the same effect on returned records, so you
don't need DISTINCT in this query.
== Last returns the value of the field in the LAST record accessed by the
query for the group. You want MAX to get the latest date.
== It is more efficient to use a WHERE clause to limit the records returned
before they are aggregated. Use HAVING clause when you want to filter against
the aggregated result.

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

SELECT OrderDetails.ProductName2
, OrderDetails.PrdtID
, ShipAddress.CID
, Max(ShipAddress.ShippedDate) AS LastShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
WHERE (((ShipAddress.CID)=[Forms]![OrderForm]![CID]))
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
ORDER BY OrderDetails.PrdtID;

Since you are restricting both queries to the same CID you don't need that at
in the join, but I think you do need the LastShipDate as part of the join.

SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Mario wrote:
John 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:
S N I P
.
Click to expand...
.
Click to expand...
Click to expand...
 
J

John Spencer

It sounds as if there are no records in CustomerSales that don't have a
matching record in Customer Sales.

If you remove the where clause, do you get 133 records returned?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello John,

Sorry it took so long to write this but I was out sick.

There was an extra "s" in theCustomerNoSales.PrdtID:
SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

when I run this query it shows no records. If I run each of the queries the
"CustomerSales" shows 133 records and the "CustomerNoSales"query shows 96
records.

Please advise.

Thank you.
John Spencer said:
If that is the case, then there must be a typing error in one of the queries.
Did you copy and paste from my post? If so, the newsgroup software may have
introduced an extraneous (invisible) character.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I did what you suggested and when I run the last query is asking for a
"Parameter Value" for "CustomerNoSales.PrdtID'


:

OK.

Rewrite of your query to handle a problem.
== DISTINCT and GROUP BY have the same effect on returned records, so you
don't need DISTINCT in this query.
== Last returns the value of the field in the LAST record accessed by the
query for the group. You want MAX to get the latest date.
== It is more efficient to use a WHERE clause to limit the records returned
before they are aggregated. Use HAVING clause when you want to filter against
the aggregated result.

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

SELECT OrderDetails.ProductName2
, OrderDetails.PrdtID
, ShipAddress.CID
, Max(ShipAddress.ShippedDate) AS LastShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
WHERE (((ShipAddress.CID)=[Forms]![OrderForm]![CID]))
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
ORDER BY OrderDetails.PrdtID;

Since you are restricting both queries to the same CID you don't need that at
in the join, but I think you do need the LastShipDate as part of the join.

SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Mario wrote:
John 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:
S N I P
.
Click to expand...
.
Click to expand...
Click to expand...
 
M

Mario

It worked! It showed the 37 records I was looking for.

I do not know why it did not worked before,I compacted the database and it
worked.

Thank you very much for your help, I really appreciated it.

John Spencer said:
It sounds as if there are no records in CustomerSales that don't have a
matching record in Customer Sales.

If you remove the where clause, do you get 133 records returned?

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hello John,

Sorry it took so long to write this but I was out sick.

There was an extra "s" in theCustomerNoSales.PrdtID:
SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

when I run this query it shows no records. If I run each of the queries the
"CustomerSales" shows 133 records and the "CustomerNoSales"query shows 96
records.

Please advise.

Thank you.
John Spencer said:
If that is the case, then there must be a typing error in one of the queries.
Did you copy and paste from my post? If so, the newsgroup software may have
introduced an extraneous (invisible) character.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Mario wrote:
I did what you suggested and when I run the last query is asking for a
"Parameter Value" for "CustomerNoSales.PrdtID'


:

OK.

Rewrite of your query to handle a problem.
== DISTINCT and GROUP BY have the same effect on returned records, so you
don't need DISTINCT in this query.
== Last returns the value of the field in the LAST record accessed by the
query for the group. You want MAX to get the latest date.
== It is more efficient to use a WHERE clause to limit the records returned
before they are aggregated. Use HAVING clause when you want to filter against
the aggregated result.

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

SELECT OrderDetails.ProductName2
, OrderDetails.PrdtID
, ShipAddress.CID
, Max(ShipAddress.ShippedDate) AS LastShippedDate
FROM ShipAddress INNER JOIN OrderDetails ON ShipAddress.OID = OrderDetails.OID
WHERE (((ShipAddress.CID)=[Forms]![OrderForm]![CID]))
GROUP BY OrderDetails.ProductName2, OrderDetails.PrdtID, ShipAddress.CID
ORDER BY OrderDetails.PrdtID;

Since you are restricting both queries to the same CID you don't need that at
in the join, but I think you do need the LastShipDate as part of the join.

SELECT CustomerSales.*
FROM CustomerSales LEFT JOIN CustomerNoSales
ON CustomerSales.PrdtID = CustomerNoSales.PrdtID
AND CustomerSales.LastShippedDate = CustomerNoSales.LastShippedDate
WHERE CustomersNoSales.PrdtID is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Mario wrote:
John 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:
S N I P
.

.
Click to expand...
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unmatched Records 7
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Show only once 1
Split form 0
QBF & Query Malfunction 0
Copy previous record into form field 0
how to resolve a parameter conflict with unmatched records query? 7
Help! Unmatched Data Query 1

Top