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
|
|||
|
|||
Last day of month in query
Hi all,
I have a query that gives me the dates and account balances like in the following example: Original query: Date Account balance 2009-01-03 500 2009-01-07 300 2009-01-31 1000 2009-02-15 800 2009-02-28 800 2009-03-30 800 2009-04-28 2000 2009-04-30 2000 .... I now would like to extract only the values for the last day of a month and put them in a new query. Keep in mind that the last day could be 31st, 30th, 28th (29th). In addition, I have multiple years of data. Also, I need the percentage change from one month to another. As a result, this second query should then looks like that: Date Account balance %-Change 2009-01-31 1000 0% 2009-02-28 800 -20% 2009-03-30 800 0% 2009-04-30 2000 +150% .... What do I have to do in order to come up with a query like this? Regards, Andreas |
#2
|
|||
|
|||
Last day of month in query
hi Andreas,
On 20.01.2010 20:55, Andreas wrote: What do I have to do in order to come up with a query like this? The last day in a month is always the first day of the next minus one day, e.g. DateSerial(2010, 3, 1) - 1 mfG -- stefan -- |
#3
|
|||
|
|||
Last day of month in query
Andreas
You might be able to use a little trick that works on the DateSerial() function. If you put in a Year and a Month, then use "0" for the day, DateSerial() returns the last day of the preceding month. So, for example, if you used: DateSerial(Year(Date()), Month(Date()),0) today, when Date() returns the 20th of January, the DateSerial() function above would return the 31st of December, 2009. Use that in a query and figure out how to specify the Year and Month, and I think you've got a way to select your dates! Good luck Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Andreas" wrote in message ... Hi all, I have a query that gives me the dates and account balances like in the following example: Original query: Date Account balance 2009-01-03 500 2009-01-07 300 2009-01-31 1000 2009-02-15 800 2009-02-28 800 2009-03-30 800 2009-04-28 2000 2009-04-30 2000 ... I now would like to extract only the values for the last day of a month and put them in a new query. Keep in mind that the last day could be 31st, 30th, 28th (29th). In addition, I have multiple years of data. Also, I need the percentage change from one month to another. As a result, this second query should then looks like that: Date Account balance %-Change 2009-01-31 1000 0% 2009-02-28 800 -20% 2009-03-30 800 0% 2009-04-30 2000 +150% ... What do I have to do in order to come up with a query like this? Regards, Andreas |
#4
|
|||
|
|||
Last day of month in query
On 20 jan, 20:55, Andreas wrote:
Hi all, I have a query that gives me the dates and account balances like in the following example: Original query: Date * * * * * * Account balance 2009-01-03 * *500 2009-01-07 * *300 2009-01-31 * *1000 2009-02-15 * * 800 2009-02-28 * * 800 2009-03-30 * * 800 2009-04-28 * * 2000 2009-04-30 * * 2000 ... I now would like to extract only the values for the last day of a month and put them in a new query. Keep in mind that the last day could be 31st, 30th, 28th (29th). In addition, I have multiple years of data. Also, I need the percentage change from one month to another. As a result, this second query should then looks like that: Date * * * * * * Account balance * * * * *%-Change 2009-01-31 * *1000 * * * * * * * * * * * * * 0% 2009-02-28 * * 800 * * * * * * * * * * * * * *-20% 2009-03-30 * * 800 * * * * * * * * * * * * * *0% 2009-04-30 * * 2000 * * * * * * * * * * * * *+150% ... What do I have to do in order to come up with a query like this? Regards, Andreas Selecting last of month records could look like: SELECT * FROM YourTable WHERE YourDate=DateAdd("d", -1, DateAdd("m", 1, DateSerial(Year (YourDate), Month(YourDate), 1))) Groeten, Peter http://access.xps350.com |
#5
|
|||
|
|||
Last day of month in query
On Wed, 20 Jan 2010 11:55:24 -0800 (PST), Andreas wrote:
Hi all, I have a query that gives me the dates and account balances like in the following example: Original query: Date Account balance 2009-01-03 500 2009-01-07 300 2009-01-31 1000 2009-02-15 800 2009-02-28 800 2009-03-30 800 2009-04-28 2000 2009-04-30 2000 ... I now would like to extract only the values for the last day of a month and put them in a new query. Keep in mind that the last day could be 31st, 30th, 28th (29th). In addition, I have multiple years of data. Also, I need the percentage change from one month to another. As a result, this second query should then looks like that: Date Account balance %-Change 2009-01-31 1000 0% 2009-02-28 800 -20% 2009-03-30 800 0% 2009-04-30 2000 +150% ... What do I have to do in order to come up with a query like this? Regards, Andreas As criteria on the Date Field write: =DateSerial(Year([DateField]),Month([DateField])+1,0) The above will return just records for the last day of the month of the [DateField]. By the way, there are 31 days in March, not 30 as you indicated above. ;-) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#6
|
|||
|
|||
Last day of month in query
Put the following in one of the fields for the query: DatePart("d",[dob]) and
the following in the criteria: 28 Or 29 Or 30 Or 31 -- Lynn Trapp MCP, MOS, MCAS "Andreas" wrote: Hi all, I have a query that gives me the dates and account balances like in the following example: Original query: Date Account balance 2009-01-03 500 2009-01-07 300 2009-01-31 1000 2009-02-15 800 2009-02-28 800 2009-03-30 800 2009-04-28 2000 2009-04-30 2000 .... I now would like to extract only the values for the last day of a month and put them in a new query. Keep in mind that the last day could be 31st, 30th, 28th (29th). In addition, I have multiple years of data. Also, I need the percentage change from one month to another. As a result, this second query should then looks like that: Date Account balance %-Change 2009-01-31 1000 0% 2009-02-28 800 -20% 2009-03-30 800 0% 2009-04-30 2000 +150% .... What do I have to do in order to come up with a query like this? Regards, Andreas . |
#7
|
|||
|
|||
Last day of month in query
What if the last day of month was a holiday or weekend and therefore no data
on that date? -- Build a little, test a little. "Andreas" wrote: Hi all, I have a query that gives me the dates and account balances like in the following example: Original query: Date Account balance 2009-01-03 500 2009-01-07 300 2009-01-31 1000 2009-02-15 800 2009-02-28 800 2009-03-30 800 2009-04-28 2000 2009-04-30 2000 .... I now would like to extract only the values for the last day of a month and put them in a new query. Keep in mind that the last day could be 31st, 30th, 28th (29th). In addition, I have multiple years of data. Also, I need the percentage change from one month to another. As a result, this second query should then looks like that: Date Account balance %-Change 2009-01-31 1000 0% 2009-02-28 800 -20% 2009-03-30 800 0% 2009-04-30 2000 +150% .... What do I have to do in order to come up with a query like this? Regards, Andreas . |
#8
|
|||
|
|||
Last day of month in query
If you need the last entered date for each account within a particular
year/month, and not necessarily the very last day of that month, you would have to use a subquery to get the maximum date for each year/month, something like: Select accountNumber, year(TheDate), month(TheDate), max(day(TheDate)) as LastDayOfTheMonth From MyTable Group by accountNumber, year(TheDate), month(TheDate) You could then join that query to your original table to select the actual balance on that last day of the month. "KARL DEWEY" wrote in message ... What if the last day of month was a holiday or weekend and therefore no data on that date? -- Build a little, test a little. "Andreas" wrote: Hi all, I have a query that gives me the dates and account balances like in the following example: Original query: Date Account balance 2009-01-03 500 2009-01-07 300 2009-01-31 1000 2009-02-15 800 2009-02-28 800 2009-03-30 800 2009-04-28 2000 2009-04-30 2000 .... I now would like to extract only the values for the last day of a month and put them in a new query. Keep in mind that the last day could be 31st, 30th, 28th (29th). In addition, I have multiple years of data. Also, I need the percentage change from one month to another. As a result, this second query should then looks like that: Date Account balance %-Change 2009-01-31 1000 0% 2009-02-28 800 -20% 2009-03-30 800 0% 2009-04-30 2000 +150% .... What do I have to do in order to come up with a query like this? |
#9
|
|||
|
|||
Last day of month in query
On Jan 20, 9:41*pm, fredg wrote:
As criteria on the Date Field write: =DateSerial(Year([DateField]),Month([DateField])+1,0) @all Thanks for giving me all these tips. @fredg This works particular well. Now, what if I tell you that my date field is a combined date/time field. Is it possible to ignore the time when performing the query? Alternatively, I could place an extra entry for every last day of the month with 11:59pm as time, such as 2009-12-31 11:59pm. How must the search criteria (or SQL code) look like to eather ignore the time completely (favourite solution) or search for the last day of the month including 11:59pm as time? @all What about my problem regarding the calculation of the percentage change from one month to another? Any suggestions for this issue? Thanks a lot! Regards, Andreas |
#10
|
|||
|
|||
Last day of month in query
Since your criteria is strictly = a given date, you don't need to do
anything because of the time. You'd only have to if your criteria was = a given date. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Andreas" wrote in message ... On Jan 20, 9:41 pm, fredg wrote: As criteria on the Date Field write: =DateSerial(Year([DateField]),Month([DateField])+1,0) @all Thanks for giving me all these tips. @fredg This works particular well. Now, what if I tell you that my date field is a combined date/time field. Is it possible to ignore the time when performing the query? Alternatively, I could place an extra entry for every last day of the month with 11:59pm as time, such as 2009-12-31 11:59pm. How must the search criteria (or SQL code) look like to eather ignore the time completely (favourite solution) or search for the last day of the month including 11:59pm as time? @all What about my problem regarding the calculation of the percentage change from one month to another? Any suggestions for this issue? Thanks a lot! Regards, Andreas |
|
Thread Tools | |
Display Modes | |
|
|