complement result from inner join

[jodero]

I have a large table with event information from many NT servers gathered on a daily bases. I created a reference table with 3 columns where I put events that I DO NOT wish to see in the query result. I use an inner join and request the results NOT IN the inner join. Apparently this is a very inefficient way to solve my problem because I get a time-out from my front-end (i.e. Access).
I worked around the problem by creating a temporary table where I select the records on a date criterium first.
I'd like to make this a stored procedure that I will run from VBA in Access (maybe later in ASP).
Any suggestions?

I use this SQL:
SELECT *
FROM NT-Events
WHERE (RecordNum NOT IN
(SELECT RecordNum
FROM [NT-Events] INNER JOIN
Filtertabel01 ON
[NT-Events].Source = Filtertabel01.Source AND
[NT-Events].TypeOfEvent = Filtertabel01.TypeOfEvent AND
[NT-Events].EventID = Filtertabel01.EventID))
AND (DateAndTime >= '2001-07-30 00:00:00')

 

[MichaelRed]

Look at the results of the Ms. Access UN-MATCHED query wizzard. Or use this wizzard to create the query for Un-Matched records to the table NT-EVENTS and add the dateandtime criteria manually after the un-matched query is built.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jodero]

Thank you for your quick response. I tried it immediately but found that this wizard allows for one corresponding column only. I modified my query accordingly to include the other 2 columns but this query returns no records.

I made this SQL:
SELECT [NT-Events].*
FROM [NT-Events] LEFT OUTER JOIN
Filtertabel01 ON
[NT-Events].Source = Filtertabel01.Source AND
[NT-Events].TypeOfEvent = Filtertabel01.TypeOfEvent AND
[NT-Events].EventID = Filtertabel01.EventID
WHERE (Filtertabel01.Source = NULL) AND
(Filtertabel01.TypeOfEvent = NULL) AND
(Filtertabel01.EventID = NULL)

More suggestions are very welcom.

 

[jodero]

Whoops.
It seems to work after all. Had some syntax problems though.
Last SQL statement should be:
WHERE (Filtertabel01.Source IS NULL)

Checking only one column is sufficient. The load on the SQL Server is dramaticaly lower.