Opposite of a Join ?

[Carl Clarke]

I have two tables, a stock table and a stock transactions table that has a
record for each stock movement. The common key is the unique StockID and the
stock transactions are dated.

I would like a query that lists the StockID's and the stock description
(from stock table) that have NOT had transactions recorded between two
dates.e.g These stock codes have no recorded transactions between these two
dates. I have tried various ways of doing this, including using joins or
subqueries, but without sucess. I would be VERY grateful for a pointer in
the right direction. This is such a straightforward requirement there must
be an easy way - isn't there ?

Many thanks

Carl Clarke

 

[John Verhagen]

Assume the stock table is called tblStocks and the transactions are stored
in tblStockTransaction, then try the query:

SELECT tblStocks.StockID, tblStocks.Description
FROM tblStocks
WHERE (((tblStocks.StockID)<>All (SELECT tblStockTransaction.StockID FROM
tblStockTransaction WHERE (((tblStockTransaction.TransactionDate) Between
#3/4/2003# And #3/6/2003#)))));

 

[LeAnne]

Try

SELECT StockID, StockDescription
FROM tblStocks INNER JOIN tblTransactions ON
tblTransactions.StockID=tblStocks.StockID
WHERE (((tblTransactions.TransactionDate) Not Between #<date1># And
#<date2>#));

hth,

LeAnne