Queries, Dates, Months, Years, Counting & Grouping issues

[l3111]

Hi accessforums.net agen!!



This forum has been amazing, and i'm hoping it will once agen help me solve, what i feel may turn out to be, a very simply query issue.

I have a table that has stores referrals, all assigned a referral date. I would like to create a query that collates how many referrals are received in a month. i have managed to extract the month from the the ReferralDate, using Month([ReferralDate]) and the year using Year([ReferralDate])....but now i'm stuck

As it stands i have 19 referrals, but this query is only showing 15 (as some are on the same day and the query is grouping by ReferralDate)

I would like to group by Month & Year, and to concatenate month and Year....but it comes up as 03-2011 rather than March 2011

Knowing how many referrals are coming in each month is vital information, the fact i'm gettin bogged down in this query is fustrating

pleaaaaase help

l3111, Manchester, UK

[Rhino373]

If you are grouping by month and year, then you need to be showing the count of referrals. Showing the count would add even those that have the same month and year thus giving you 19.

You see the date in the specific format because your original data is of a date format and you are using functions that are date functions such as month() and year(). You'll need to use CStr() to convert it to a string. You'll need a function to turn the month value (03) into the string equivalent "March".

If you really wanted to be inefficient about it you would make a huge nested query that started like IIF(Month(ReferralDate) = 01, "January " & CStr(Year(ReferralDate)), IIF(Month(ReferralDate) = 02, "February & CStr(Year(ReferralDate)), IIF(.......

Really you need a function though