max function on dates - please assist!

[yos...@yahoo.com]

If this message has already been posted, my apologies - I am not sure whether
it went through, although I'm told the post has been "spooled"... Objective:
I've been trying to build a query in MS Access that will perform a select on
all records in a table where date is greater than or equal to the latest date
in the table minus three months. Attempted Solutions: I made a separate
query that would just retrieve the latest date (called "maxdate"). I made
another query that would take the latest date from the first query and
subtract three months from it (called "lastdate"). I included the "lastdate"
query as a table in my select query and for the date field specified the
criteria as >= [lastdate]. I tried changing the select query into a make
table query. Problem: I continually get errors like: "you can't have an
aggregate function within a subquery" and "The SQL statement couldn't be
executed because it contains ambiguous outer joins. To force one of the
joins to be executed first, create a separate query that performs the first
join and then include that query in your SQL statement." - which I thought I
did by creating the "maxdate" and "lastdate" queries. Any input as to how
I could resolve this issue would be much appreciated. Much thanks, Shane

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[Sendcrap]

>rying to build a query in MS Access that will perform a
>select on
>all records in a table where date is greater than or equal to the latest
>date
>in the table minus three months.

You can do it all with one subquery. For the Date field's Criteria enter:
>=DateAdd("m",-3,(SELECT Max(yourdatefield) FROM yourtablename))

Hope this helps, let me know if questions.

[RQ...@student.fortwashakieschool.com]

woshane