Query using two different date values

[skr]

I have a query for investments. My ultimate goal is to have a report that
allows me to show the investments that are open during a month (i.e., If an
investment is purchased 1/1/07 and matures 3/31/07 or an investment is
purchased 1/1/07 and matures 1/31/07) I need to have them both show up as
January, one in February and one in March. At the same time the interest
earned on all investments is a year-to-date total. I have tried to merge two
different query's but that doesn't seem to work. I'm pretty new to unique
problems so I'm hoping that somebody can help me out.

Tha nks,
Sue

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...ceb59db285&dg=microsoft.public.access.queries

 

[Van T. Dinh]

Consider the Investment period in Event A and the period you want to report
on (in this case 01/01/07 to 01/31/07) as Event B then what you want are the
overlapping Events. This happens when:

A starts before B ends *AND*
B starts before A ends

You can use the above as the citeria for your selection, e.g.:

.... WHERE ([InvStart] >= #01/31/2007#)
AND (#01/01/2007# <= [InvEnd)

 

[skr]

I really goofed up in explaining my problem! I have a table with the
investment, the interest on the investment and the fund number to which the
investement is connected. There will be multiple investments per fund. For
example fund 10 might have several different investments (100.00 + 250.00 +
500.00). I want to get one total for the investments within the one fund
number within a specific date range. For example for the month of January
the investments for Fund Number 10 would be $850. I can get the group by
method to work but then when I throw in a parameter query it eliminates the
group by method and goes from a total of $850 for fund #10 to 3 sepearate
entries for fund #10. I would guess there is some way to do this but I CAN'T
figure it out!

Thanks,
Sue

 

[John Spencer]

Change GROUP BY to WHERE for the date field



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[skr]

I finally used the right search words and got my answer. Thanks Duane!