Help with simple query

[Aaron Weintraub]

Hi,

I have a products table linked to a shipments table which tracks the dates
of the shipments of each product. The tables are linked by ProductID. How
can I create a query which gives me a list of all products that have NOT had
a shipment in the last two weeks?

Any help is really appreciated.

-A

 

[Guest]

Set the date requirements in your query to search for
shipment dates in the shipment table for the past two
weeks, then look for productID = IsNull in your shipment
table.

You can use the "find unmatched query wizard" to help.

 

[John Spencer (MVP)]

Simplest way to understand is to use two queries.

First query, gets a list of all products that have shipped from the shipments
table. Something like

SELECT Distinct [shipments].[productid]
FROM Shipments
WHERE Shipments.ShipDate > DateAdd("d",-14,Date())

Save that as the qShipped. Now create a new query that has Products table and
the qShipped query as sources. Set a relation between Products.ProductId and
qShipped.ProductID. Click on the relationship and say show All Products and
only qShipped. Add the fields from Products you want to see to the grid and add
qShipped.ProductId with the criteria IS NULL. SQL would look something like

SELECT Products.*
FROM Products LEFT JOIN qShipped
ON Products.ProductID = qShipped.ProductID
WHERE qShipped.ProductID Is Null

You can do all this in one query using the first query as a subquery, but
performance varies and it is more difficult to setup and understand.

 

[hcj]

Try this:
First, create a query which extracts only shipping
records from the last two weeks. The criterion for ship
date would be: >=Date()-14. This assumes the computer
clock is correct at run time, of course.
Then, build a second query that joins the products table
to the first query. Make the join such that all product
records are selected and only those shipping date records
that match (via ProductID) are selected. Set the
criterion for shipping date as: Is Null
This query should select all product records that have
no matching shipping date record in the subset created by
the first query. This will include all products that
haven't been shipped in the last two weeks as well as
those which have never shipped.

You can generalize the first query by prompting for a
range of dates; thus you could easily test for products
that didn't ship within any selected time period.
Let me know if this works for you, or whether I need to
expand the explanation a bit.

hcj

 

[John Vinson]

Hi,

I have a products table linked to a shipments table which tracks the dates
of the shipments of each product. The tables are linked by ProductID. How
can I create a query which gives me a list of all products that have NOT had
a shipment in the last two weeks?

Any help is really appreciated.

-A

The somewhat obscure (and rather inefficient, unfortunately) Not In
clause would be useful here:

SELECT <whatever>
FROM Products
WHERE ProductID NOT IN
(SELECT ProductID From Shipments
WHERE Shipment.ShipDate > DateAdd("ww", -2, Date()))

 

[Aaron Weintraub]

Thanks to everyone who helped out with this. The unmatched query worked out
perfectly.

-A