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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|