mtd and ytd formula issues

[Jimmy]

I have a report that has 4 fields - business line, region, district and city.
I have a master table that houses all of the data. I want to create a
report that counts the number of referrals mtd and ytd. I created 2 queries
with the same data, but one shows only the mtd info. I can't get that number
in my report to populate.

 

[KARL DEWEY]

Post the SQL of the query you are using to feed the report by opening in
design view, click on VIEW - SQL View, highlight all, copy, and paste in a
post.

 

[Jimmy]

SELECT Count([Corporate Investment Services Referral Tracking].[Date
Referred]) AS [CountOfDate Referred], [Corporate Investment Services Referral
Tracking].[Business Line], [tbl_CIS Referral Map].Region, [tbl_CIS Referral
Map].District, [Corporate Investment Services Referral Tracking].City
FROM [Corporate Investment Services Referral Tracking] INNER JOIN [tbl_CIS
Referral Map] ON [Corporate Investment Services Referral Tracking].City =
[tbl_CIS Referral Map].City
GROUP BY [Corporate Investment Services Referral Tracking].[Business Line],
[tbl_CIS Referral Map].Region, [tbl_CIS Referral Map].District, [Corporate
Investment Services Referral Tracking].City;


This just does the ytd data. I haven't had any success with adding the mtd
data. Thank you!!

 

[Jimmy]

SELECT Count([Corporate Investment Services Referral Tracking].[Date
Referred]) AS [CountOfDate Referred], [Corporate Investment Services Referral
Tracking].[Business Line], [tbl_CIS Referral Map].Region, [tbl_CIS Referral
Map].District, [Corporate Investment Services Referral Tracking].City
FROM [Corporate Investment Services Referral Tracking] INNER JOIN [tbl_CIS
Referral Map] ON [Corporate Investment Services Referral Tracking].City =
[tbl_CIS Referral Map].City
GROUP BY [Corporate Investment Services Referral Tracking].[Business Line],
[tbl_CIS Referral Map].Region, [tbl_CIS Referral Map].District, [Corporate
Investment Services Referral Tracking].City;

 

[KARL DEWEY]

Try this --
SELECT Sum(IIF([Corporate Investment Services Referral Tracking].[Date
Referred] Between Date() AND DateSerial(Year(Date()),1,1), 1,0)) AS ytd,
Sum(IIF([Corporate Investment Services Referral Tracking].[Date Referred]
Between Date() AND DateSerial(Year(Date()),Month(Date()),1), 1,0)) AS mtd,
........


--
Build a little, test a little.

SELECT Count([Corporate Investment Services Referral Tracking].[Date
Referred]) AS [CountOfDate Referred], [Corporate Investment Services Referral
Tracking].[Business Line], [tbl_CIS Referral Map].Region, [tbl_CIS Referral
Map].District, [Corporate Investment Services Referral Tracking].City
FROM [Corporate Investment Services Referral Tracking] INNER JOIN [tbl_CIS
Referral Map] ON [Corporate Investment Services Referral Tracking].City =
[tbl_CIS Referral Map].City
GROUP BY [Corporate Investment Services Referral Tracking].[Business Line],
[tbl_CIS Referral Map].Region, [tbl_CIS Referral Map].District, [Corporate
Investment Services Referral Tracking].City;

Post the SQL of the query you are using to feed the report by opening in
design view, click on VIEW - SQL View, highlight all, copy, and paste in a
post.