queries to find dates that exceed set amount of days

[SD5KP]

Hi



I am not sure if this has already been asked but I'm newbie to Microsoft access but I'm trying to figure out a way to make a query on a table to see when a date completed has exceeded 180 days from last completed. I have tried different formulas but not getting the results I want I have tried <=date()-180 and what I get is every date prior to the current date. I only want to know is when a date is older than 6 months or 180 days from todays date. I can do this in Excel I know but I'm trying to migrate and learn Microsoft access for future endeavors.

[June7]

What do you mean by "date completed has exceeded 180 days from last completed"? What is 'last completed'?

[SD5KP]

What I mean is say if i give class A a test on 05 September 14 and class B a test on 01 October 2014 and they have to take this test every 180 days
..I want to be able to run a query to show everyone who has completed that test over 180 days ago so that I know they have to retest. hopefully that explains it

[June7]

You need a query that returns the most recent test date for each person and evaluate if that date is past due.

One way to retrieve the most recent date is with an aggregate query.

SELECT PersonID, Max(TestDate) AS LastTest FROM tablename;

[SD5KP]

ok I have an idea of what that is suppose to get me in a result but im not sure how to go about that. Say im using excel and i enter a date into the cell and if that date is in past over 180 days the cell will turn yellow because it was 180 days ago when it was last completed. I can then have a macro or copy past the info I need into another workbook and make a list of who needs to retest. I want access to do the same thing but generate the roport for me based of a query. I dont need to see who has completed inbetween today and the last 180 days just who has exceeded the 180 day mark from last completing it. the formula i posted earlier is returning everyone that has completed the test inbetween yesterday and 180 days ago. I want my formula to only start looking at the 180 day mark. maybe that makes more sense or maybe we are talking about the same thing and im just not understanding it.