The most complicated query in the world

G

Guest

Hi,
The below query was working so fine,but when i added the table KPITools,it
does no longer work,i know it's to complicated,but i cannot go ithout it,
Whenever i run it,i get the message "Data type mismatch in criteria
expression",can anybody help?

"SELECT KPICSQ.[Fiscal month], KPICSQ.Agent, KPIATTEND.Unplanned,
KPIATTEND.SumOfWM AS [Working minutes], KPICSQ.CountOfSerial AS [Electronic
transactions], KPIESC1.CountOfReceived AS Cycle1, IIf([Cycle1] Is
Null,0,[Cycle1]) AS [Sending escalations], KPIESC2.CountOfCode2 AS Cycle2,
IIf([Cycle2] Is Null,0,[Cycle2]) AS [Call Backs], KPITools.Cases, [Sending
escalations]+[Call backs] AS Escalations, IIf([CountOfMA] Is
Null,0,[CountOfMA]) AS [Morning attendance], IIf([CountOfSHM] Is
Null,0,[CountOfSHM]) AS [Morning SH], IIf([CountOfSH] Is Null,0,[CountOfSH])
AS [Night SH], IIf([CountOfNA] Is Null,0,[CountOfNA]) AS [Night attendance],
IIf([CountOfDR] Is Null,0,[CountOfDR]) AS DR, IIf([CountOfDR] Is
Null,0,[CountOfDR]) AS [Daily reports], KPIs.AL, KPIs.TTT, ([Morning
attendance]*40)+([night attendance]*40)+([Night SH]*15)+([Morning
SH]*15)+([Daily reports]*15)+([AL]*15) AS [Tasks score], [Electronic
transactions]*20 AS [ET score], IIf([cases] Is Null,0,[cases]) AS Partners,
([sending escalations]*20)+([Call Backs]*20) AS [Escalations score], ([Tasks
score]+[ET score]+[Partners]+[TTT]+[Escalations score])/[Working minutes]*100
AS [Utilization%], Auditagent.Audit, IIf([TTT]>=300,15,0) AS [Account
Awareness], IIf(([Utilization%])>=40,30,([Utilization%]/40*100)*30/100) AS
[Utilization score], IIf([unplanned]<=0,10,0) AS [Attendance score],
Adherance.Adherance, KPIs.SV, [Attendance score]+[Utilization
score]+[SV]+[Account Awareness]+[Audit]+[Adherance] AS Ranking
FROM ((((((((((agents INNER JOIN ((KPICSQ LEFT JOIN KPIATTEND ON
(KPICSQ.Agent = KPIATTEND.Agent) AND (KPICSQ.[Fiscal month] =
KPIATTEND.[Fiscal month])) LEFT JOIN KPIESC1 ON (KPICSQ.Agent =
KPIESC1.Escagent) AND (KPICSQ.[Fiscal month] = KPIESC1.[Fiscal month])) ON
agents.Agent = KPICSQ.Agent) LEFT JOIN KPIMA ON (KPICSQ.[Fiscal month] =
KPIMA.[Fiscal month]) AND (KPICSQ.Agent = KPIMA.Agent)) LEFT JOIN KPIDR ON
(KPICSQ.[Fiscal month] = KPIDR.[Fiscal month]) AND (KPICSQ.Agent =
KPIDR.Agent)) LEFT JOIN KPIMSH ON (KPICSQ.[Fiscal month] = KPIMSH.[Fiscal
month]) AND (KPICSQ.Agent = KPIMSH.Agent)) LEFT JOIN KPINA ON (KPICSQ.[Fiscal
month] = KPINA.[Fiscal month]) AND (KPICSQ.Agent = KPINA.Agent)) LEFT JOIN
KPINSH ON (KPICSQ.Agent = KPINSH.Agent) AND (KPICSQ.[Fiscal month] =
KPINSH.[Fiscal month])) LEFT JOIN Auditagent ON (KPICSQ.[Fiscal month] =
Auditagent.[Fiscal month]) AND (KPICSQ.Agent = Auditagent.Agent)) LEFT JOIN
KPIs ON (KPICSQ.Agent = KPIs.Agent) AND (KPICSQ.[Fiscal month] = KPIs.[Fiscal
month])) LEFT JOIN KPIESC2 ON (KPICSQ.Agent = KPIESC2.Agent) AND
(KPICSQ.[Fiscal month] = KPIESC2.[Fiscal month])) LEFT JOIN Adherance ON
(KPICSQ.Agent = Adherance.Agent) AND (KPICSQ.[Fiscal month] =
Adherance.[Fiscal month])) LEFT JOIN KPITools ON (KPICSQ.[Fiscal month] =
KPITools.[Fiscal month]) AND (KPICSQ.Agent = KPITools.Agent)
ORDER BY KPICSQ.[Fiscal month];
 
G

Guest

On your joins to fields in the KPITools table, are the data types of the
fields you're joining the same type?
Are KPICSQ.[Fiscal month] and KPITools.[Fiscal month] the same data type?
Similarly, are KPICSQ.Agent and KPITools.Agent the same datatype?

If KPICSQ.[Fiscal month] is numeric and KPITools.[Fiscal month] is text,
you'll get that error.


Pietro said:
Hi,
The below query was working so fine,but when i added the table KPITools,it
does no longer work,i know it's to complicated,but i cannot go ithout it,
Whenever i run it,i get the message "Data type mismatch in criteria
expression",can anybody help?

"SELECT KPICSQ.[Fiscal month], KPICSQ.Agent, KPIATTEND.Unplanned,
KPIATTEND.SumOfWM AS [Working minutes], KPICSQ.CountOfSerial AS [Electronic
transactions], KPIESC1.CountOfReceived AS Cycle1, IIf([Cycle1] Is
Null,0,[Cycle1]) AS [Sending escalations], KPIESC2.CountOfCode2 AS Cycle2,
IIf([Cycle2] Is Null,0,[Cycle2]) AS [Call Backs], KPITools.Cases, [Sending
escalations]+[Call backs] AS Escalations, IIf([CountOfMA] Is
Null,0,[CountOfMA]) AS [Morning attendance], IIf([CountOfSHM] Is
Null,0,[CountOfSHM]) AS [Morning SH], IIf([CountOfSH] Is Null,0,[CountOfSH])
AS [Night SH], IIf([CountOfNA] Is Null,0,[CountOfNA]) AS [Night attendance],
IIf([CountOfDR] Is Null,0,[CountOfDR]) AS DR, IIf([CountOfDR] Is
Null,0,[CountOfDR]) AS [Daily reports], KPIs.AL, KPIs.TTT, ([Morning
attendance]*40)+([night attendance]*40)+([Night SH]*15)+([Morning
SH]*15)+([Daily reports]*15)+([AL]*15) AS [Tasks score], [Electronic
transactions]*20 AS [ET score], IIf([cases] Is Null,0,[cases]) AS Partners,
([sending escalations]*20)+([Call Backs]*20) AS [Escalations score], ([Tasks
score]+[ET score]+[Partners]+[TTT]+[Escalations score])/[Working minutes]*100
AS [Utilization%], Auditagent.Audit, IIf([TTT]>=300,15,0) AS [Account
Awareness], IIf(([Utilization%])>=40,30,([Utilization%]/40*100)*30/100) AS
[Utilization score], IIf([unplanned]<=0,10,0) AS [Attendance score],
Adherance.Adherance, KPIs.SV, [Attendance score]+[Utilization
score]+[SV]+[Account Awareness]+[Audit]+[Adherance] AS Ranking
FROM ((((((((((agents INNER JOIN ((KPICSQ LEFT JOIN KPIATTEND ON
(KPICSQ.Agent = KPIATTEND.Agent) AND (KPICSQ.[Fiscal month] =
KPIATTEND.[Fiscal month])) LEFT JOIN KPIESC1 ON (KPICSQ.Agent =
KPIESC1.Escagent) AND (KPICSQ.[Fiscal month] = KPIESC1.[Fiscal month])) ON
agents.Agent = KPICSQ.Agent) LEFT JOIN KPIMA ON (KPICSQ.[Fiscal month] =
KPIMA.[Fiscal month]) AND (KPICSQ.Agent = KPIMA.Agent)) LEFT JOIN KPIDR ON
(KPICSQ.[Fiscal month] = KPIDR.[Fiscal month]) AND (KPICSQ.Agent =
KPIDR.Agent)) LEFT JOIN KPIMSH ON (KPICSQ.[Fiscal month] = KPIMSH.[Fiscal
month]) AND (KPICSQ.Agent = KPIMSH.Agent)) LEFT JOIN KPINA ON (KPICSQ.[Fiscal
month] = KPINA.[Fiscal month]) AND (KPICSQ.Agent = KPINA.Agent)) LEFT JOIN
KPINSH ON (KPICSQ.Agent = KPINSH.Agent) AND (KPICSQ.[Fiscal month] =
KPINSH.[Fiscal month])) LEFT JOIN Auditagent ON (KPICSQ.[Fiscal month] =
Auditagent.[Fiscal month]) AND (KPICSQ.Agent = Auditagent.Agent)) LEFT JOIN
KPIs ON (KPICSQ.Agent = KPIs.Agent) AND (KPICSQ.[Fiscal month] = KPIs.[Fiscal
month])) LEFT JOIN KPIESC2 ON (KPICSQ.Agent = KPIESC2.Agent) AND
(KPICSQ.[Fiscal month] = KPIESC2.[Fiscal month])) LEFT JOIN Adherance ON
(KPICSQ.Agent = Adherance.Agent) AND (KPICSQ.[Fiscal month] =
Adherance.[Fiscal month])) LEFT JOIN KPITools ON (KPICSQ.[Fiscal month] =
KPITools.[Fiscal month]) AND (KPICSQ.Agent = KPITools.Agent)
ORDER BY KPICSQ.[Fiscal month];
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CDate results in Data Type Mismatch in Query & Excel Pivot 1
Problem with (complicated) expression in query 3
Min and Max for Date Range in Report 4
Slow query 1
Multiple supervisors over a date range in query 1
Memo field in Query 4
Can someone explain the behavior of this query? 7
How can 8 fields be too many in a query?? 1

Top