Date question 2

[dbero]

If I have a start date of 1/1/2013 and an end date of 5/17/2013, is there a query way to return Jan, Feb,March, Apri and May? I am looking for the query to return the months during the start and end period.

Thank you!

 

[PHV]

A starting point:

SELECT MonthName(Month(yourDateField)), Sum(yourMoneyField) AS Total
FROM yourTable
WHERE yourDateField Between yourStartDate And yourEndDate
GROUP BY Month(yourDateField)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Is there at least one record for each month?

Duane
Hook'D on Access
MS Access MVP

 

[dbero]

There may or may not be a record for each month. what i have are hundreds of names with various beg/end dates, and I want to write to another table each person and a record for each month of their beg/end period. For examplel, if Mike is in from jan - march, i want to load to a table the following;
name month year
Mike jan 2012
Mike Feb 2012
Mike Mar 2012

 

[dhookom]

I would first create a table of dates for every month.
[tt]tblMonthDates
MthDate date/time
1/1/2013
2/1/2013
3/1/2013
etc[/tt]

Then create a query with tblMonthDates and your current table. Then set the criteria under the MthDate field to

Between [BeginDateField] AND [EndDateField]

You will want to make the query into a totals query with the [Hundreds of names field] and Format(MthDate,"mmm yyyy")

Duane
Hook'D on Access
MS Access MVP