Hi ALL,

I have 2 tables: Clients (Client ID, name, addr, etc.) and Transactions (Client ID, date, cost, etc.). I need two queries. One to see which clients had any activity within a date period and the other to see which clients had no activity within a date period. The first is easy and works: A query joining the two tables with the Client ID as the common field and with a criteria of "between date1 and date2" for the date field.

The inactivity one is driving me crazy!!! I first set it up the same as above but used "not between date1 and date2" and I thought it worked. But then I realized that all it did was eliminate any transactions between those dates and didn't really give me clients with no transactions between those dates. Also, if that client had transactions outside of those dates they still showed up even though I didn't want them to. I need some way to show only clients that do not have any transactions with a date between the two dates even if they have transactions outside those dates. Any client with no transactions at all would also show up.

I know I can do it in a "brute force" way by loading all the clients into a temp table then then with VB code go through each client and see if a transaction exists with a date between the two dates and then delete that client from the temp table. That should work but I'm afraid that will take a long time to execute as the two tables can get rather large.

So I'm looking for a way to do this with a query, but so far I'm drawing a blank. Any help would be appreciated!!!

Thanks,
Gerry

You can try a left join of the clients with the transactions and set the criteria to show only clients that don't have transactions in the given date range.