Sub Query

[vitordf]

Hi,


It's driving me mad, is anyone can help me is checking why this sub query is not working and gives me the message:
"At most one record can be returned by this query"
My aim is to create a field within the query to return the values of the previous month as the current month to be able to perform the variance.

SELECT Tbl_Centres.Centre, 
Sum(tbl_TEL.Calls) AS SumOfCalls, 
(SELECT Tbl_Calen.Month FROM Tbl_Calen WHERE ((Tbl_Calen.Month)=DateAdd('m',-1,[Enter Month]))) AS Month_2, 
Tbl_Calen.Month
FROM 
(((Tbl_EmployeeAll INNER JOIN tbl_Telephony_Data AS tbl_TEL ON Tbl_EmployeeAll.UserID = tbl_TEL.ID) INNER JOIN Tbl_Centres ON Tbl_EmployeeAll.Location = Tbl_Centres.OrgLocation) INNER JOIN Tbl_CostCodes ON Tbl_EmployeeAll.CostCode = Tbl_CostCodes.CostCode) INNER JOIN Tbl_Calen ON tbl_TEL.Week = Tbl_Calen.WeekNbr
WHERE (((Tbl_CostCodes.Dept)="Service") AND ((Tbl_Calen.Month)=DateAdd('m',-1,[Enter Month])))
GROUP BY Tbl_Centres.Centre, Tbl_Calen.Month;

Thanks in advance,

[orange]

Great sub Query reference with examples
http://allenbrowne.com/subquery-01.html

[Dal Jeanis]

Not sure I get what you are trying to do by plopping that subquery down in that spot. The subquery logically must give you the exact same value for Month_2 as tbl_Calen.Month was giving you. The date must already exist in tbl_Calen, or the bottom WHERE clause will return no records anyway.

This is a cleaned up version of your current query:

SELECT T3.Centre, TC.Month As CallMonth, Sum(T2.Calls) AS SumOfCalls 
FROM (((Tbl_EmployeeAll AS T1 
        INNER JOIN tbl_Telephony_Data AS T2 ON T1.UserID = T2.ID) 
        INNER JOIN Tbl_Centres AS T3        ON T1.Location = T3.OrgLocation) 
        INNER JOIN Tbl_CostCodes AS T4      ON T1.CostCode = T4.CostCode) 
        INNER JOIN Tbl_Calen AS TC          ON T2.Week = TC.WeekNbr
WHERE (((T4.Dept)="Service") AND ((TC.Month)=DateAdd('m',-1,[Enter Month])))
GROUP BY T3.Centre, TC.Month;

If you tell me what you wanted the second copy of "Month" for, then I can help you figure out how to get it.

By the way, Month is a reserved word, and using reserved words as table names or field names should be avoided whenever possible. And it's always possible.

[vitordf]

Hi Dal Jeanis,
Thanks for your comments and solution.
What I am aiming is to create a field within the query to return the values of the previous month and the results for the current month to be able to perform the variance. So I guess that I would need a SubQuery for the extra field, something like:

SELECT T3.Centre, Sum(T2.Calls) AS SumOfCallsCurrentMonth,

SUBQuery here giving the SumOfCallsPreviousMonth

FROM (((Tbl_EmployeeAll AS T1
INNER JOIN tbl_Telephony_Data AS T2 ON T1.UserID = T2.ID)
INNER JOIN Tbl_Centres AS T3 ON T1.Location = T3.OrgLocation)
INNER JOIN Tbl_CostCodes AS T4 ON T1.CostCode = T4.CostCode)
INNER JOIN Tbl_Calen AS TC ON T2.Week = TC.WeekNbr
WHERE (((T4.Dept)="Service") AND ((TC.Month)=[Enter Month])) GROUP BY T3.Centre, TC.Month;

In regards to your comment of Month being a reserved word, would you be so kind and advise what should I use instead of Month as a Field name where the data is a month or day or week etc., is there any documentation that I could refer to?

Kind regards,

[Dal Jeanis]

I think this will get you what you want.

SELECT 
   T3.Centre, 
   DateAdd('m',-1,[Enter Month]) AS CallMonth1,
   Sum(IIF(TC.Month=CallMonth1,T2.Calls,0)) AS CallSum1,
   DateAdd('m',-2,[Enter Month]) AS CallMonth2,
   Sum(IIF(TC.Month=CallMonth2,T2.Calls,0)) AS CallSum2
FROM (((Tbl_EmployeeAll AS T1 
        INNER JOIN Tbl_Centres AS T3        ON T1.Location = T3.OrgLocation) 
        INNER JOIN Tbl_CostCodes AS T4      ON T1.CostCode = T4.CostCode) 
        INNER JOIN tbl_Telephony_Data AS T2 ON T1.UserID = T2.ID) 
        INNER JOIN Tbl_Calen AS TC          ON T2.Week = TC.WeekNbr)
WHERE ((T4.Dept)="Service")
GROUP BY T3.Centre)

Basically, you were only getting one record per call centre anyway, so I set it up to leave the tbl_Telephony_Data records nice and flat and complete, and just sum up the relevant ones in to the two desired buckets, ignoring the rest.

See if it gives you something like the results you're looking for.