Results 1 to 4 of 4
  1. #1
    VWSpeedyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Work in Center Valley, PA, live near Philadelphia, PA
    Posts
    25

    Creating an Unmatched Query with multiple linked criteria

    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));

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    How do you link the master data table with the related records in your Notice Tracking table? If you are using primary/foreign keys, your unmatched query would only need to use that relationship rather than trying to match on all of the other fields you mention. Alternatively, to handle those items that require annual notices, first create a totals query with max of date sent grouped by the foreign key field (most recent notice); you can add other criteria that would filter out records before a certain date (i.e. those sent out recently). Then create the unmatched query using your master data table and the totals query; this should give you the primary records that have not been sent out recently (and thus need to be sent).

  3. #3
    VWSpeedyB is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Work in Center Valley, PA, live near Philadelphia, PA
    Posts
    25
    I will attempt this. Thank you for the response.

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Please let us know if it works out for you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-26-2014, 03:02 PM
  2. Multiple Find Unmatched
    By dr4ke1990 in forum Queries
    Replies: 13
    Last Post: 11-08-2013, 10:41 AM
  3. Replies: 6
    Last Post: 09-14-2013, 05:59 PM
  4. Unmatched query with multiple criteria
    By brighteyes in forum Access
    Replies: 0
    Last Post: 10-26-2011, 06:37 AM
  5. Replies: 2
    Last Post: 05-09-2011, 06:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums