Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group by Month/Year from start and end Date

Status
Not open for further replies.

spartansFC

Programmer
Apr 1, 2009
165
GB
Hi

I need to somehow create a totals query which somehow extracts all the months between 2 dates, so for example if it was:

01/01/2013 to 31/03/2013, it would ouput Jan-13, Feb-13, Mar-13. The query i'm trying to create the totals query looks like:

Code:
SELECT tblChildrenRunNumber.curRunNumber, tblChildrenRunNumber.lngChildRunNoID, tblDropDownsRunNo.strRunNoDesc, tblDropDownsRunType.strRunTypeDesc, [Name2] & " " & [Name1] AS Childsname, tblChildrenRunNumber.dteFrom, tblChildrenRunNumber.dteTo, qryRunNumberSub3SchoolName.Schoolname, tblContractorVehicles.lngVehicleReg
FROM tblContractorVehicles INNER JOIN (tblContractors INNER JOIN ((qryRunNumberSub3SchoolName INNER JOIN ((tblChildrenRunNumber INNER JOIN tblDropDownsRunNo ON tblChildrenRunNumber.lngChildRunNoDescID = tblDropDownsRunNo.lngRunNoID) INNER JOIN tblDropDownsRunType ON tblChildrenRunNumber.lngRunNoTypeID = tblDropDownsRunType.lngRunTypeID) ON qryRunNumberSub3SchoolName.PID = tblChildrenRunNumber.lngRunSchoolNameID) INNER JOIN c_contacts ON tblChildrenRunNumber.lngChildID = c_contacts.PID) ON tblContractors.lngRunNoContractID = tblChildrenRunNumber.lngRunContractorID) ON tblContractorVehicles.lngVehicleLicenceID = tblChildrenRunNumber.lngVehicleLicenseID
WHERE (((tblChildrenRunNumber.curRunNumber)=Yes));

so it would output

lngChildRunNoID StrRunNoDesc StrRunTypeDesc Childsname dteFrom dteto SchoolName lngVehicleReg Month
5 RunNo1 HTS Child1 01/01/14 31/03/14 School1 Reg1 Jan-14
5 RunNo1 HTS Child1 01/01/14 31/03/14 School1 Reg1 Feb-14
5 RunNo1 HTS Child1 01/01/14 31/03/14 School1 Reg1 Mar-14

I thought i might need a seperate table with

dteFrom
dteTo
Month-Yr

and then link the table to the query via dtefrom, i did try but it didn't work out.

Not even sure if this is possible.

Michael
 
Please preview your "output" prior to submitting so it can be understood without much effort.

Code:
[u]lngChildRunNoID  StrRunNoDesc  StrRunTypeDesc  Childsname  dteFrom   dteto     SchoolName  lngVehicleReg  Month[/u]
5                RunNo1        HTS             Child1      01/01/14  31/03/14  School1     Reg1           Jan-14
5                RunNo1        HTS             Child1      01/01/14  31/03/14  School1     Reg1           Feb-14
5                RunNo1        HTS             Child1      01/01/14  31/03/14  School1     Reg1           Mar-14


Can't you just group by Month? Does your data contain values representing each month?

Duane
Hook'D on Access
MS Access MVP
 
How on earth did you do the datasheet preview, i do preview all my posts but never knew you could show an example of "output" data as you've shown.

Sorry about that. Anyways we've decided i'm going to use the recurring database to build attendance and then the calendar forms that you helped me with a while ago.

Thanks again Duane
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top