Problems with a Cartesian join

[moody3829]

Hi, hope one of you experts can help me out. I have query that does calculations using data from two tables. The two tables do not have any fields in common so I created a translation table to join them. When I run the query I seem to be get getting the results you would explect from a Cartesian join. Can someone explain to me what I am doing wrong?

tblDailyACD contains daily information that I pull from our phone software and txtLogID is what identifies the agent.

tblSpecialistCounts contains daily totals from our ticketing system and txtXID indentifies the agent

tblAgent is my translation table that contains txtNames, txtXID, txtLogID, etc so that I can link my non related tables together.

Here is my query. For example for dblAVGACD I get 36 instead of 18. There are 18 rows in tblDailyACD I want the count from, but it seems to be multiplying this number against the 2 rows I get from the other table to give me 36.

strSQLRetrieve = "SELECT Sum(intNoansredir) AS intRONAs, Round(Sum(intNoansredir)/Sum(intAcdcalls),4) AS intRONAsPercent, " & _
"(Sum(intACDTime)+Sum(intVMEMTime)+Sum(intAvailTime)+Sum(intOther_HoldTime)+Sum(intRingTime))/(Sum(intStaffTime)-Sum(intLunchTime)) AS dblLCF, " & _
"(Sum(intACDTime)+Sum(intOther_HoldTime))/Sum(intAcdcalls) AS dblACT, Sum(intACDTime)/Sum(intAcdcalls) AS dblATT, " & _
"Count(tblDailyACD.txtLogID) As dblAVGACD, " & _
"Sum(intHDLSTime)/Sum(intTouch) AS dblSTPT, Sum(intTouch) AS intSPT, Sum(intRecovered) AS intRec " & _
"FROM(tblDailyACD INNER JOIN tblAgent ON tblDailyACD.txtLogID=tblAgent.txtLogID) INNER JOIN tblSpecialistCounts ON tblAgent.txtXID=tblSpecialistCounts.txtXID " & _
"WHERE dtRowDate Between DateSerial(2010,Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0) " & _
"AND dtDate Between DateSerial(2010,Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0) " & _
"AND tblDailyACD.txtLogID = '" & strLogID & "' AND tblSpecialistCounts.txtXID = '" & strXID & "'"

 

[moody3829]

I figured it out. Not sure if it's the right way, but I ended up doing a subqueries to get my results.

strSQLRetrieve = "SELECT Sum(intNoansredir) AS intRONAs, Round(Sum(intNoansredir)/Sum(intAcdcalls),4) AS intRONAsPercent, tblDailyACD.txtLogID, " & _
"(Sum(intACDTime)+Sum(intVMEMTime)+Sum(intAvailTime)+Sum(intOther_HoldTime)+Sum(intRingTime))/(Sum(intStaffTime)-Sum(intLunchTime)) AS dblLCF, " & _
"(Sum(intACDTime)+Sum(intOther_HoldTime))/Sum(intAcdcalls) AS dblACT, Sum(intACDTime)/Sum(intAcdcalls) AS dblATT, " & _
"Round(Sum(intAcdcalls)/Count(tblDailyACD.txtLogID),2) As dblAVGACD, " & _
"Sum(intHDLSTime)/SP.intSPT AS dblSTPT, SP.intSPT, SP.intRec, SP.intEmailVM " & _
"FROM " & _
"(SELECT Sum(intTouch) AS intSPT, Sum(intRecovered) AS intRec, SUM(intEMVM) AS intEmailVM, tblAgent.txtLogID " & _
"FROM tblSpecialistCOunts LEFT JOIN tblAgent on tblSpecialistCounts.txtXID=tblAgent.txtXID " & _
"WHERE dtDate Between DateSerial(2010,Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0) " & _
"AND tblSpecialistCounts.txtXID = '" & strXID & "'" & _
"GROUP BY tblAgent.txtLogID) AS SP " & _
"INNER JOIN tblDailyACD ON SP.txtLogID=tblDailyACD.txtLogID " & _
"WHERE dtRowDate Between DateSerial(2010,Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0) " & _
"AND tblDailyACD.txtLogID = '" & strLogID & "'" & _
"GROUP BY tblDailyACD.txtLogID, SP.intSPT, SP.intRec, SP.intEmailVM"