asked on

SQL Query to extract records by month.

This should be a pretty easy 100pts for any expert.

i'm using ms access and asp to extract data.

here's what my table looks like:

No Date
------- --------
1 3/21/2003
2 3/22/2003
3 4/23/2003
4 5/1/2003
5 5/2/2003
6 6/1/2003

And here's what i want my result set to look like:

No Date
---- --------
1 3/21/2003
3 4/23/2003
4 5/1/2003
6 6/1/2003

Basically, i want one single record per month.

Thanks in advance.

nexusnation

try a

SELECT DISTINCT
Format(Date,"mmm")
FROM tblName

or something similar.

understand?

nex

ASKER CERTIFIED SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DarrelTech

use this as you can also request which month

SELECT DISTINCT
Format(Date,"mmm")
FROM tblName
where tblname.date = %dated

but as mentioned stay away from using reserved names

shanesuebsahakarn

I would avoid just using the month, as you may need to take into account different years, although I don't know if this is a requirement.

Hallucin8

ASKER

I tried the following:
"SELECT DISTINCT Format(Date,'mmm')FROM tblLinks" but received the error: "item cannot be found in the collection corresponding to the requested or ordinal" i know i'm using the right field/table names.

shanesuebsahakarn

Did my solution give you any joy ?

Hallucin8

ASKER

This query did yield the intended result, a little bulkier then i would've liked (as i don't need the minimum from each month, it can be any record of the month, is this easily accomplished???

thanks.

shanesuebsahakarn

If your table only contains those two fields:
SELECT [No],First([Date]) FROM MyTable GROUP BY [No]

shanesuebsahakarn

Actually scratch that. The above won't work.