dilemma tracking testing

[mary]

Here is my dilemma:

I need to track if testing has been done on about 15,000 compounds. This needs to be done on a quarterly or annual basis depending on the item.

Here is a simplified table structure (with longer field names for ease of understanding!)

tblItem
idItem
annualOrQuarterlyTest
spikeGroup

tblResults
spikegroup
idItem
date
data

The issue is, I want to be able to create a query to determine all items that do not have results for a particular quarter.

I know I'm missing a pice of the puzzle, but i can't put my finger on how to fix it. I've tried searching, but I'm not even sure what to call the "thing" I'm looking for!

Thanks,
Mary

 

[dcb]

2007 or 2003?

 

[dcb]

One way I can think of is :

Select idItem from tblItem
where
annualorquartarlytest = "A/Q"
AND
idItem NOT IN
(Select idItem from tblResults
where date > #startqdate# and date < #endqdate#);

However the IN clause is only supported in 2007

 

[mary]

Thanks for the suggestion. I only have 200 at work

I appreciate the suggestion!

Mary