Query end of month data

M

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!
 
P

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!
Click to expand...

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
 
L

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
 
L

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
 
M

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!
 
L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

functions in queries 1
Looping through data with calculations 1
How to build subquery on aggregate query 0
IF statement with 2 logicals 1
Need advice re. extracting data for a cycle 7
Help to Highlight Excel Row based on Weekly Date Range 2
To Calculate the qty b/w two dates 3
Reverse Index Match 2

Top