Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Access: how to make a complex query

Posted on 2009-07-15
3
Medium Priority
?
348 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:andy7789
3 Comments
 
LVL 5

Expert Comment

by:Aaronroach
ID: 24865274
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
 

Author Comment

by:andy7789
ID: 24865355
I don't have any query browsers - all done via VBA from Excel. It has to be only a VBA querie
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 24866863
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question