Limit Query based on excluding data from other table
Windows XP 
Access 2007
What I am trying to do is not easy to explain, but below I gave it a shot. I am an average Access user and am trying to limit a query and have run into some problems. The quick version is I want a query that has three tables, pulls data from the first two. Then the third table contains a Y/N field. I want to limit the query further to all data on first two tables that are not a Y on the third table. Since the third table only hosts a fraction of the records in the first two tables, when I add this criteria, all the desired results are not returned as it limits it only to records that are in table three. Below is a more detailed specific example of exactly what I am doing with an attached database. Sorry for the long post, but wanted to be sure I provide enough info to answer the question. I greatly appreciate any help.
More Detail of attached:
The Trigger Test form is based off the query Trigger Form Query which is based off the Stored Financials tbl and Stored Overview tbl. I need two types of forms, one for financials straight up (Trigger Test form) and one where we annualize the data (Trigger Test Annualized). As the Test Trigger from stands, it shows all records in the Stored Overview tbl and Stored Financial tbl (this is a good thing).
On the Trigger Form, I have a button that opens the form Abstract Form that is related to the current record on the Trigger form (if the Abstract tbl does not have a related record, it opens a blank form which we enter data and then the record is added to the Abstract tbl). In the form, I have two ways to denote if it requires the financials to be Annualized (not sure the best way to store this, so I currently am showing both my ideas, open to new ones).
What I need is when opening the Trigger Test form for the first time, it should have all records. Then as we go through each record, we will open the Abstract form and fill it out, one of the fields denoting if it is needs to be annualized or not. After this is completed and I close the form, next time I open it, it should show all records (whether Abstracted or not) that do not require to be annualized. The Trigger Test Annualized form should have all record requiring to be annualized.
The problem I run into, is that I can add the Abstract tbl to the Trigger Form Query and give results that are false for the field annualized, but then when I open my Trigger Form, it only returns records that are in all three tbl. In the provided example, record with Property ID 60000 is eliminated from the Trigger test since it has not be abstracted and thus not in the Abstract tbl. This causes a problem due to the goal is to have our employees only have to open the Trigger Test to complete the work the first time. If the Trigger Test from does not contain all records, the record will not get abstracted.
This is a lot and I could really use some help. Thank you for your time in advance.
Reply With Quote
