Getting Access to return blank records?

Question

I built, with luck and duct tape, a database to process some employee data. I get a massive Excel spreadsheet once a month with the following fields:

Employee Number, Fiscal Month, Project Code, Hours Worked

I built queries/tables to link things like Employee Number and the employee's name. All is well.

The problem I'm having, which is probably because other than a few 1-day classes, I don't have any programming background, is I want it to return a result when there's no hours worked.

For a specific example - Let's say we have a project called Project Pasta. In the month of March, no one worked any hours on Project Pasta. When I run my report of all hours worked on each project, though, I want Project Pasta to still show up with "0" in the second column of the resulting spreadsheet.

I have tried various forms of Left Outer Join, but I can't figure out how to get a "Null" return and I'm so annoyed at Access and myself at this point I thought I'd cry for help.

I will answer any questions anyone has, but I'm not 100% sure what people need to know in order to help. It's Microsoft Access 2007 on Windows XP, for what it's worth.

Thank you! :)

The query I have now groups it by manager:

SELECT timeworked.fiscalmonth, 
       timeworked.projectcode, 
       kronoscodes.projectname, 
       SUM(timeworked.hoursworked) AS SumOfHoursWorked, 
       employees.manager 
FROM   employees 
       INNER JOIN ((kronoscodes 
                    INNER JOIN timeworked 
                            ON kronoscodes.projectcode = timeworked.projectcode) 
                   INNER JOIN months 
                           ON timeworked.fiscalmonth = months.fiscalmonth) 
               ON employees.[employee id] = timeworked.employeeid 
GROUP  BY timeworked.fiscalmonth, 
          timeworked.projectcode, 
          kronoscodes.projectname, 
          employees.manager 
HAVING (( ( timeworked.fiscalmonth ) LIKE "janfy13" )); 

The column names are:

EmployeeID, 
ProjectCode, 
ProjectName, 
HoursWorked, 
FiscalMonth

So basically I want EVERY project code to come back for every fiscal month, even if there was nothing under "Hours Worked" in the data I imported.

Answer 1

Consider the following sample data:

Table: timeworked
employeeid  fiscalmonth projectcode hoursworked
1           janfy13     101         16
2           janfy13     101         11
1           janfy13     102         8

Table: employees
[employee id]   manager
1               Manager A
2               Manager B

Table: kronoscodes
projectcode projectname
101         Project 101
102         Project 102
103         Project 103

Using straight INNER JOINS, a slightly simplified version of your "Report by Manager" will look something like this:

where the SQL is

SELECT employees.manager, timeworked.fiscalmonth, kronoscodes.projectname, 
    Sum(timeworked.hoursworked) AS SumOfHoursWorked
FROM employees INNER JOIN 
    (kronoscodes INNER JOIN timeworked 
        ON kronoscodes.projectcode = timeworked.projectcode
    ) ON employees.[employee id] = timeworked.employeeid
GROUP BY employees.manager, timeworked.fiscalmonth, kronoscodes.projectname;

and the results look like this

manager     fiscalmonth projectname SumOfHoursWorked
Manager A   janfy13     Project 101 16
Manager A   janfy13     Project 102 8
Manager B   janfy13     Project 101 11

Hmm, no mention of "Project 102" for "Manager B", and no mention of "Project 103" at all. Still, it's a pretty good start, so lets save it as "ProjectSumsByManager_innerJoin". (It might be useful later... hint, hint.)

We want to see all projects, so we right-click on the join between [timeworked] and [kronoscodes], choose "Join Properties", and select the option "Include ALL records from 'kronoscodes' and only those records from 'timeworked' where the joined fields are equal."

Unfortunately, when we try to run the query we get the pesky "ambiguous outer joins" message:

The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement.

Okay, so we create a separate query with just that join:

where the SQL is

SELECT kronoscodes.projectcode, timeworked.employeeid, 
    timeworked.fiscalmonth, timeworked.hoursworked
FROM kronoscodes LEFT JOIN timeworked 
    ON kronoscodes.projectcode = timeworked.projectcode;

and the results look like this.

projectcode employeeid  fiscalmonth hoursworked
101         1           janfy13     16
101         2           janfy13     11
102         1           janfy13     8
103

Well, "Project 103" is there, but it has no employeeid so there's no way for us to link to an [employees] record and get a manager. Furthermore, "Project 102" is still only associated with "employee 1" ("Manager A"), so "Manager B" still won't show up. This approach doesn't look particularly promising.

Maybe what we really want is just a list of all projects and all managers:

SELECT DISTINCT kronoscodes.projectname, employees.manager
FROM kronoscodes, employees;

returning

projectcode         manager
Project 101         Manager A
Project 101         Manager B
Project 102         Manager A
Project 102         Manager B
Project 103         Manager A
Project 103         Manager B

If we create a new query in Access, type (or paste) that SQL statement into the SQL view, then save that as "Project_Manager_map", we can LEFT JOIN that with our first query above, and get

where the SQL is

SELECT Project_Manager_map.manager, ProjectSumsByManager_innerJoin.fiscalmonth, 
    Project_Manager_map.projectname, ProjectSumsByManager_innerJoin.SumOfHoursWorked
FROM Project_Manager_map LEFT JOIN ProjectSumsByManager_innerJoin ON 
    (Project_Manager_map.manager = ProjectSumsByManager_innerJoin.manager) 
        AND (Project_Manager_map.projectname = ProjectSumsByManager_innerJoin.projectname);

and the results are

manager     fiscalmonth projectname SumOfHoursWorked
Manager A   janfy13     Project 101 16
Manager B   janfy13     Project 101 11
Manager A   janfy13     Project 102 8
Manager B               Project 102 
Manager A               Project 103 
Manager B               Project 103