• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1303
  • Last Modified:

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.



 
0
Hallucin8
Asked:
Hallucin8
1 Solution
 
nexusnationCommented:
try a

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

or something similar.

understand?

nex
0
 
shanesuebsahakarnCommented:
Assuming you want the record with the earliest date of a particular month:

SELECT * FROM MyTable
WHERE MyTable.[No]=(SELECT Min([No]) FROM MyTable As A WHERE Month(A.[Date]) = Month(MyTable.[Date]) AND Year(A.[Date]) = Year(MyTable.[Date]))

Try to avoid using No and Date as field names though - these are reserved words and will cause problems (try omitting the brackets around the [No] in the statement above - it won't work because No is a value that is equal to 0 or False).
0
 
DarrelTechCommented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
shanesuebsahakarnCommented:
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.
0
 
Hallucin8Author Commented:
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.
0
 
shanesuebsahakarnCommented:
Did my solution give you any joy ?
0
 
Hallucin8Author Commented:
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.
0
 
shanesuebsahakarnCommented:
If your table only contains those two fields:
SELECT [No],First([Date]) FROM MyTable GROUP BY [No]
0
 
shanesuebsahakarnCommented:
Actually scratch that. The above won't work.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now