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
I have created the following query which lists the intervals/shifts that each caller has worked:
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
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??