Query end of month data

[Michelle]

Hello all!

I'm trying to write a make table query using the table "Portfolio" to pull
out only the end of month data. For example, the Portfolio table is set up
something like this:

Date | Security | Amt
7/31/08 | A | 10
7/31/08 | B | 20
7/31/08 | C | 30
..
..
..
8/28/08 | A | 10
8/28/08 | B | 20
8/28/08 | C | 30
8/29/08 | A | 10
8/29/08 | B | 20
8/29/08 | C | 30

I want the query to create a new table that only pulls out:
Date | Security | Amt
7/31/08 | A | 10
7/31/08 | B | 20
7/31/08 | C | 30
8/29/08 | A | 10
8/29/08 | B | 20
8/29/08 | C | 30
There are also holidays to consider. I'm not opposed to writing VBA code to
do this, but I really don't know where to start. (I'm also very new to SQL,
if you can explain well I can probably pull that off if need be. ^_~) I've
seen postings that take care of pulling out one month's data, but not
anything that would allow me to skip through the months to the last day I
have data for. Please let me know if I have been unclear about anything, and
I'll be happy to clarify. Any help would be greatly appreciated. Thanks!

 

[pietlinden]

Hello all!

I'm trying to write a make table query using the table "Portfolio" to pull
out only the end of month data.  For example, the Portfolio table is set up
something like this:

Date | Security | Amt
7/31/08 | A | 10
7/31/08 | B | 20
7/31/08 | C | 30
.
.
.
8/28/08 | A | 10
8/28/08 | B | 20
8/28/08 | C | 30
8/29/08 | A | 10
8/29/08 | B | 20
8/29/08 | C | 30

I want the query to create a new table that only pulls out:
Date | Security | Amt
7/31/08 | A | 10
7/31/08 | B | 20
7/31/08 | C | 30
8/29/08 | A | 10
8/29/08 | B | 20
8/29/08 | C | 30
There are also holidays to consider.  I'm not opposed to writing VBA code to
do this, but I really don't know where to start.  (I'm also very new toSQL,
if you can explain well I can probably pull that off if need be.  ^_~)  I've
seen postings that take care of pulling out one month's data, but not
anything that would allow me to skip through the months to the last day I
have data for.  Please let me know if I have been unclear about anything, and
I'll be happy to clarify.  Any help would be greatly appreciated.  Thanks!

Judging from your data, you're skipping Holidays and Weekends.

You could start here and then pass the last date of the month to the
GetBusinessDay function from Arvin's website...
www.datastrat.com

then go to the downloads section.

?dateserial(year(date()),month(date()),0)
7/31/2008

 

[pietlinden]

Judging from your data, you're skipping Holidays and Weekends.

You could start here and then pass the last date of the month to the
GetBusinessDay function from Arvin's website...www.datastrat.com

then go to the downloads section.

?dateserial(year(date()),month(date()),0)
7/31/2008

never mind... what am I thinking? How about this?
http://www.mvps.org/access/datetime/date0012.htm

 

[Lord Kelvan]

well

this query should give you the results you want

SELECT portfolio.date, portfolio.security, portfolio.amt
FROM portfolio
WHERE (((portfolio.date)
In (select max(day([date])) & "/" & month([date]) & "/" &
year([date])
from portfolio
group by month([date]), year([date]))));

what this query does is pulls your date apart to select the greatest
day for each month that is activly in your database in each year and
then puts it back together and compares it against the date

as a note that was done in my date format you will most likly need

SELECT portfolio.date, portfolio.security, portfolio.amt
FROM portfolio
WHERE (((portfolio.date)
In (select month([date]) & "/" & max(day([date])) & "/" &
year([date])
from portfolio
group by month([date]), year([date]))));

which puts the month before the day as this appears to be your country
date format

i hope this helps

Regards
Kelvan

 

[Lord Kelvan]

as a note my query only give you information relevant to what is in
the database forinstance if in your month you have 20/08/2008 as the
greatest record that is what will be displayed in the query not
31/08/2990

 

[Lord Kelvan]

cough typo sorry *****31/08/2008

 

[Michelle]

Kelvan,

This appears to be exactly what I'm looking for. There is one caveat that I
neglected to mention earlier. (I know, shame on me. I forgot.) The
portfolio table is huge and I therefore need to give it a start date. I
think the query will crash my computer otherwise trying to pull all that
historical data. I want only the EOM numbers starting 12/31/07. How would I
incorporate that to your SQL query? (Did I mention I'm a novice with SQL?
^_~) Thanks for your help thus far!

 

[Lord Kelvan]

SELECT portfolio.date, portfolio.security, portfolio.amt
FROM portfolio
WHERE (((portfolio.date)
In (select month([date]) & "/" & max(day([date])) & "/" &
year([date])
from portfolio
where [date] between [start date in format mm/dd/yyyy] and date()
group by month([date]), year([date]))));

that one will allow you to query between a point in time and todays
date

PARAMETERS [start date in format mm/dd/yyyy] DateTime, [end date in
format mm/dd/yyyy] DateTime;
SELECT portfolio.date, portfolio.security, portfolio.amt
FROM portfolio
WHERE (((portfolio.date)
In (select month([date]) & "/" & max(day([date])) & "/" &
year([date])
from portfolio
where [date] between [start date in format mm/dd/yyyy] and [end date
in format mm/dd/yyyy]
group by month([date]), year([date]))));

that one will allow you to do it between two points in time start date
and end date for some reason the paramaters are necessary because it
deems it to be too complex most likly because the condition is in the
sub query

i hope this helps

Regards
Kelvan

 

[Michelle]

Thanks for taking the time to help me out. I appreciate it. ^_^