Windows 7 64bit 
Access 2010 64bit
I'm trying to create a report for how many "nasty grams" (rejection notices) my company has sent to people who keep sending in paper forms when they are supposed to file electronically.
Every letter that goes out has information recorded based on whatever they sent to us - so the only remotely reliable way to count how many each person received is by the address on the envelope (people use different names, different business names, use different telephone numbers on the forms, etc).
I just built several queries that feed into a report that gets sent to my boss on a monthly basis to show the people who've sent in more than one paper form and have received our rejection notices more than once.
I'm not the greatest at SQL, but I've been trying to find a way to use DISTINCT Addresses, leave all other fields the same (not DISTINCT), to:
1. Only return people who have received 2 or more letters
2. If at least one of the letters was sent more than 90 days ago
AND If at least one of the letters was within the last 90 days
-If at least one was within the last 90 days, only display the most recent send date of the letter (lots of people get back-to-back letters).
3. Display their names, addresses, telephone numbers, the date of the most recent letter sent, count of the total letters ever sent to that person. (the report will already do this, just need help with a Max date)
This *should* be simple and straight forward, but it is apparently not.
My first query counts the number of times each address appears in the main table and simply only has [Address] and [CountofAddress]
My second query has the [Name], the [LetterDated] >=Now()-90, and the qryCountofAddress is linked to the main table by [Address], using [CountofAddress] >=2
P.S. Thank you in advance. Yes, I have tried Selecting Unique Values in the Properties tab. Yes, I have tried INNER JOIN (but can't get the rest of my fields to display once I make addresses distinct).
Reply With Quote
