Results 1 to 5 of 5
  1. #1
    TimH is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2016
    Posts
    1

    Multiple Tables query with count/sum of third table based on query from first two tables.

    Help!
    I use Access 2016, and am a novice with SQL. Haven’t had any luck with this.
    I have 3 tables:
    ID Empl_ID First_Name Last_Name
    1 1000 Joe Smith
    2 1001 Mary Jones
    3 1002 Billy Blue
    4 1003 Donald Duck
    5 1004 John Doe
    6 1005 Jayne Dough

    id Caller_ID DateTime_ON DateTime_OFF
    1 1000 8/1/2016 8:00:00 AM 8/1/2016 11:00:00 AM
    2 1000 8/1/2016 12:15:00 PM 8/1/2016 5:00:00 PM
    3 1000 8/2/2016 9:15:00 AM 8/2/2016 1:00:00 PM
    4 1000 8/3/2016 8:00:00 AM 8/3/2016 10:00:00 AM
    5 1000 8/3/2016 11:00:00 AM 8/3/2016 6:00:00 PM
    6 1001 8/4/2016 8:00:00 AM 8/4/2016 4:00:00 PM
    7 1001 8/5/2016 7:45:00 AM 8/5/2016 10:46:00 AM
    8 1001 8/6/2016 9:00:00 AM 8/6/2016 5:00:00 PM
    9 1002 8/7/2016 10:00:00 AM 8/7/2016 5:00:00 PM
    10 1003 8/1/2016 4:45:00 PM 8/2/2016 2:00:00 AM
    11 1003 8/2/2016 1:00:00 PM 8/2/2016 10:00:00 PM
    12 1004 8/3/2016 5:00:00 PM 8/3/2016 11:15:00 PM
    13 1004 8/4/2016 4:00:00 PM 8/5/2016 1:00:00 AM
    14 1005 8/5/2016 11:00:00 AM 8/5/2016 7:00:00 PM
    15 1005 8/6/2016 5:00:00 PM 8/6/2016 11:00:00 PM


    ID Caller_ID DateTime_SaleClosed SaleAmount
    1 1000 8/1/2016 9:00:00 AM $100.00
    2 1000 8/1/2016 11:00:00 AM $62.50
    3 1000 8/1/2016 12:30:00 PM $57.75
    4 1003 8/1/2016 5:00:00 PM $500.00
    5 1003 8/1/2016 9:16:00 PM $42.50
    6 1003 8/2/2016 1:00:00 AM $25.00
    7 1000 8/2/2016 10:22:00 AM $50.00
    8 1000 8/2/2016 12:00:00 PM $100.00
    9 1003 8/2/2016 3:00:00 PM $80.00
    10 1003 8/2/2016 7:00:00 PM $1,000.00
    11 1003 8/2/2016 8:15:00 PM $42.60
    12 1003 8/2/2016 9:30:00 PM $50.00
    13 1000 8/3/2016 9:00:00 AM $40.00
    14 1000 8/3/2016 9:30:00 AM $60.00
    15 1000 8/3/2016 3:00:00 PM $20.00
    16 1000 8/3/2016 5:25:00 PM $25.00
    17 1004 8/3/2016 5:15:00 PM $100.00
    18 1004 8/3/2016 6:45:00 PM $100.00
    19 1004 8/3/2016 8:20:00 PM $100.00
    20 1004 8/3/2016 11:00:00 PM $50.00
    21 1001 8/4/2016 9:25:00 AM $250.00
    22 1001 8/4/2016 11:00:00 AM $225.00
    23 1001 8/4/2016 1:14:00 PM $300.00
    24 1004 8/4/2016 4:22:00 PM $1,000.00
    25 1004 8/4/2016 5:33:00 PM $500.00
    26 1004 8/4/2016 7:00:00 PM $750.00
    27 1004 8/5/2016 12:01:00 AM $25.00
    28 1001 8/5/2016 9:00:00 AM $22.75
    29 1001 8/5/2016 10:37:00 AM $55.00
    30 1005 8/5/2016 3:00:00 PM $10.00
    31 1005 8/5/2016 6:45:00 PM $100.00
    32 1001 8/6/2016 10:13:00 AM $52.00
    33 1001 8/6/2016 1:00:00 PM $500.00
    34 1005 8/6/2016 7:00:00 PM $22.50
    I have created the following query which lists the intervals/shifts that each caller has worked:

    SELECT Worked.Caller_ID,
    Worked.DateTime_ON,
    Worked.DateTime_OFF,
    [empl]![Last_Name]+", "+[empl]![First_Name] AS Caller,
    Round((DateDiff("n",[Worked]![DateTime_ON],[Worked]![DateTime_OFF])/60)*2,0)/2 AS [Act Hrs]


    FROM Worked
    INNER JOIN empl ON Worked.Caller_ID = empl.Empl_ID;

    Which produces the following results:
    Caller_ID DateTime_ON DateTime_OFF Caller Act Hrs
    1000 8/1/2016 8:00:00 AM 8/1/2016 11:00:00 AM Smith, Joe 3
    1000 8/1/2016 12:15:00 PM 8/1/2016 5:00:00 PM Smith, Joe 5
    1000 8/2/2016 9:15:00 AM 8/2/2016 1:00:00 PM Smith, Joe 4
    1000 8/3/2016 8:00:00 AM 8/3/2016 10:00:00 AM Smith, Joe 2
    1000 8/3/2016 11:00:00 AM 8/3/2016 6:00:00 PM Smith, Joe 7
    1001 8/4/2016 8:00:00 AM 8/4/2016 4:00:00 PM Jones, Mary 8
    1001 8/5/2016 7:45:00 AM 8/5/2016 10:46:00 AM Jones, Mary 3
    1001 8/6/2016 9:00:00 AM 8/6/2016 5:00:00 PM Jones, Mary 8
    1002 8/7/2016 10:00:00 AM 8/7/2016 5:00:00 PM Blue, Billy 7
    1003 8/1/2016 4:45:00 PM 8/2/2016 2:00:00 AM Duck, Donald 9
    1003 8/2/2016 1:00:00 PM 8/2/2016 10:00:00 PM Duck, Donald 9
    1004 8/3/2016 5:00:00 PM 8/3/2016 11:15:00 PM Doe, John 6
    1004 8/4/2016 4:00:00 PM 8/5/2016 1:00:00 AM Doe, John 9
    1005 8/5/2016 11:00:00 AM 8/5/2016 7:00:00 PM Dough, Jayne 8
    1005 8/6/2016 5:00:00 PM 8/6/2016 11:00:00 PM Dough, Jayne 6

    I want to add two columns to this query. A count of completed sales calls that occurred for each caller during that shift, and a sum of the sales totals for those calls. These would be from the CompletedSales table. How do I do this??

  2. #2
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I am a novice also and what I currently do is... multiple queries each to get part each of what I am looking for. Then use those queries as the source for another query that joins together the information in the way you want it. I have a set of queries that go 4 deep right now but it works.


    I am sure there is a better way but as a novice I am limited to my skills and this works for me at the moment.


    Hope this helps you.

    Walker
    Last edited by NightWalker; 08-23-2016 at 03:45 PM. Reason: clarification

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    In addition to NightWalker's suggestions, perhaps it is time to bind a Report to your query and use some of the tools that a Report will afford you.

  4. #4
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    ItsMe,

    Is what I am doing the correct way to get the information out? I just thought it was the only way I could get it with my limited skills. If that is the correct way then I am doing better than I thought with learning Access.

    Thank you,
    Walker

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I use the Query Designer in Access all of the time. I find it a quick and easy way to build SQL statements. Too often we, as developers, build too much before testing/debugging. Running small queries, analyzing the results and looking for anomalies is a great idea before combining statements and creating joins.

    Even when I combine multiple queries into a single SQL statement, I will use the query designer to break out and analyze smaller datasets. Also, I do not see a problem of using other query objects within a main query object. I do it all of the time. The only downside to that is having many query objects in your database and keeping track of which objects are dependent on others.

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

Similar Threads

  1. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  2. Replies: 3
    Last Post: 05-14-2015, 04:17 PM
  3. Replies: 15
    Last Post: 12-06-2013, 01:41 PM
  4. Replies: 1
    Last Post: 04-12-2013, 03:03 PM
  5. Query for table, based on criteria from other tables
    By PureLoneWolf in forum Queries
    Replies: 2
    Last Post: 10-04-2012, 11:23 AM

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