Query using two different date values

[Guest]

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)

 

[Jamie Collins]

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.

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)

A word of warning about representation of end dates:

I personally prefer the closed-closed representation, where the end
date value is included in the period e.g. the period 'January 2007'
would be (assumes the smallest time granule in Jet is one second;
subsecond values will be round which is OK):

[#2007-01-01 00:00:00# - #2007-01-31 23:59:59#]

The other popular representation is the closed-open representation,
where the end date value is not included in the period e.g. the period
'January 2007' would be (no assumption of smallest time granule):

[#2007-01-01 00:00:00# - #2007-02-01 00:00:00#)

When using the closed-open representation, change your operators

I'd warn against a representation with gaps in the DATETIME range e.g.
if the following represents contiguous periods:

#2007-01-01 00:00:00# - #2007-01-31 00:00:00#
#2007-02-01 00:00:00# - #2007-02-28 00:00:00#

then the value #2007-02-01 09:00:00# falls in the 'no man's land#
between the periods so you need to ensure every DATETIME value in the
entire model (table constraints, parameter values, etc) is 'rounded
down' to the prior midnight, which can become a real pain

Jamie.

--

 

[Guest]

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
'====================================================

 

[Guest]

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