Hello, once again Access Gurus!! You have all be extremely helpful in the past. So, here I am with another problem.
Back-ground: I have a table of Master Data, which requires either a 1 time Notice or an annual Notice dependent on certain variables which are intertwined. I have a second table of Notice Tracking to record when the Notice is sent out (I am required to maintain data for 5-7 years). I'm trying to create an 'UNMATCHED' Query to search for new additions to the master data, compared against a record of items previously reported. The 4 primary variables are ID#, Section #, Destination, and Date.
ID# = specific identifier requiring reporting
Section #= Variable indicating either 1 time or annual notice (within calendar year 1/1 - 12/31)
Destination = destination
Date = Date of occurance
The catch is I have 7 days from the date of occurence to submit the Annual Notice. I have a linked table from the outside source which will compare internal reference to Master Table to add any new occurrences. My next step is to compare any new occurences on the Master Table to the Notice Tracking for any occurrence which has not previously been Notified. Notifications are required for all ID and Destination combinations. The last piece of the puzzle, where I'm stuck, is how to extract those occurences which are new for the current year, and not yet notified; but did occur and were notified in previous years. I can't get the query to pull those records without pulling all previous occurrences from previous years.
Code:
SELECT [Master Data].[ID#], [Master Data].[Description], [Master Data].[Section #], [Master Data].[Date], [Master Data].[Destination], Notice Tracking.[ID#], Notice Tracking.[Destination Ctry], Notice Tracking.[EPA Notification]
FROM [Master Data] LEFT JOIN Notice Tracking ON ([Master Data].[Destination] = Notice Tracking.[Destination Ctry]) AND ([Master Data].[ID#] = Notice Tracking.[ID#])
WHERE ((([Master Data].[Section #])=4) AND (([Master Data].[Date])>=#1/1/2015#) AND ((Notice Tracking.[ID#]) Is Null)) OR ((([Master Data].[Section #])=4) AND (([Master Data].[Date])>=#1/1/2015#) AND ((Notice Tracking.[ID#]) Is Not Null) AND ((Notice Tracking.[Destination]) Is Null)) OR ((([Master Data].[Section #])=5) AND (([Master Data].[Date])>=#1/1/2015#) AND ((Notice Tracking.[ID#]) Is Null)) OR ((([Master Data].[Section #])=5) AND (([Master Data].[Date])>=#1/1/2015#) AND ((Notice Tracking.[ID#]) Is Not Null) AND ((Notice Tracking.[Destination]) Is Null)) OR ((([Master Data].[Section #])=6) AND (([Master Data].[Date])>=#1/1/2015#) AND ((Notice Tracking.[ID#]) Is Null)) OR ((([Master Data].[Section #])=6) AND (([Master Data].[Date])>=#1/1/2015#) AND ((Notice Tracking.[ID#]) Is Not Null) AND ((Notice Tracking.[Destination]) Is Null));