Results 1 to 3 of 3
  1. #1
    WEJ is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2013
    Posts
    7

    Exclamation Summing Multiple Queries & Summing Time

    Hi There,

    I've come up against a bit of a problem.
    I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.
    I've created 3 queries;
    1 - Counts Calls


    2 - Counts Emails
    3 - Counts Meetings

    These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.

    I have a second query as well (no pun intended).

    I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).

    Thanks for your help!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What do you mean by 'combine', what happens? Need to provide query and maybe even data samples for analysis.

    Converting elapsed time value to a clock representation will require calculation with concatenation.

    x = Time results in minutes.

    Int(x / 60) & ":" Trim(x Mod 60)

    Do calc in query or in textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    If you post your queries, then we can kibbitz.

    My guess is that you are trying to bind your calls, emails and meetings all together, and getting a cross join instead of unique numbers.

    If you have an email file with 2 records for a particular sale, a call file with 3 records for that sale, and a meeting file with 1 record for that sale, then a cross join would give you 6 records, and summing up the calls, emails and meetings could give you 18 or 36 or whatever, depending on how you coded it. If there happened to be no emails for a particular sale, you might get 0 as your result for that sale.

    If you use a left join against your sales table for each other table, then you can add up the number of calls (or emails, or meetings), and get zero if that's the correct answer. Those queries would look a little like this:
    Code:
    QueryCalls -
    SELECT 
       E1S.SalesID AS CallSalesID, 
       SUM(NZ(E1C.Duration,0)) AS CallTotTime, 
       SUM(IIF(IsNull(E1C.SalesID),0,1)) AS CallCount 
    FROM 
       E1_Sales AS E1S LEFT JOIN E1_Calls AS E1C ON E1S.SalesID = E1C.Salesid
    GROUP BY E1S.SalesID;
    
    QueryEmails -
    SELECT 
       E1S.SalesID, 
       SUM(NZ(E1E.Duration,0)) AS EmailTotTime, 
       SUM(IIF(IsNull(E1E.SalesID),0,1)) AS EmailCount
    FROM 
       E1_Sales AS E1S LEFT JOIN E1_Emails AS E1E ON E1S.SalesID = E1E.Salesid
    GROUP BY E1S.SalesID;
    
    QueryMeets -
    SELECT 
       E1S.SalesID AS MeetSalesID, 
       SUM(NZ(E1M.Duration,0)) AS MeetTotTime, 
       SUM(IIF(IsNull(E1M.SalesID),0,1)) AS MeetCount
    FROM 
       E1_Sales AS E1S LEFT JOIN E1_Meetings AS E1M ON E1S.SalesID = E1M.Salesid
    GROUP BY E1S.SalesID;
    Since you have a single record coming out of each of those queries for each salesID, regardless of the number of touches of ecah type, you can now join each of those single records together and you know there will be precisely one match for every unique SalesID.
    Code:
    QueryAll -
    SELECT
       E1S.SalesID,
       QC.CallTotTime,
       QC.CallCount,
       QE.EmailTotTime
       QE.EmailCount,
       QM.MeetTotTime,
       QM.MeetCount,
       QC.CallTotTime + QE.EmailTotTime + QM.MeetTotTime AS TouchTime,
       QC.CallCount + QE.EmailCount + QM.MeetCount AS TouchCount
    FROM
       (E1_Sales AS E1S INNER JOIN QueryCalls AS QC ON E1S.SalesID = QC.SalesID) 
       INNER JOIN 
       (QueryEmails AS QE INNER JOIN QueryMeets AS QM ON QE.SalesID = QM.SalesID) 
       ON E1S.SalesID = QM.Salesid;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-18-2013, 08:17 PM
  2. Summing Time is giving me wrong totals.
    By Nuke1096 in forum Access
    Replies: 7
    Last Post: 06-13-2013, 11:00 AM
  3. Replies: 7
    Last Post: 01-02-2013, 11:23 PM
  4. Newbie getting stuck with summing queries
    By snorky in forum Access
    Replies: 2
    Last Post: 01-19-2012, 03:42 PM
  5. summing
    By nashr1928 in forum Forms
    Replies: 18
    Last Post: 04-05-2011, 05:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums