Code:
SELECT tblMIF_MGMT.VOSS, MonthName(Month([MIFMgtStatusDate])) AS [Month], (Day([MIFMgtStatusDate])-1)\7+1 AS Week, DCount("[RowID]",'tblMIF_MGMT',"[VOSS]='" & [voss] & "' and (day([mifmgtstatusdate]-1)\7+1) <=" & [Week] & " And Month([mifmgtstatusdate])=Month(Now()) And Year([mifmgtstatusdate])=Year(Now()) AND ([MIF Mgt Most Recent Status]='F2F Call Complete' Or [MIF Mgt Most Recent Status]= 'Order Signed' Or [MIF Mgt Most Recent Status]= 'Proposal Presented' Or [MIF Mgt Most Recent Status]= 'VQ/CSA Created' Or [MIF Mgt Most Recent Status]= 'XOA Delivered' Or [MIF Mgt Most Recent Status]= 'Site Visited' Or [MIF Mgt Most Recent Status]= 'Other-comment' Or [MIF Mgt Most Recent Status]= 'Offer Declined' Or [MIF Mgt Most Recent Status]= 'Validated Only')") AS cumproposals, Count(tblMIF_MGMT.Rowid) AS WklyProposals, [cumproposals]/([week]*150) AS Prct
FROM tblMIF_MGMT
WHERE (((tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'F2F Call Complete' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Order Signed' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Proposal Presented' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'VQ/CSA Created' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'XOA Delivered' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Site Visited' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Other-comment' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Offer Declined' Or (tblMIF_MGMT.[MIF Mgt Most Recent Status]) Like 'Validated Only'))
GROUP BY tblMIF_MGMT.VOSS, (Day([MIFMgtStatusDate])-1)\7+1, Year([MIFMgtStatusDate]), Month([MIFMgtStatusDate])
HAVING (((Year([MIFMgtStatusDate]))=Year(Now())) AND ((Month([MIFMgtStatusDate]))=Month(Now())));
I wish it was that easy There are many values in the Recent Status Field and this one looks at just a certain number (more than 1) where as Proposal Presented query was just looking at the single value