A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query using two different date values



 
 
Thread Tools Display Modes
  #1  
Old February 22nd, 2007, 01:25 AM posted to microsoft.public.access.queries
skr
external usenet poster
 
Posts: 17
Default Query using two different date values

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/comm...access.queries
  #2  
Old February 22nd, 2007, 06:15 AM posted to microsoft.public.access.queries
Van T. Dinh
external usenet poster
 
Posts: 1,048
Default Query using two different date values

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)

--
HTH
Van T. Dinh
MVP (Access)



"skr" wrote in message
...
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/comm...access.queries



  #3  
Old February 22nd, 2007, 03:48 PM posted to microsoft.public.access.queries
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Query using two different date values

On Feb 22, 5:15 am, "Van T. Dinh"
wrote:
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.

--


  #4  
Old February 26th, 2007, 03:27 AM posted to microsoft.public.access.queries
skr
external usenet poster
 
Posts: 17
Default Query using two different date values

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
"skr" wrote:

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/comm...access.queries

  #5  
Old February 26th, 2007, 03:43 AM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 2,364
Default Query using two different date values

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 wrote:
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
"skr" wrote:

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/comm...access.queries

  #6  
Old February 26th, 2007, 03:51 AM posted to microsoft.public.access.queries
skr
external usenet poster
 
Posts: 17
Default Query using two different date values

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

"skr" wrote:

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
"skr" wrote:

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/comm...access.queries

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.