Need help using a calendar table in SQL-CodePudding

I'm super new to SQL.

I need to run some SQL queries in VBA (Excel) - so I'm using ADO in Excel VBA to access Microsoft ACE OLEDB 12.0 as a provider (which I think - correct me if I'm wrong here - is Access). Currently a work tracking program generates (of interest) a table with columns of User, Start Date, Duration like so (here's a sample snippet):

'sql

SELECT Users.UserID, Users.UserName, WorkDates.WorkDate
FROM WorkDates, Users;

'results
----------------------------------------------------------------
|       UserID       |      UserName      |      WorkDate      |
----------------------------------------------------------------
|                  1 | User1              |          11/1/2021 |
----------------------------------------------------------------
|                  1 | User1              |          11/2/2021 |
----------------------------------------------------------------
|                  2 | User2              |          11/1/2021 |
----------------------------------------------------------------
|                  2 | User2              |          11/2/2021 |
----------------------------------------------------------------

the first query behaves just like a table in a second query so we can add a calculated field to look up and sum duration:

'nz(whatever, 0) replaces nulls with 0
'# # hashtags delimit dates

Duration: Nz(DSum("Duration","WorkData","UserID = " & [UserID] & " AND StartDate = #" & [WorkDate] & "#"),0)

'sql

SELECT qryUsersWorkDates.UserName, qryUsersWorkDates.WorkDate, Nz(DSum("Duration","WorkData","UserID = " & [UserID] & " AND StartDate = #" & [WorkDate] & "#"),0) AS Duration
FROM qryUsersWorkDates;

'results

----------------------------------------------------------------
|      UserName      |      WorkDate      |      Duration      |
----------------------------------------------------------------
| User1              |          11/1/2021 | 1240               |
----------------------------------------------------------------
| User1              |          11/2/2021 | 0                  |
----------------------------------------------------------------
| User2              |          11/1/2021 | 85                 |
----------------------------------------------------------------
| User2              |          11/2/2021 | 0                  |
----------------------------------------------------------------

You can copy the tables into text files and import them into Access. Sql can be copied into the sql pane of the query designer

CodePudding user response：

You need a left join:

SELECT 
    [WorkData].User, 
    [Calendar].Dates,
    CDate(Nz(Sum([WorkData].Duration), #00:00#)) AS Total
FROM 
    Calendar 
LEFT JOIN 
    WorkData
    ON ([WorkData].[Start date]=[Calendar].Dates)
GROUP BY 
    [WorkData].User, 
    [Calendar].Dates
ORDER BY 
    [Calendar].Dates;

Then use this query as source in another query where you filter on the user:

Select * From YourQuery
Where [User] = 'User1'