spartansFC
Programmer
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:
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
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