Access Join Query

Question

I have two tables tbl_DaysWeeksMonths (Left Table) and tbl_Telephony (Right Table). tbl_DaysWeeksMonths has a record of every day of the year with columns (Row_Date/Week/Month) whilst tbl_Telephony has telephony data for hundreds of agents by day with columns (row_date/agent/calls/talk time)(Note: Each agent only has records for 5-6 days of the week instead of everyday).

I want to join the two tables so that each agent has a record for every day of the week regardless if they took calls on a day or not. I want to display blank records (except for the date field) for days which consultants did not take calls. E.g:

## Date ##           ## Agent ##          ## Calls ##      ## Talk Time ##

 1. 26/05/2012     |     James        |         40       |           560
 2. 27/05/2012     |     James        |                  |
 3. 28/05/2012     |     James        |         34       |           456
 4. 29/05/2012     |     James        |                  |
 5. 30/05/2012     |     James        |         40       |           643
 6. 31/05/2012     |     James        |         36       |           345
 7. 01/06/2012     |     James        |         31       |           160

I'm trying to use the below code but I don't think it's correct. Any suggestions on a better code to use. Please help.

SELECT tbl_DaysWeeksMonths.Row_Date, 
       [tbl_Telephony].Consultant, 
       [tbl_Telephony].i_acdtime
FROM tbl_DaysWeeksMonths
LEFT JOIN [tbl_Telephony] 
ON tbl_DaysWeeksMonths.Row_Date = [tbl_Telephony].row_date;

Answer 1

Step 1 -- Assuming you have a table (say, named Consultants) that lists each distinct consultant, create a query (say, named Consultant Days) that generates all possible combinations of days and consultants. It might look something like this:

SELECT [Consultants].Consultant, tbl_DaysWeeksMonths.Row_Date
FROM [Consultants], tbl_DaysWeeksMonths;

If you don't have a Consultants table, you could substitute it with a query that selects the distinct consultants listed in tbl_Telephony. In other words, you can create a Consultants query that looks something like this:

SELECT DISTINCT Consultant FROM [tbl_Telephony];

Step 2 -- Create a query that outer joins tbl_Telephony to Consultant Days. It might look something like this:

SELECT [Consultant Days].Row_Date, [Consultant Days].Consultant, [tbl_Telephony].i_acdtime 
FROM [Consultant Days] 
LEFT JOIN [tbl_Telephony]  
ON [Consultant Days].Consultant = [tbl_Telephony].Consultant 
AND [Consultant Days].Row_Date = [tbl_Telephony].row_date;

This also assumes that the row_date values in tbl_Telephony match the Row_Date values in tbl_DaysWeeksMonths -- in other words, that row_date values in tbl_Telephony are whole days (that is, do not contain a time-of-day component). This also assumes that i_acdtime values in tbl_Telephony are the total talk time for the given consultant and day (as opposed to the talk time for a given call). Presumably there is another column in tbl_Telephony that will give the the total number of cals for the given consultant and day which you could add to the query to get the "Calls" column you said you wanted your question.

Answer 2

You can also use this query.

SELECT tbl_DaysWeeksMonths.Row_Date, [tbl_Telephony].Consultant, [tbl_Telephony].i_acdtime

FROM tbl_DaysWeeksMonths

LEFT OUTER JOIN [tbl_Telephony] ON tbl_DaysWeeksMonths.Row_Date = [tbl_Telephony].row_date;