Finding Records that are not there!

[TrudyD1474]

So here goes:

1. We are a tractor dealership. When the Salesmen contact a farm, we want to document that visit. We would like a report that tells the Salemen which farms they haven't visited in the last 60 days.
2. I have a database that has three types of records: Farm, Contact, Activity
3. I also have Farm_Contact for the many-to-many and Contact_Activity for the many-to-many
4. All this works great.
5. I created a 60_Day_Query that uses ALL the above mentioned records that creates an activity record with information from Farm, Contact and Activity (Farm_Activity). This query selects only those records that match on the Salesman and have a date not older that 60 days. This query works fine.
6. I created a query using the no matching record Wizard using Farm_Activity and the 60_Day_Query. This provided me with the Farm records that have had no activity in the past 60 days.
7. Now here is the problem. I want to show activity proir to the last 60 days, for those Farms_with_no_activity. So I duplicated the 60_Day_Query but eliminated the date check.
8. So then I created a query using the Farms_with_no_activity and the 60_Day_Query_No_Date. I joined them using Farm_Id where with the rule: Use all Farms_with_no_Activity and only the matching 60_Day_Query_NO_Date records. Here is the problem: I get the information from the Farms_with_no_activity, but do not get the information from the 60_Day_Query_No_Date. I have records in both queries that match, so I should have all the information.

So has anyone else had to find records with no match within a specific date timeframe?
Does anyone have any idea how to sovle my problem at item no. 8?
Trudy

[TrudyD1474]

I recreated the 60_Day_Query_No_Date without using Farm. I was able to pick up the Farm_Id from the Farm_Contact many-to-many record. then I paired it with Farms_With_no_Activity and got the results I was looking for.

The lesson is: If you are using two queries that are created using the same source for IDs then I guess Access gets confused.

[RuralGuy]

That's great Trudy. Are you ready to follow the link in my sig and mark this thread as Solved?