Subquery looking for missing information

[Frank]

The DB in question contains daily stock transactions keyed by ticker
(symbol) and date.

Unfortunately all dates do not exist for all ticker symbols.

To assist the process, there are two temporary tables: Tickers (the
collection of all tickers) AND TransactionDates (the collection of all
dates)

The latest failed attempt at finding the missing transaction dates by
ticker is

SELECT Dailys.Ticker, TransactionDates.Date
FROM Tickers INNER JOIN (TransactionDates INNER JOIN Dailys ON
TransactionDates.Date = Dailys.Date) ON Tickers.Ticker = Dailys.Ticker
WHERE (((TransactionDates.Date)=(SELECT TransactionDates.Date FROM
TransactionDates LEFT JOIN Dailys ON TransactionDates.Date =
Dailys.Date WHERE (((Dailys.Date) Is Null)))))
ORDER BY Dailys.Ticker, TransactionDates.Date;

This returns no hits when there are missing transaction dates (April
19th is missing in all cases, there are others).

Any insights into this issue will be appreciated.

[John Winterbottom]

"Frank" <to*****@fctvplus.net> wrote in message
news:f0**************************@posting.google.c om...

The DB in question contains daily stock transactions keyed by ticker
(symbol) and date.

Unfortunately all dates do not exist for all ticker symbols.

To assist the process, there are two temporary tables: Tickers (the
collection of all tickers) AND TransactionDates (the collection of all
dates)

The latest failed attempt at finding the missing transaction dates by
ticker is

SELECT Dailys.Ticker, TransactionDates.Date
FROM Tickers INNER JOIN (TransactionDates INNER JOIN Dailys ON
TransactionDates.Date = Dailys.Date) ON Tickers.Ticker = Dailys.Ticker
WHERE (((TransactionDates.Date)=(SELECT TransactionDates.Date FROM
TransactionDates LEFT JOIN Dailys ON TransactionDates.Date =
Dailys.Date WHERE (((Dailys.Date) Is Null)))))
ORDER BY Dailys.Ticker, TransactionDates.Date;

This returns no hits when there are missing transaction dates (April
19th is missing in all cases, there are others).

i think this will work

---------------------------------------
select t.Ticker, d.[Date]
from Tickers as t, TransactionDates as d
where not exists
(
select * from Dailys as y
where y.[Date] = d.[Date]
and y.Ticker = t.Ticker
)
order by t.Ticker, d.[Date]
--------------------------------------

I don't know what performace will be like - in sql server this would
probably be fine but in access your mileage may vary.

BTW, I wouldn't use "Date" for a column name - it's a reserved word.

[Frank]

"John Winterbottom" <as******@hotmail.com> wrote in message news:<2g************@uni-berlin.de>...

"Frank" <to*****@fctvplus.net> wrote in message
news:f0**************************@posting.google.c om...

The DB in question contains daily stock transactions keyed by ticker
(symbol) and date.

Unfortunately all dates do not exist for all ticker symbols.

To assist the process, there are two temporary tables: Tickers (the
collection of all tickers) AND TransactionDates (the collection of all
dates)

The latest failed attempt at finding the missing transaction dates by
ticker is

SELECT Dailys.Ticker, TransactionDates.Date
FROM Tickers INNER JOIN (TransactionDates INNER JOIN Dailys ON
TransactionDates.Date = Dailys.Date) ON Tickers.Ticker = Dailys.Ticker
WHERE (((TransactionDates.Date)=(SELECT TransactionDates.Date FROM
TransactionDates LEFT JOIN Dailys ON TransactionDates.Date =
Dailys.Date WHERE (((Dailys.Date) Is Null)))))
ORDER BY Dailys.Ticker, TransactionDates.Date;

This returns no hits when there are missing transaction dates (April
19th is missing in all cases, there are others).

i think this will work

---------------------------------------
select t.Ticker, d.[Date]
from Tickers as t, TransactionDates as d
where not exists
(
select * from Dailys as y
where y.[Date] = d.[Date]
and y.Ticker = t.Ticker
)
order by t.Ticker, d.[Date]
--------------------------------------

I don't know what performace will be like - in sql server this would
probably be fine but in access your mileage may vary.

BTW, I wouldn't use "Date" for a column name - it's a reserved word.

Thanks a lot -- It works but slowly. The Daily table is now over
110,000 rows. As it is a maintenance function and not directly tied to
User response, I'll live with it for a while. I suspect I'll move the
whole thing to MySQL. The ultimate User is a university.

Thanks for the heads up on the Key Word. Using Access as a prototype
tool does lead to sloppy habits.

Again thanks.