Date query!!

[rhofland]

Hi everyone! I have what SHOULD be a simple query, but I've never really worked with Access before, so I'm having problems troubleshooting my query. I have a spreadsheet that has bill #'s in column A, a Debtor's name in column B, a Date of appointment in Column C Balance in Column D, and a billed amount in column E.



I need to generate a report with the bill #'s, Debtor's name, and balance which eliminates data when the debtor has an appointment within the last 2 years. We can clear out those remaining claims as long as the debtor doesn't have any recent (2 years) appointments but does have bills after that date range. We can do this by exporting the remaining bills to a spreadsheet and as long as we have the debtor's name, balance and Bill #. The debtor can have many bill #'s, so I can't just simply eliminate duplicates, which is why I turned to Access to try to address this issue! There is only one table since I put it all on one spreadsheet. Below is a paste of the spreadsheet data to give a visual: See how Doe #1 has an appointment in 09 AND an appointment in 15? We do NOT want any of his bills exported to our query results; but the other ones who don't have a recent appointment, we DO want exported! Thanks in advance...I've been working on this for a week...and nobody at my work knows what the heck they are doing in Access

Bill No:	Debtor	Preprd Date	Balance	Billed Amt
Bill # 1	Doe,John1	30-Nov-09	30	35.58
Bill # 2	Doe,John2	1-Dec-09	30	35.58
Bill # 3	Doe,John1	16-Jul-15	30	35.58
Bill # 4	Doe,John4	3-Dec-09	30	35.58
Bill # 5	Doe,John5	4-Dec-09	30	35.58
Bill # 6	Doe,John6	5-Dec-09	30	35.58
Bill # 7	Doe,John7	6-Dec-09	30	35.58
Bill # 8	Doe,John8	7-Dec-09	30	35.58
Bill # 9	Doe,John9	8-Dec-09	30	35.58
Bill # 10	Doe,John10	9-Dec-09	30	35.58
Bill # 11	Doe,John11	10-Dec-09	30	35.58

[June7]

Do you want to retrieve records for debtors whose LAST appointment was over two years ago? This can get tricky.

1. build an aggregate query that pulls that latest date for each debtor - use Max() function

2. build another query that joins first query to the data table

3. set filter criteria on the Max date field

[CJ_London]

a number of ways to do this - depending on how much data, the first should be the fastest

SELECT myTable.* 
FROM myTable LEFT JOIN (SELECT Debtor, Max(PreDate) FROM myTable WHERE PreDate>dateadd("yyyy",-2,Date()) GROUP BY Debtor) AS Exclude ON myTable.Debtor=Exclude.Debtor
WHERE Exclude.Debtor is null

SELECT * 
FROM myTable
WHERE Not Exists(SELECT * FROM myTable WHERE PreDate>dateadd("yyyy",-2,Date()) AND Debtor=myTable.Debtor)

SELECT * 
FROM myTable
WHERE (SELECT count(*) FROM myTable WHERE PreDate>dateadd("yyyy",-2,Date()) AND Debtor=myTable.Debtor)=0

[rhofland]

I need to filter out any bill for a debtor with an appointment within the last 2 years. I'll try out these suggestions tomorrow when I get back at work.