?
Solved

SQL Query to extract records by month.

Posted on 2003-03-30
9
Medium Priority
?
1,301 Views
Last Modified: 2008-02-26
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
Comment
Question by:Hallucin8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 12

Expert Comment

by:nexusnation
ID: 8236229
try a

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

or something similar.

understand?

nex
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 300 total points
ID: 8236311
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
 

Expert Comment

by:DarrelTech
ID: 8237785
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8237839
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
 

Author Comment

by:Hallucin8
ID: 8238121
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8239141
Did my solution give you any joy ?
0
 

Author Comment

by:Hallucin8
ID: 8241461
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8241613
If your table only contains those two fields:
SELECT [No],First([Date]) FROM MyTable GROUP BY [No]
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8241617
Actually scratch that. The above won't work.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question