MS Access: how to make a complex query
Posted on 2009-07-15
This is a structure of my table:
return date name
0.9 1/1/2005 assetA
0.8 2/1/2005 assetA
0.4 3/1/2005 assetA
0.66 4/1/2005 assetA
0.76 6/1/2007 assetB
0.26 7/1/2007 assetB
0.87 8/1/2007 assetB
The column [date] presents consecutive sets of dates for each asset. I need to make the query which selects all records with returns later than a certain date EXCEPT these that don't have returns for the last three months (deselecting non-reporting fund managers for the last 3 months).
So, the query:
1) selects all records with returns later than 4/1/2005. This is a simple thing
SELECT * FROM mytable WHERE [date] > #4/1/2005#
2) also should remove records that don't exist after certain date (3 months before the current date). I can filter these records by
SELECT * FROM mytable WHERE [date] > DateAdd("m", -4, Date)
but this deselects all records before that date.
How can I do both filters in one query?