MS Access: how to make a complex query

Hi X-perts,

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

etc etc
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?

thanks
andy7789Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AaronroachCommented:
For ease i tend to do this sort of thing in the Access Query browser first and then just get the SQL afterwards (by changing mode to SQL)

What you would do i pull the date field in the field selection area, then in criteria put:

Between #4/1/2005# and DateAdd("m", -4, Date)

Although in your query above you have put > for both these points, i take it you actually want all records between 4/1/05 and Today - 4months.

If thats what you want then the above is what you need.
0
andy7789Author Commented:
I don't have any query browsers - all done via VBA from Excel. It has to be only a VBA querie
0
peter57rCommented:
Select * from mytable inner join
(Select distinct [Name] from mytable where [date] >DateAdd("m", -4, Date) ) as q1
on mytable.[name] =q1.[Name]
Where mytable.[date]>#4/1/2005#

You should not use Name or Date as fieldnames.  They are reserved words.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.