Multiple dates

[Guest]

This query will show me the symbols that have 2 symbols on the data table but
I would like to see the symbols that have 2 entrys only on the 16th and 15th.
so if abc is in the data table twice and if the two times have a date of the
16th and 15th then show them to me.
How do I do that?
thanks,
RogueIT
SELECT CountOfSymbols.Symbol, CountOfSymbols.CountOfSymbol, Data.[Rel Str],
Data.Last, Data.change
FROM Data INNER JOIN CountOfSymbols ON Data.Symbol = CountOfSymbols.Symbol
WHERE (((CountOfSymbols.CountOfSymbol)=2) AND ((Data.Date)=#6/16/2005#));

 

[John Vinson]

This query will show me the symbols that have 2 symbols on the data table but
I would like to see the symbols that have 2 entrys only on the 16th and 15th.
so if abc is in the data table twice and if the two times have a date of the
16th and 15th then show them to me.
How do I do that?
thanks,
RogueIT
SELECT CountOfSymbols.Symbol, CountOfSymbols.CountOfSymbol, Data.[Rel Str],
Data.Last, Data.change
FROM Data INNER JOIN CountOfSymbols ON Data.Symbol = CountOfSymbols.Symbol
WHERE (((CountOfSymbols.CountOfSymbol)=2) AND ((Data.Date)=#6/16/2005#));

You'll probably need a couple of subqueries with an EXISTS clause:

SELECT CountOfSymbols.Symbol, CountOfSymbols.CountOfSymbol, Data.[Rel
Str], Data.Last, Data.change
FROM Data INNER JOIN CountOfSymbols
ON Data.Symbol = CountOfSymbols.Symbol
WHERE EXISTS
(SELECT Symbol FROM CountOfSymbols AS X
WHERE X.[Symbol] = CountOfSymbols.Symbol
AND [Date] = #6/15/2005#)
AND EXISTS
(SELECT Symbol FROM CountOfSymbols AS X
WHERE X.[Symbol] = CountOfSymbols.Symbol
AND [Date] = #6/16/2005#);


John W. Vinson[MVP]

 

[Bill]

Hello:

Try adding an "OR" to your Where clause. Like this.

SELECT CountOfSymbols.Symbol, CountOfSymbols.CountOfSymbol, Data.[Rel Str],
Data.Last, Data.change
FROM Data INNER JOIN CountOfSymbols ON Data.Symbol = CountOfSymbols.Symbol
WHERE CountOfSymbols.CountOfSymbol = 2 AND (Data.Date = #6/16/2005# OR
Data.Date = #6/15/2005#)

Bill