Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2008
    Posts
    9

    Unanswered: Joining two tables causes calculation error

    I have the following two queries that produce the correct data that I am looking for:

    SELECT DISTINCTROW DAgentGroupByAgent.AgentName, DAgentGroupByAgent.AgentId, Sum(DAgentGroupByAgent.AcdCallsAnswered) AS [Sum Of AcdCallsAnswered]
    FROM DAgentGroupByAgent
    WHERE (((DAgentGroupByAgent.EVENTDATE)>=#6/17/2008# And (DAgentGroupByAgent.EVENTDATE)<#6/18/2008#))
    GROUP BY DAgentGroupByAgent.AgentName, DAgentGroupByAgent.AgentId;


    SELECT DISTINCTROW tblCallRecord.AGENT, Count(tblCallRecord.AGENT) AS [Sum Of AGENT]
    FROM tblCallRecord
    WHERE (((tblCallRecord.DATETIME)>=#6/17/2008# And (tblCallRecord.DATETIME)<#6/18/2008#))
    GROUP BY tblCallRecord.AGENT;

    The problem occurs When I want to combine the two queries to have one query, the [Calls Acctd For] appears to equal the [Sum Of AcdCallsAnswered]. Here's the query:

    SELECT DAgentGroupByAgent.AgentName, COUNT(tblCallRecord.AGENT) AS [Calls Acctd For], SUM(DAgentGroupByAgent.AcdCallsAnswered) AS [Sum Of AcdCallsAnswered]
    FROM tblCallRecord, DAgentGroupByAgent
    WHERE (tblCallRecord.DATETIME)>=#6/17/2008# And (tblCallRecord.DATETIME)<#6/18/2008# AND (DAgentGroupByAgent.EVENTDATE)>=#6/17/2008# And (DAgentGroupByAgent.EVENTDATE)<#6/18/2008# AND tblCallRecord.AGENT = DAgentGroupByAgent.AgentId
    GROUP BY DAgentGroupByAgent.AgentName;


    Any ideas why the COUNT(tblCallRecord.AGENT) AS [Calls Acctd For] is not calculating correctly?

    Thank you in advance for your help.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Just thought I'd throw a few thoughts out there to help you troubleshoot. It's difficult for me to diagnose your issue without seeing the data and results.

    Instead of combining the count and sum functions into the same query, have you tried creating separate queries (or make "temp" table queries) for 1 or both functions and then either utilizing the temp table or the dlookup function, get the resulting value(s) into a final query. Or I wonder if this part of the query (AND tblCallRecord.AGENT = DAgentGroupByAgent.AgentId) isn't giving you problems. It also looks like you are querying on 2 date fields which makes it even more difficult to troubleshoot from this prospective.

    Also remember that when you combine 2 or more relational tables into 1 query you produce a "flat" type result set (where records can be duplicated). To account for this and calculation type queries, you may either need to use 1 more nested queries (ie. a query based on another query) or create temporary tables to achieve the final calculation. This is especially true when you are dealing with date fields and showing dates in the query. I will often create a temporary table on the initial result set where dates are involved and then use that temporary table in another query to get the final result set. I can't help but wonder if you're trying to query with too many different criteria parameters in the same query and perhaps shouldn't break it apart. Although I'm being a little vague on your specific issue, I hope that helps. Again, difficult to troubleshoot without seeing the data and results.
    Last edited by pkstormy; 06-19-08 at 21:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2008
    Posts
    9
    Thank you for your suggestion pkstormy- I will try the temp table. The nested query worked originally for me in Access. However, I need to have this query to take two input variables (the two dates) from a form input (which then passess the variables to my sql statements).

    I appreciate the help and will let you know of the results of the temp table.

    sqluzr

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you need to supply input values (such as dates), it's often good to create a "criteria" form whereas that form remains open and the query looks up the values on the form and uses them.

    See a few of the reporting examples in the code bank on how to do this (here's one example you may find helpful: http://www.dbforums.com/showpost.php...1&postcount=21)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jun 2008
    Posts
    9
    Here are my three queries that work inside of MS Access:

    First Query:
    SELECT DISTINCTROW DAgentGroupByAgent.AgentName, DAgentGroupByAgent.AgentId, Sum(DAgentGroupByAgent.AcdCallsAnswered) AS [Sum Of AcdCallsAnswered]
    FROM DAgentGroupByAgent
    WHERE (((DAgentGroupByAgent.EVENTDATE)>=#6/17/2008# And (DAgentGroupByAgent.EVENTDATE)<#6/18/2008#))
    GROUP BY DAgentGroupByAgent.AgentName, DAgentGroupByAgent.AgentId;

    Second Query:
    SELECT DISTINCTROW tblCallRecord.AGENT, Count(tblCallRecord.AGENT) AS [Sum Of AGENT]
    FROM tblCallRecord
    WHERE (((tblCallRecord.DATETIME)>=#6/17/2008# And (tblCallRecord.DATETIME)<#6/18/2008#))
    GROUP BY tblCallRecord.AGENT;

    and then the query that queries the two:

    Third Query:
    SELECT [qryACDCallsAns].[AgentName], [qryCallsAcctFor].[Sum of Agent] AS [Calls Acctd For], [qryACDCallsAns].[Sum of ACDCallsAnswered] AS [ACD Calls Ansd]
    FROM qryACDCallsAns LEFT JOIN qryCallsAcctFor ON INT([qryACDCallsAns].AgentID) = INT([qryCallsAcctFor].Agent);


    When I attempt to execute the queries through ADODB recordset, I receive an "HTTP 500 error" when I try to execute the sql queries. I tried just executing the first sql query and still receive HTTP 500.
    My goal is to execute the two queries but write the results of the third query.

    Oddly enough, the following code works if I execute the following query:


    'Response.Buffer = true
    dim cnn,rst

    'Open Access Database ADO Connection
    set cnn = Server.CreateObject("ADODB.Connection")
    set rst = Server.CreateObject("ADODB.RecordSet")
    cnn.Open "driver={Microsoft Access Driver (*.mdb)};;DBQ=C:\inetpub\wwwroot\MyWeb\db.mdb;"

    'Get start and end date from form
    st_date =Request.Form("st_date")
    en_date =Request.Form("en_date")

    'Send SQL Statement for processing

    sqltext="SELECT tblReasonCode.REASONCODENAME, Count(*) AS [Count] FROM tblReasonCode RIGHT JOIN tblCallRecord ON tblReasonCode.[REASONCODE] = tblCallRecord.[REASONCODE] WHERE (((tblCallRecord.DATETIME)>=cdate('" & st_date & "')) And (tblCallRecord.DATETIME)<cdate('" & en_date & "' )) GROUP BY tblReasonCode.REASONCODENAME;"
    set rst = cnn.Execute(sqltext)

    'Display SQL statement result
    Response.Write"<table border=1><tr>"
    While NOT rst.EOF
    Response.Write"<tr><td>DAgentGroupByAgent</td><td>"
    Response.Write rst("REASONCODENAME")&"</td></tr>"
    Response.Write"<tr><td>Sum</td><td>"
    Response.Write rst("Count")&"</td></tr>"
    rst.MoveNext
    WEND
    Response.Write"</table>"

    When I put this sql statement, I get HTTP 500:

    sqltext="SELECT DAgentGroupByAgent.AgentName,DAgentGroupByAgent.Ac dCallsAnswered FROM DAgentGroupByAgent WHERE (DAgentGroupByAgent.EVENTDATE)>=cdate('" & st_date & "') And (DAgentGroupByAgent.EVENTDATE)<cdate('" & en_date & "') GROUP BY DAgentGroupByAgent.AgentName;"

    Any help on this would be greatly appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •